Index Workbook - Return Sheet Names
Dec 19, 2012
I have a workbook with a menu and 122 sheets holding detail for unique item (product model).
On the menu there is an index of the sheet names which was hand typed. However, the sheet names change as models evolve/get replaced.
I've written a small macro to put the sheet name in cell J1 of each sheet.
[Code] ......
On my menu page i was hoping to do something like this:
A B
Index NAME
2 =sheet[A2].$J$1
3 =sheet[A3].$J$1
That way i could just drag this down and my index list would complete itself and would dynamically update as sheet names change....
View 5 Replies
ADVERTISEMENT
Feb 25, 2011
Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?
If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.
View 3 Replies
View Related
Jun 2, 2009
I am currently looking at a simple data set spanning a few years of annual average data. I want to create a front sheet to show people in what year the maximum value was reached for each site but am having trouble returning the values......
The summary sheet is headed:......
I want the formula to say - find the summary sheet max value cell in the main data sheet row 2 (for XYZ) and return the corresponding column from row 1 (year). I've got myself in a muddle trying to create array data from the index function and want something simple. There are only about 30 rows so am happy to manually select the rows in question and repeat the formula entry so it doesn't need to be fancy.
View 2 Replies
View Related
Jan 19, 2014
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
View 7 Replies
View Related
May 13, 2013
Here's the data table being referenced
Rank
Week Ending
Name
Value
1
1/1
Apple
100
[Code] ........
Now on another sheet, I want to return the top two 'Name's and their values like below:
Name
Value
There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?
I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..
View 1 Replies
View Related
Mar 22, 2014
I have created a file that has several worksheets with different naming convention - to specify what the sheets are for. However, as the sheets are added, I sometimes find it difficult to keep track of all the sheets that I have in the workbook.
Is there a Macro Code or formula that I can use so that a Summary worksheet can summarize all the names of the worksheets that I have in the particular workbook.
View 5 Replies
View Related
Oct 7, 2013
Within one workbook I have 15 sheets, 13 are for separate divisions within the company, 2 are used to present sums across the 13 sheets. All 13 sheets have identical columns and rows, with unique numbers in each cell. Right now I am manually selecting the cells to reference in my master sheet, =sheet name!cell+sheetname!cell+sheetname!cell - so and and so on. How can I reference the sheet name once, and then all of the cells to SUM? =sheetname!(c4+c14+c24+c34) etc?
This would let me be able to copy the formula from cell to cell, and only have to change the sheet name each time.
View 3 Replies
View Related
Feb 20, 2013
I'm about get code for list out the sheet names in current workbook, list would be displayed in a new sheet at the end.
View 3 Replies
View Related
Feb 27, 2014
I've created a macro that loads a listbox with the sheet names from another workbook. It works fine with Excel 2007-2010 but some reason fails with 2013. It doesn't crash, it just doesn't add items to the listbox nor doest it add the caption to Userform1.Caption. It does launch the form. Here is the code snippet:
Code:
'open read-only
wbkpath = Sheet3.Cells(1, "f") 'full pathway to source workbook
Workbooks.Open Filename:=wbkpath, ReadOnly:=True
Set swbk = ActiveWorkbook
swbk.Activate
[Code] ...........
View 2 Replies
View Related
May 8, 2007
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
View 17 Replies
View Related
May 7, 2012
Can I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies
View Related
Oct 15, 2013
Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
View 2 Replies
View Related
Jan 11, 2008
I have a long list of tabs listing "projects" which have changing names - on the first sheet, I want to have the table of contents automatically update and link to each tab - I want the user to only have to change the tab name to have the table of contents and link update -
View 5 Replies
View Related
May 1, 2008
Two part question:
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
View 9 Replies
View Related
Apr 9, 2012
I have an excel workbook with a dynamic dependent drop down list setup, works a treat on the first worksheet i set it up on.
However here's the problem, my workbook contains a good handful of worksheets each named to reflect the certificate on the page, i need the drop down list to work on each tab. problem is when i setup the index/match function it requires a specific sheet name to make it work i.e.
=INDEX(Table1,0,MATCH(PI!$C20,Table1[#Headers],0))
However what i need it to do is reference the worksheet name, as new ones get added and names may change, something like
=INDEX(Table1,0,MATCH(&ActiveSheet.Name&$C20,Table1[#Headers],0))
However this doesn't work, i do have a cell on each page that holds the Sheet name using the function:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
But I cant even get it to use that cell as a reference in my formula.
View 9 Replies
View Related
Jun 4, 2014
I have a data set where the row headings repeat a lot. I have 5 headers repeated probably 30-45 times each. Eg. Truck, Car, Van, Tank, House, Car
At the moment I am using the formula...
=INDEX($B$2:$M$45,MATCH($O$2,$A$2:$A$45,0),MATCH(O4,$B$1:$M$1,0))
which will only return the result of the first occurrence of the cell it is looking for (for eg. I am looking for the values in the cells in columns labelled 'Car', is it possible for it to look past the first occurrence in the data set and find all the values?
View 12 Replies
View Related
Mar 27, 2014
I would like to hyperlink the names in sheet 1 to the same names in sheet two. Is there a way I could do this using a formula rather that do each one by one?
View 3 Replies
View Related
Jun 25, 2009
I have two active worksheets. The first one is called 'Chart' and the second is 'Values'. I have three columns of data. I would like to look in the first two, and return a value in the third. =INDEX(Values!$C$1:$C$299,MATCH(Values!$D$1:$D$299,LEFT(Chart!A1:A16091,6)&" "&LEFT(Chart!C1:C16091,2)),0) .........
View 2 Replies
View Related
Apr 23, 2012
I need to return the index based on values from 2 columns with largest amounts, below the example: From the below table, the index I need returned will be "Purchasing Data Inquiry" because even though this one does not have the biggest % has more number of hits (51) based in column D. So I need a comparison of column D and E to subtract the Index.
ABCDE1ProductYesNoTotal%2SAP-Shopping Cart1141573.33%3Purchasing Data Inquiry4655190.20%4CIP Login-Security606100.00%5SPEED Item BOM101100.00%6BCRN-Business connect RosettaNet202100.00%7Access Manager-IBL Customer Entitlement51683.33%8ISM-Integrated Shipping Memo202100.00%9EAM-Enterprise Access Mgmt1511693.75%
View 3 Replies
View Related
Jul 1, 2014
I am very competent using the index match formula and have incorporated this into many spreadsheets to auto-fill for avoiding input errors. The objective I am attempting now is to retrieve from a list of 2800 entries the smallest return on an index match formula.
From the data below if my Index Match formula uses C1 to index and match my return is always a7 (-12.7) the first in the list, where my desire is a return of a19 (-14.11). To complex this problem I often have duplicate amounts that need to be listed when they fall into my parameters. I have figured that the Small function allows me to assign first smallest, second smallest, and so forth.
I prefer not to use vlookup for simplicity and error avoidance issues.
a b c
1 amount Reason for Movement 2250
2 -183.252150
3 -366.612150
4 -61.1 2154
5 -91.652150
6 -4.34 2450
7 -12.7 2250
8 -2.98 2250
9 -2.98 2250
10 -69.372452
11 -0.6 2451
12 -4.49 2250
13 -4.49 2250
14 -4.14 2250
15 -4.7 2250
16 -4.55 2250
17 -4.85 2250
18 -1.41 2250
19 -14.112250
20 -8.47 2250
View 13 Replies
View Related
Aug 4, 2014
How can I modify this formula so it retrieves the 1st matching value (like always), but then the 2nd match, 3rd, etc.:
{=INDEX($A$2:$D$6,MATCH(F2:$F$6&G2:$G$6&H2:$H$6,$A$2:$A$6&$B$2:$B$6&$C$2:$C$6,0),4)} sheet attached: Example.xlsx
View 3 Replies
View Related
Feb 13, 2014
I have attached a sheet with what I am trying to accomplish...
I have several records that I am doing a lookup through. Most records have the same "work order number". As you can see on the attached sheet, my lookup function returns all the work order numbers that match my criteria.
What I need is to only return the first instance of the work order number then return the next unique work order number that meets my criteria.
Attachment 297239!
View 3 Replies
View Related
Feb 12, 2010
how to go about this, as I always get confused with Index and Match. In my example, in col A I have the Beginning of a period, Column B is the End of the Period, and the amount to be matched is in Column D. Range A1 to E4 is the source data. Below that, starting from A9 to A37 are dates. I would like to match the corresponding value in range D2:D3 if the date in range in A9 : A37 falls between the period stated in A2:B3. I've listed how the values should appear in C9:C37.
View 2 Replies
View Related
Jan 30, 2003
Is there a way to use the INDEX function to return Blanks or NAs instead of zeros when the corresponding row & column match is a blank/null cell?
e.g. =INDEX(F10:H13,1,1) returns 0 even if cell F10 is blank?
View 9 Replies
View Related
Nov 10, 2011
What I want to do is, given the data in col. a, I want to supply a number(range) of strings which can be found in column a, whenever a string is found, which exists in given range, I want to write the string from the Key(col. c) in col b instead of the value from the string that matches a value.
See below for example of what I got working but not exactly what I was looking for
Col. A- Data
Orangegreen
Blackwhite
Blueyellow
Col. B - Result
Col. C - Keys
Orange
White
Yellow
I used
Code:
=INDEX(A1:A3,MATCH("*"&C1&"*",A1:A3,0))
This does produce a result, but not entirely what I am after.
This requires only one string to search for, and will in cell B1 result in a value of 'Orangegreen' as I can only supply one valeu to the function. What I want to do is supply the function with a range of values, and have those values outputtet in colb instead af the value of cells where the result is found.
I tried modifying the function to include a range, but that does not produce a valid result.
View 5 Replies
View Related
Dec 20, 2011
If the weight of a shipment is between a range e.g 3.5kg I need it return the higher cost. For England this would be 9.31.
COSTLook upWeight KGEnglandScotlandIrelandWales1131-3 (KG)7.687.687.688.144454-5 (KG)9.319.319.319.4566306-30 (KG)14.3014.3014.3014.4531315031-50 (KG)22.4222.4222.4223.5551517651-75 (KG)35.1735.1735.1737.04767610076-100 (KG)45.2645.2645.2647.62KGS3.5ORIGINEnglandCost
View 9 Replies
View Related
Feb 17, 2013
In column B I enter a date when a student attended tutoring.
In column C I enter the name of a student attending the tutoring.
In cell D4, I will be entering a student name
In cells D5:D55 I'd like a formula that transposes the dates (if any) that a student attended tutoring. So if a student has their name appear beside the dates ... 14-Feb, 22-Mar, 24-Mar .... then I'd like those three dates to appear in cells D%, D6 and D7.
How to formulate this ... I'm guessing it's going to involve an index-match formula.
View 1 Replies
View Related
Jan 15, 2014
I have a table with a couple thousand rows. I am having trouble writing an index and match to return multiple matches.
I want to use the value in Column 1 (Search Name) to search through Column 2 (Code) and when it finds a match, return the matches from the Return Item column.
Not all of them will have a match (A1AT), but some of them will have multiple matches (ALB2).
Would the output be horizontal across? Where the various matches would come under columns 4, 5, 6, etc.
SearchName
Code
Return Item
A1AT
SDES
TDRNC
[Code]....
View 1 Replies
View Related
Mar 3, 2014
I'm trying to lookup two dates in a column in order to provide the start and end point of an array for use in the PERCENTILE.INC function.
I can't quite get it working, my base idea is something like;
PERCENTILE.INC(INDEX(A1:A10,MATCH(C1,B1:B10,0))&":"&INDEX(A1:A10,MATCH(D1,B1:B10,0)),0.95)
So this looks for the dates found in C1 and D1, finds them in B1:B10, and returns the corresponding cells in A1:A10 for the array part of the PERCENTILE syntax.
I think the issue is that the lookup will return the value in A1:A10 rather than using the cell reference for the PERCENTILE.INC function...
View 4 Replies
View Related
Jun 22, 2009
in one column I am looking up the value Yes to return the row number.
=MATCH("Yes",C:C,0) in this case it returns a 2
I want to use this row number in a sum...
i.e. =sum(b2:b&x) where x is the row number from the formula above, but it just errors out.
View 9 Replies
View Related
