Dinamic sum on diferent Sheets

[Luis Nic]

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.

 

[GraH - Guido]

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)

 

[Luis Nic]

I appreciate your help.

 

[Peter Bartholomew]

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.