How to make sheet name as dynamic in formula?

Hi there,

I have a hundreds of sheet name: 1,2,3,4,5...so on. I have to copy from all sheets into combine sheet. How to make sheet name dynamic in formula for drag down? Your suggestion is highly appreciated. Sample file is attached.

Regards,
Indra

The simplest answer is, in B2, copied down:

=IFERROR(INDIRECT("'"&$A2&"'!B2"),"")

But, if you want to pick up B2, C2, etc, etc, from all sheets using a draggable formula, then use:

=IFERROR(INDIRECT("'"&$A2&"'!"&ADDRESS(2,COLUMNS($A:B),4)),"")

Are you still using Excel 2003?? Clearly not as you have attached an .xlsx. Please update your profile.

Purely out of interest why are you keeping a data record for a single individual on multiple sheets?

Why not keep them all on a single sheet? You say they are dynamic which implies you continually change the individual sheets.
When you have all the details on a single sheet I suggest you delete all the others and just work with the single sheet database.
When you want to edit a record just use Autofilter to find the name you want.

Lol. Please update your profile NOW, before you forget.



You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.