Help with VLOOKUP using INDIRECT to reference other worksheets

mctopher · Jun 23, 2011

I need to create a summary sheet that displays Net Income for each of 200+ store locations. Inside the workbook I have one worksheet for each location that has the Net Income amount I need. I have a Summary Worksheet that needs to reference each of the subsequent tabs and return the net income. Because we acquire and sell locations, these store numbers change on a regular basis, so I’m trying to avoid I’m having a hard time doing this without hard coding the store number into the formula, instead I’d like to base the lookup off the store number entered on the summary tab.

I’d like to try to set this up to work with the “Indirect” function, however I’m not having any luck using this to reference other tabs. We cannot use VBA for this as some of the computers are locked down to prohibit macros.

Each workbook has the store name such as “Store #001”

My simplified summary worksheet looks like this:
<table class="MsoNormalTable" style="width: 171px; margin-left: -0.75pt; border-collapse: collapse; height: 124px;" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="64"> Store
</td> <td style="width:59.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="79"> Net Income
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="64"> 001
</td> <td style="width:59.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="79">

89510

</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="64"> 002
</td> <td style="width:59.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="79">

754123

</td> </tr> <tr style="mso-yfti-irow:3;height:15.0pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="64"> 003
</td> <td style="width:59.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="79">

95400

</td> </tr> <tr style="mso-yfti-irow:4;height:15.0pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="64"> 004
</td> <td style="width:59.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="79">

85413

</td> </tr> <tr style="mso-yfti-irow:5;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width:59.0pt;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="79">
</td> </tr> </tbody></table>
The formula for Net Income is =VLOOKUP(“NET INCOME”,'Store #001'!A:C,3,FALSE). I want to replace the Store 001 with a reference to the store number on this summary tab.

Any direction or insight that can be provided would be greatly appreciated.

Jonmo1 · Jun 23, 2011

Welcome to the board...

If you are going to replace ONLY the store #, try

=VLOOKUP(“NET INCOME”,INDIRECT("'Store #" & A1 & "'!A:C"),3,FALSE).

A1 = 001 or 002 etc..

If you want to replace the ENTIRE sheet name..

=VLOOKUP(“NET INCOME”,INDIRECT("'" & A1 & "'!A:C"),3,FALSE).
A1 = Store #001 or Store #002 etc..

Hope that helps.

T. Valko · Jun 23, 2011

mctopher said:
I need to create a summary sheet that displays Net Income for each of 200+ store locations. Inside the workbook I have one worksheet for each location that has the Net Income amount I need. I have a Summary Worksheet that needs to reference each of the subsequent tabs and return the net income. Because we acquire and sell locations, these store numbers change on a regular basis, so I’m trying to avoid I’m having a hard time doing this without hard coding the store number into the formula, instead I’d like to base the lookup off the store number entered on the summary tab.

I’d like to try to set this up to work with the “Indirect” function, however I’m not having any luck using this to reference other tabs. We cannot use VBA for this as some of the computers are locked down to prohibit macros.

Each workbook has the store name such as “Store #001”

My simplified summary worksheet looks like this:
<TABLE class=MsoNormalTable style="MARGIN-LEFT: -0.75pt; WIDTH: 171px; BORDER-COLLAPSE: collapse; HEIGHT: 124px" cellSpacing=0 cellPadding=0 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 48pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=64>Store

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 59pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=79>Net Income

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 48pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=64>001

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 59pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=79>
89510

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 48pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=64>002

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 59pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=79>
754123

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 48pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=64>003

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 59pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=79>
95400

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 48pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=64>004

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 59pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=79>
85413

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 48pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=64>

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 59pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=79>

</TD></TR></TBODY></TABLE>
The formula for Net Income is =VLOOKUP(“NET INCOME”,'Store #001'!A:C,3,FALSE). I want to replace the Store 001 with a reference to the store number on this summary tab.

Any direction or insight that can be provided would be greatly appreciated.

Try this...

A2 = 001 (as a text entry)
B1 = NET INCOME

Enter this formula in B2:

=VLOOKUP(B$1,INDIRECT("'Store #"&A2&"'!A:C"),3,0)

Copy down as needed.

mctopher · Jun 23, 2011

Beautiful! Thank you so much, I think the think tat was throwing me previously was the need to include the quotes around the range references. Thanks so much for your help!

T. Valko · Jun 23, 2011

mctopher said:
Beautiful! Thank you so much, I think the think tat was throwing me previously was the need to include the quotes around the range references. Thanks so much for your help!

You're welcome. Thanks for the feedback!

dockster · Jul 1, 2014

OK thought I'd add on to this thread since its similar question.

In my "Helper" worksheet, I have the beginning and end values of my table array to use in VLOOKUP. So, Helper/A3 has A25 and Helper/A4 has P35. The A25 and P35 are themselves the result of indirect and match functions. The vlookup function is also within the "Helper" worksheet.

I now want to use vlookup to go back to a different worksheet (Payroll Summary) and then use the A25 and P35 as the table array bounds in that worksheet. I tried:

=VLOOKUP("Total",INDIRECT("'Payroll Summary"&"'!A3:A4"),8,FALSE)

But I get an error. I think I'm messing up on the references and maybe some quotes. Any help appreciated. Thanks.

Help with VLOOKUP using INDIRECT to reference other worksheets

mctopher

Board Regular

Excel Facts

Jonmo1

MrExcel MVP

T. Valko

Well-known Member

mctopher

Board Regular

T. Valko

Well-known Member

dockster

New Member

Similar threads

Forum statistics

Share this page

Help with VLOOKUP using INDIRECT to reference other worksheets

mctopher

Board Regular

Excel Facts

Jonmo1

MrExcel MVP

T. Valko

Well-known Member

mctopher

Board Regular

T. Valko

Well-known Member

dockster

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock