Tab Referencing

[Carl_W]

Hi gang!  I'm sure this is covered somewhere but I'm not able to find.

A row of cells in tab "ORDERS" contain complicated part numbers (e.g., 123A567B9).  I break out each part number in neighboring cells (e.g., 123, S, 567, B, 9).  The data in the second broken-out cell tells me what tab I need to go to for additional data.  The data in the follow-on cells tell me what cell within the other tab I need data from.  In this example, "S" means I must now go to tab, "SUPPLIER" to get some data from cell B567.  That data, of course, gets placed in another cell on tab "ORDERS".

On tab "ORDERS", how do I reference the data from a particular cell in tab, "SUPPLIER"?

A simple formula would be ='SUPPLIER'!B567 but because the tabs vary, I need to replace "SUPPLIER" with a reference to the cell that contained the letter "S".  I can use a VLOOKUP table to get the tab name (e.g., S = SUPPLIER).  how do I take my VLOOKUP result and say 'use the tab with this name'?

Is there another way instead of VLOOKUP?  For example, all of my tab names start with different letters.  Maybe that helps.

I tried ="'"VLOOKUP(E11,C14:D15,2)"'"!AF10 but, as expected, it did not work.

Thanks in advance for the help!

[#2]

@Carl_W 

It sounds like you're a relatively sophisticated Excel user who may just need to be pointed to the appropriate function. Let's try that. Check out INDIRECT. I think that it , in conjunction with VLOOKUP to get the tab's name, may well be what you need to create a direct reference to the appropriate cell on the SUPPLIER sheet (and others, as needed).

Here's a great reference to INDIRECT. If this isn't sufficient (i.e., if you need further help), please come back with a copy of your workbook (or a mock-up that removes any confidential data), so that we can give specific and directed help.

https://exceljet.net/excel-functions/excel-indirect-function

[#3]

@Carl_W Combining INDIRECT, as suggested by @mathetes , with the ADDRESS function can achieve what you need. In the attached workbook, I've mocked-up something that demonstrates how this could work. So, S, 3, B will pick up the value from SUPPLIER!$B$3

[#4]

@Carl_W I played with @Riny_van_Eekelen 's solution to set up some alternatives.  Since I am using Excel 365 it is likely that the initial changes that allow me to work with the formula makes it unusable for others.  Firstly Riny's solution

= LET(
    rowNum, Row,
    colNum, XLOOKUP(Col,ColRef,ColNr),
    sheetRef, XLOOKUP(Sheet,SheetCode,SheetName),
    return, INDIRECT(
       ADDRESS(rowNum, colNum,1,1, sheetRef)),
    return)

Then, I retained the INDIRECT, but used it to evaluate sheet-local names rather than direct cell references

= LET(
    rowNum, Row,
    colNum, XLOOKUP(Col,ColRef,ColNr),
    sheetRef, XLOOKUP(Sheet,SheetCode,SheetName),
    dataRange, INDIRECT(sheetRef & "!data"),
    return, INDEX(dataRange, rowNum, colNum),
    return)

Next, I used CHOOSE as an alternative to the volatile function INDIRECT

= LET(
    rowNum, Row,
    colNum, XLOOKUP(Col,ColRef,ColNr),
    sheetIndex, XMATCH(Sheet,SheetCode),
    dataRange, CHOOSE(sheetIndex, MASTER!Data, ORDERS!Data, SUPPLIER!Data),
    return, INDEX(dataRange, rowNum, colNum),
    return)

Passing the parameters as a Lambda function allows the details to be hidden within the function name, leaving the worksheet formula to read

= DATAλ(Row, Col, Sheet)

The ultimate objective might be to use recursion over the sheet codes in order to return results from selected sheets as an array.  By the time one finishes, not much remains as traditional Excel!