Share via

reference tab names in formulas

Anonymous
2024-09-03T19:33:57+00:00

I'm trying to pull data from various tabs in a worksheet. (like each sheet is an invoice (yellow sample) & I want to create a summary page). I'm trying to create the formula to reference a list of the tabs, but can't get the syntax right. I can get it to work by concatenating the parts of the formula, then activating each cell (erase the =, retype it, then hit enter) - but that's too much work for hundreds of instances. It seems like it should be easy & lots of help doing the reverse (making invoices from a dataset), but I can't quite get it - what am I missing? Also, trying to avoid VBA/code to keep it simple for other users.

Edited to add: Here is a screen shot of the & formula that creates the formula (then I copy & paste special values); I just have to go into each cell & hit Enter for the formula to activate.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.3K Reputation points MVP Volunteer Moderator
    2024-09-03T21:54:43+00:00

    You can use the INDIRECT function:

    =INDIRECT("'" & A2 & "'!C10")

    to return the value of C10 on the sheet whose name is in A2.

    This can be filled down.

    0 comments
  2. Anonymous
    2024-09-04T18:37:09+00:00

    I found correct syntax & posting in case it helps others.

    =INDIRECT("'" & A1 & "'!$D$7")

    And fill down step increases the tab names according to the list in column A.

    Now I just copy & paste formula and change the D7 reference to the appropriate cell & fill down. Also change column format to match date or dollars as appropriate.

    Open to other simple answers.

    0 comments