• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dinamic sum on diferent Sheets

Luis Nic

New Member
Hello this may be simple, but i´ve been struggling whith a workbook that has about 20 sheets, each containing information about diferent clients and i need to summarise the data into one sheet son i can visualice and compare several at a time.

So mi idea is to make one sheet, and bring the info i want from the other sheets,

i uploaded an example file to make it easyer.
 

Attachments

  • Example Workbook.xlsx
    12.8 KB · Views: 4
Depending on your version of Excel, for this use case I would always use a Power Query solution. Typically this can be created by simply using the mouse.
Option 1: first consolidate all data in a single table. Your data validation can then be a simply index/match reading from that table.
Option 2: first enter data validation and then refresh query.

I am assuming your data in real life is a a bit larger.
EDIT: for this simple example you could also use an indirect function:
[C10]=INDIRECT("'"&C9&"'!"&"C2",1)
 
Last edited:
If you use sheet-scoped names 'New' (say) you can also build the local names within the INDIRECT function:
=INDIRECT("'"&C9&"'!New")
Then you will be able to move the client data to a different location of each sheet and the formula will still find the correct data.
 
Back
Top