Vlookup referencing tab name

**redlion3** · 03-18-2008, 07:28 PM

is there a way to create a vlookup referencing the tab name?

For example, I have a summary sheet and tabs labeled 1- 200. I would like to create a formula on the summary sheet that says - if the # on the summary sheet corresponds with the tab name, then grab the info from that tab.

Hope this makes sense. Any help would be appreciated. This could save me a lot of time that I would spend linking sheets!

Thanks in advance!

**daddylonglegs** · 03-18-2008, 08:10 PM

You probably need to use the INDIRECT function, e.g. if A1 contains 99 then this formula will lookup B2 in column A of sheet 99 and return the corresponding value from column B

=VLOOKUP(B2,INDIRECT("'"&A1&"'!A:B"),2,0)

**Richard Buttrey** · 03-18-2008, 08:26 PM

Originally Posted by redlion3

is there a way to create a vlookup referencing the tab name?

For example, I have a summary sheet and tabs labeled 1- 200. I would like to create a formula on the summary sheet that says - if the # on the summary sheet corresponds with the tab name, then grab the info from that tab.

Hope this makes sense. Any help would be appreciated. This could save me a lot of time that I would spend linking sheets!

Thanks in advance!

Hi,

Yes, use the INDIRECT() function. e.g.

Please Login or Register  to view this content.

where here summary sheet A4 contains the value '2' meaning sheet2, summary sheet B4 is the lookup value, and A1:B10 is the range looked up on sheet2.

HTH

**redlion3** · 03-18-2008, 09:56 PM

Thank you so much for your help. I'm still a little confused - when I put this into excel Im having trouble following. Would you mind showing it to me in excel?

**Richard Buttrey** · 03-19-2008, 07:57 AM

Originally Posted by redlion3

Thank you so much for your help. I'm still a little confused - when I put this into excel Im having trouble following. Would you mind showing it to me in excel?

Hi,

I'm attaching the workbook with the formula you need. I've simplified the formula since it doesn't need a VLookup if your layouts are as published.

HTH

**bciputra** · 09-12-2012, 01:37 AM

Thank you for this.

**markingpmp** · 02-28-2013, 11:11 AM

I'm doing something similar but using tab "names" and use columns C2, C3, and C4 on each of these tabs as a summary of Org. Req., Bus. Value, and Prob. of Success for example:

Summary sheet

Project name

Project 1.....Org.Reg....Bus Value...Prob of Success.........Ave
Project 2.....=C2........=C3...........=C4...........................C2:C4
Project 3.....=C2........=C3...........=C4

The current formula used is: ='Project 1'!C2....'Project 1'!C3.....'Project 1'!C4

This requires typing names in Summary sheet formulas which does not always happen. Is there a better way?

Thanks,
markingpmp

**arlu1201** · 02-28-2013, 12:04 PM

markingpmp,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.