Just a slight tweak:
=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRU E,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A20"),A1)0 ,0))&"'!A2:G20"),2,0)
Changed the range in the COUNTIF function.
Biff
"Biff" wrote in message
...
Hi!
First you had 2 sheets to lookup, now you have "50+".
How many is "50+"? 52? 77? 128?
<vbg
OK, try this......
The lookup range has to be EXACTLY the same in ALL sheets.
Create a list of all the sheet names. If your sheet names are PMn this
will be easy. Just enter PM1 in the first cell and drag down.
Give this list a name. Something like SheetList.
Enter this formula using the key combo of CTRL,SHIFT,ENTER:
=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRU E,COUNTIF(INDIRECT("'"&SheetList&"'!A2:G20"),A1)0 ,0))&"'!A2:G20"),2,0)
No error checking in this!
Biff
"John" wrote in message
...
I have 50+ worksheets this was just an example
"John" wrote:
I have three worksheets: Main, PM1, PM2
I have a named range "PM_Ranges=PM1:PM2!$A$2:$G$20 "
Each worksheet has the same amount of columns. Column A in worksheets
PM1
and PM2 has a unique reference "Project" for each of the worksheets.
Worksheet Main column A has all the "Projects".
I am attempting to use Main as a combination of all worksheets by
attempting
to search the PM_Range and get all the corresponding column information
for a
given project.
I have attempted the following:
= vlookup(Main!A1,PM_Range,2,false) and I only get #Value.
but if I use
= vlookup(Main!A1, PM1!$A$2:$G$20,2,false)
it works but only for the PM1 worksheet
I need it to search all worksheets.
Any thoughts?
|