MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Referencing sheets


Posted by Aaron on October 19, 2001 2:27 AM

I have a spreadsheet that has many sheets incorporating the same type of data in the same format. I want to create a summary sheet that allows the user to input the sheet name (eg "Sheet23"), and have the summary data update automatically with reference to the info on Sheet23.

I can do this with VB, and events, but would like to know if there is a way to do it without using the VB functionality.


Posted by Juan Pablo on October 19, 2001 5:35 AM

Use the INDIRECT function, for example, if you put in A1

Sheet23

and in B1 you need to get Cell C3 for the specific sheet the user enters, put this formula in B1

=INDIRECT(A1&"!C3")

Juan Pablo