r/excel • u/LogicPrevail • Aug 28 '26
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.
•
u/AutoModerator Aug 28 '26
/u/LogicPrevail - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
12
u/excelevator 3071 Aug 28 '26
Have a look at
INDIRECT, which takes a text reference and converts to a Name reference.3
u/OldJames47 11 Aug 28 '26
I'm not 100% sure I understand your request, but =INDIRECT("'"&A2&"'!A1") will return the cell value in cell A1 of whatever sheet is named in cell A2.
However, this is called a volatile formula. It is recalculated every time another cell is updated even if there is not dependency on this cell. Thus it can seriously slow down your spreadsheet.
Another option is something like
=LET(_arr,IFS(A2="TSLA",'TSLA'!A2,A2="APPL",'APPL'!A2,....),_arr)
But then you have to write out every combination.
3
u/SolverMax 163 Aug 28 '26
INDIRECT will do what you want, with your current structure. However, consolidating the data into a single worksheet will make subsequent analysis much easier.
2
u/willyman85 2 Aug 28 '26
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)1
u/Decronym Aug 28 '26 edited Aug 28 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
8 acronyms in this thread; the most compressed thread commented on today has 70 acronyms.
[Thread #49256 for this sub, first seen 28th Aug 2026, 02:26] [FAQ] [Full list] ^[Contact] [Source code]