Getting the value from sub sheets

Hi,

We have a excel file in there we have many sub sheets, but for demo we have created only two

in sheet1, we have the following data
S.No. Name Number
1 2
1 test1 300 318

2 test2 445 408

we have two sub sheets which names are test1 and test2. We are taking the number 1 and Number 2 value from sub sheets (as per attached sheet)

We are using the function =temp1!B2 for getting the data but we have approx 20 sub sheets and want to get the data from there but when we add some new sheets, again we need to do it manually

any option " =temp1!B2 " instead of this

You can use the INDIRECT function to get the data from different sheets. Will your sheet names follow some pre-defined format (like Temp1 and Temp2 in your example file), or can the Name column (B) in Sheet1 contain the names of the sheets?

Pete

Thanks for the update Pete_UK

we have the sheet names,
Goldy, Raghav, Gupta, MDH

any example with INDIRECT function

pls

So, as well as those names being applied to each sheet tab, will they also appear in the Name column of Sheet1?

Pete

Assuming that the names are in column B, you can use this formula in C3:

=INDIRECT("'"&$B3&"'!B"&C$2+1)

and copy into D3. Then copy C3:D3 down for your other names.

Hope this helps.

Pete

not working bro

showing error #REF!

You have probably mis-typed the sheet names - ensure there are no leading or trailing spaces.

I've attached your file with the necessary amendments. Notice that I have renamed your subsidiary sheets, and put the same names in column B on Sheet1, where the formulae are in columns C and D.

Hope this helps.

Pete

Thanks Pete_UK,

its working

Glad to hear it, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

Thanks, Sure bro