Look Up Same Cell Number from Different Worksheets

[Prosperina]

I have file that has a daily report on a different worksheet.

I'm trying to create a summary report for all the worksheets.

How do I create a formula to lookup values on the same cell number but on different worksheets? I don't want to edit each formula to change the worksheet name since it is on the same cell number.

Ex:
On Worksheet 1:
A1='Worksheet2'!B3 B1='Worksheet2'!B4
A2='Worksheet3'!B3 B2='Worksheet3'!B4
A3='Worksheet4'!B3 B3='Worksheet4'!B4

Thanks.

 

[Tom Urtis]

OK, follow me on this example.

For starters, go to your Sheet1 tab.
In cell D1 enter B3
In cell E1 enter B4

In cell A1 enter the formula
=INDIRECT("'Sheet2'!"&D1)

That will get you what you want for that formula, and you can modify it for the other worksheets in cells A2 and A3.

But wait, there's more...

Assuming you are really placing the formula in cell A1 of sheet1, then with "B3" (without the quotes) entered into cell D1, and with "B4" (without the quotes) entered into cell E1, then enter this in cell A1 and copy it down to cell A3:

=INDIRECT("'Sheet"&ROW()+1&"'!"&$D$1)

Repeat the process for the formula in (presumably) cell B1 but as follows:

=INDIRECT("'Sheet"&ROW()+1&"'!"&$E$1)

 

[Prosperina]

That's it! Thanks a lot, Tom. You saved my day.

 

[cgr504]

Hi Tom:

I am attempting to use this formula, yet it does not seem to be working for me.The setup in my workbook is the same as in your example: The value I need is in a particular cell, and I want to return a list of those values in a summary sheet along with the names of all the tabs. Yet when I use the formula as is I get a #REF error.

I feel like the problem is that the formula as you have written it is expecting the sheets to be named the default 'Sheet1,' 'Sheet2,' yet my tabs have other names.

This is my first time replying to a thread, though I have used your advice before. Thanks in advance!

c.

 

[Aladin Akyurek]

Hi Tom:

I am attempting to use this formula, yet it does not seem to be working for me.The setup in my workbook is the same as in your example: The value I need is in a particular cell, and I want to return a list of those values in a summary sheet along with the names of all the tabs. Yet when I use the formula as is I get a #REF error.

I feel like the problem is that the formula as you have written it is expecting the sheets to be named the default 'Sheet1,' 'Sheet2,' yet my tabs have other names.

This is my first time replying to a thread, though I have used your advice before. Thanks in advance!

c.

Care to elaborate: Which cell, which sheets (for sake of simplicity: keep the number to three), and which results do you want to see?

 

[cgr504]

Care to elaborate: Which cell, which sheets (for sake of simplicity: keep the number to three), and which results do you want to see?

Dear Aladin:

I have over 100 sheets, and I am trying to make a summary sheet with two columns: one listing the sheet names, and the second listing the value of cell B1 in each sheet.

However, the sheet names are not systematic like Sheet1, Sheet2, etc.

How can I adapt the formula presented in this thread for this situation?

Thanks,

cgr

 

[Aladin Akyurek]

Dear Aladin:

I have over 100 sheets, and I am trying to make a summary sheet with two columns: one listing the sheet names, and the second listing the value of cell B1 in each sheet.

However, the sheet names are not systematic like Sheet1, Sheet2, etc.

How can I adapt the formula presented in this thread for this situation?

Thanks,

cgr

If you know the sheet names, you can list them say in column A of your summary sheet and invoke in column B the required INDIRECT formulas...

A2, A3, and so on, each houses a sheet name.

B2, just enter and copy down:

=IF(INDIRECT("'"&A2&"'!B1")="","",INDIRECT("'"&A2&"'!B1"))

If you don't want to list your sheet names manually, try the procedure given in:

http://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets-2.html (Post #22)

 

[cgr504]

If you know the sheet names, you can list them say in column A of your summary sheet and invoke in column B the required INDIRECT formulas...

A2, A3, and so on, each houses a sheet name.

B2, just enter and copy down:

=IF(INDIRECT("'"&A2&"'!B1")="","",INDIRECT("'"&A2&"'!B1"))

If you don't want to list your sheet names manually, try the procedure given in:

http://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets-2.html (Post #22)

I don't understand the part about "invoking...the required INDIRECT formulas." I have the sheet list in Column A in the same order as the sheets. What exactly do I do in Column B?

c.

 

[cgr504]

Never mind. I got it. I just had to switch the A2 in both parenthetical statements to A1, since I had started my list on that row. Silly mistake...

Thanks a lot for your help, Aladin.

cgr

 

[Aladin Akyurek]

Never mind. I got it. I just had to switch the A2 in both parenthetical statements to A1, since I had started my list on that row. Silly mistake...

Thanks a lot for your help, Aladin.

cgr

No problem. You are welcome.