Vlookup question

[aka_krakur]

Okay, I'll try & make this as simple as I can.
I have two worksheet tabs. 1. Named: Sheet2 & 2. Named: 2524-Pivot
In Sheet2 I have the following layout:
A B C D E
MM/YY MM/YY MM/YY
Code Reported Device Jan-06 Feb-06 Mar-06
2524 Failure to Advance

Okay, so under let's say C3 I am trying to write a Vlookup formula. A normal one would be =VLOOKUP(C2,'2524-Pivot'!$A$3:$J$24,10,FALSE) as Worksheet Tab 2524-Pivot is where the data is I want it to look up according to the MM/YY.
It works great; however, I am going to be creating a crazy amount of these Pivot-table worksheets and each one will be named code-Pivot
So I wanted to know if anyone knew if it was possible to do a Vlookup that would automatically grab the A column (Code) and then just put the -Pivot part of the table_array name.
I tried the following (which didn't work:
=vlookup(c2,'a3&-Pivot'!$a$3:$j$24,10,false) and some similar.

anyone out there can help, it would be greatly appreciated. if it's possible all I would have to do would be to copy and paste these in this huge spreadsheet with many mm/yy fields.

Please help.

Thanks,
David

[Bernard Liengme]

Try Help under INDIRECT. You can construct a formula that takes text from a
cell
Suppose A1 has the value 1, then =INDIRECT("Sheet"&A1&"!C1") is equivalent
to =Sheet1!C1

By the way: your WORKBOOK is make up of WORKSHEETS and, maybe, CHARTSHEETS
The TABS are just the tools used to open the sheets.
So you refer to the worksheet named 2524-Pivot, not to the tab with that on
it.
Your workbook has worksheets named Sheet2 and 2524-Pivot
Using the correct terminology helps getting results from Help and from other
users
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"aka_krakur" <[email protected]> wrote
in message news:[email protected]...
>
> Okay, I'll try & make this as simple as I can.
> I have two worksheet tabs. 1. Named: Sheet2 & 2. Named: 2524-Pivot
> In Sheet2 I have the following layout:
> A B C D
> E
> MM/YY MM/YY
> MM/YY
> _Code__________Reported_Device_______Jan-06_________Feb-06_________Mar-06_
> 2524 Failure to Advance
>
> Okay, so under let's say C3 I am trying to write a Vlookup formula. A
> normal one would be =VLOOKUP(C2,'2524-Pivot'!$A$3:$J$24,10,FALSE) as
> Worksheet Tab 2524-Pivot is where the data is I want it to look up
> according to the MM/YY.
> It works great; however, I am going to be creating a crazy amount of
> these Pivot-table worksheets and each one will be named *-code-*-Pivot
> So I wanted to know if anyone knew if it was possible to do a Vlookup
> that would automatically grab the A column (Code) and then just put the
> -Pivot part of the table_array name.
> I tried the following (which didn't work:
> =vlookup(c2,'a3&-Pivot'!$a$3:$j$24,10,false) and some similar.
>
> anyone out there can help, it would be greatly appreciated. if it's
> possible all I would have to do would be to copy and paste these in
> this huge spreadsheet with many mm/yy fields.
>
> Please help.
>
> Thanks,
> David
>
>
> --
> aka_krakur
> ------------------------------------------------------------------------
> aka_krakur's Profile:
> http://www.excelforum.com/member.php...o&userid=36905
> View this thread: http://www.excelforum.com/showthread...hreadid=566200
>

[SimonCC]

This might work:
=VLOOKUP(C2,INDIRECT("'"&A3&"-Pivot'!$A$3:$J$24"),10,FALSE)

-Simon


"aka_krakur" wrote:

>
> Okay, I'll try & make this as simple as I can.
> I have two worksheet tabs. 1. Named: Sheet2 & 2. Named: 2524-Pivot
> In Sheet2 I have the following layout:
> A B C D
> E
> MM/YY MM/YY
> MM/YY
> _Code__________Reported_Device_______Jan-06_________Feb-06_________Mar-06_
> 2524 Failure to Advance
>
> Okay, so under let's say C3 I am trying to write a Vlookup formula. A
> normal one would be =VLOOKUP(C2,'2524-Pivot'!$A$3:$J$24,10,FALSE) as
> Worksheet Tab 2524-Pivot is where the data is I want it to look up
> according to the MM/YY.
> It works great; however, I am going to be creating a crazy amount of
> these Pivot-table worksheets and each one will be named *-code-*-Pivot
> So I wanted to know if anyone knew if it was possible to do a Vlookup
> that would automatically grab the A column (Code) and then just put the
> -Pivot part of the table_array name.
> I tried the following (which didn't work:
> =vlookup(c2,'a3&-Pivot'!$a$3:$j$24,10,false) and some similar.
>
> anyone out there can help, it would be greatly appreciated. if it's
> possible all I would have to do would be to copy and paste these in
> this huge spreadsheet with many mm/yy fields.
>
> Please help.
>
> Thanks,
> David
>
>
> --
> aka_krakur
> ------------------------------------------------------------------------
> aka_krakur's Profile: http://www.excelforum.com/member.php...o&userid=36905
> View this thread: http://www.excelforum.com/showthread...hreadid=566200
>
>