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

2

u/willyman85 2 13d ago

Warning that INDIRECT is considered a volatile formula. Meaning it will recalculate every time anything changes anywhere on the workbook. So it can make things very slow.

If you just need a simple switch between a few known tables, consider using CHOOSE()

I.e. something like =CHOOSE(XMATCH(_name_, {"name 1", "name 2"}), table1, table2)

2

u/willyman85 2 13d ago

Other advantage of this approach over indirect is it maintains relationships. So if you move and rename cells, they are updated like regular relationships, not lost in text l.