This is a first time post so apologies if i transgress the rules. I am using Office 2013 (64-bit) in a Windows 7 environment.
I have written the following nested array of index/match with named ranges to create a cumulative list from several named ranges which reside on different sheets/tabs. The below array begins in cell $A$3 and continues for several hundred rows. Named range TotalCSInput resides on sheet/tab CSInput, named range resides on tab/sheet CDS and named range TotalOptions resides on sheet/tab Options
Over time this list will include additional tabs. Currently i use the result of this cell to drive a nested HLOOKUP which refers to the three named ranges included in the array (TotalCSInput, TotalCDS and TotalOptions). This formula is used to find about 60 different data items and begins in cell $B$3 and is copied to the right and down for the same number of rows as the array in column A. Here is that formula:
While this works, as i add more and more tabs from which i construct my list and from which i look up data, the nested If statements will get very unwieldy.
The simple solution would be to use an indirect function which refers to the specific sheet from which the cell in column A is returning the value. I cannot determine which function or combination of functions will provide me the name of the sheet which is actually providing the value in cell $A$3. If i could find the name of the sheet, i could write a simple HLOOKUP combined with Indirect to lookup the rest of the data i need.
Thank you in advance for helping out with this - it will make my life so much easier.
I have written the following nested array of index/match with named ranges to create a cumulative list from several named ranges which reside on different sheets/tabs. The below array begins in cell $A$3 and continues for several hundred rows. Named range TotalCSInput resides on sheet/tab CSInput, named range resides on tab/sheet CDS and named range TotalOptions resides on sheet/tab Options
- {=IFERROR(IFERROR(INDEX(TotalCSInput,MATCH(0,IF(ISBLANK(TotalCSInput),1,COUNTIF($A$2:$A2,TotalCSInput)),0)),IFERROR(INDEX(TotalCDS,MATCH(0,IF(ISBLANK(TotalCDS),1,COUNTIF($A$2:$A2,TotalCDS)),0)),INDEX(TotalOptions,MATCH(0,IF(ISBLANK(TotalOptions),1,COUNTIF($A$2:$A2,TotalOptions)),0)))),"")}
Over time this list will include additional tabs. Currently i use the result of this cell to drive a nested HLOOKUP which refers to the three named ranges included in the array (TotalCSInput, TotalCDS and TotalOptions). This formula is used to find about 60 different data items and begins in cell $B$3 and is copied to the right and down for the same number of rows as the array in column A. Here is that formula:
- =IF(OR(ISBLANK($A3),$A3=""),"",IFERROR(IF(HLOOKUP(B$1,CSInput!$A$1:$CE$169,MATCH($A3,CSInput!$A$1:$A$169,0),FALSE)="","",HLOOKUP(B$1,CSInput!$A$1:$CE$169,MATCH($A3,CSInput!$A$1:$A$169,0),FALSE)),IFERROR(IF(HLOOKUP(B$1,CDS!$A$1:$CE$169,MATCH($A3,CDS!$A$1:$A$169,0),FALSE)="","",HLOOKUP(B$1,CDS!$A$1:$CE$169,MATCH($A3,CDS!$A$1:$A$169,0),FALSE)),IF(HLOOKUP(B$1,Options!$A$1:$CE$169,MATCH($A3,Options!$A$1:$A$169,0),FALSE)="","",HLOOKUP(B$1,Options!$A$1:$CE$169,MATCH($A3,Options!$A$1:$A$169,0),FALSE)))))
While this works, as i add more and more tabs from which i construct my list and from which i look up data, the nested If statements will get very unwieldy.
The simple solution would be to use an indirect function which refers to the specific sheet from which the cell in column A is returning the value. I cannot determine which function or combination of functions will provide me the name of the sheet which is actually providing the value in cell $A$3. If i could find the name of the sheet, i could write a simple HLOOKUP combined with Indirect to lookup the rest of the data i need.
Thank you in advance for helping out with this - it will make my life so much easier.
