Excel experts, I need your help.

Factual Questions
1

Sorry for the vague thread title, but I’m really not sure how to distill the essence of what I need into a convenient, bite-sized, title-length phrase.

Anyway, on to what I need. Cell A1 is text. The text is the name of a worksheet. I need a formula that will tell cell B1 that A1 is a sheet (in the same workbook) and return a value from that sheet.

Yes, I know I can do this manually, but there are dozens of sheet names in column A. Is there a way I can tell Excel that the data in the cells in column A are the names of sheets without having to click in B1, key in =, click the sheet and then click the cell that contains the value I want to return?

Thanks much and I hope that wasn’t too confusing. I am more than willing to clarify the above.

2

Use the “indirect()” function coupled with the “&” operator (for concatenating text).

For example:


=INDIRECT(A1&"!A1")

will take the worksheet name from the current sheet’s cell A1 and look up the value in the other worksheet’s cell A1. (The first A1 is the worksheet’s name; the second A1 is the cell you want to look up in that worksheet.)

You can download a sample workbook here if you need one.

3

Nice! I consider myself to be pretty proficient in Excel and that function is a new one for me.

4

Thanks a bunch. I haven’t had a chance to evaluate the function to see if it fits my needs, but I wanted to reply to let you know I’ve read your answer.