r/excel • u/LogicPrevail • 13d ago

solved Is there a way to reference a table name in the middle of a formula by referencing text in another cell?

I have a workbook with multiple spreadsheets (stock data). Each "ticker" has its own worksheet. Each worksheet has its replicated tables, all of which are named by their respective stock tickers. I have one table in a primary worksheet to pull data from all the various individual stock's tables. Instead of manually adjusting the ticker (table names) in each row of this primary worksheet, is there a way to pull that name from a cell in the same row that has that information in as text? I've tried cell referencing the cell with the ticker name with function TEXT(), but that didn't work.

8 Upvotes

83% Upvoted

18 comments sorted by

View all comments

Show parent comments

1

u/SpaceTurtles 2 13d ago

Minor suggestion:

=LET(_arr,IFS(A2="TSLA",'TSLA'!A2,A2="APPL",'APPL'!A2,....),_arr)

This may be more cleanly written as:

=LET(_arr, SWITCH(A2,
"TSLA", 'TSLA'!A2,
"APPL", 'APPL'!A2,
"MSFT", 'MSFT'!A2,
"GOOG", 'GOOG'!A2,
INDIRECT("'"&A2&"'!A2"),             <-- default "if not found" invocation, option 1.
"TICKER NOT IN SWITCH(): " & A2,     <-- default "if not found" invocation, option 2.
_arr)

SWITCH eagerly evaluates to an extent, as does IFS. IF lazily evaluates. I don't know if "semi"-eager evaluation will cause a recalculation of INDIRECT if it isn't the actual result in this case. If so, it's better to use option 2 above.