Hi Pete,
I wanted to use the formula for 6 sheets, I managed to do for five sheets , (in excel 2007) then I have a warning as there are too many inserted formula.
I am not good at codes, what can I to use this for more then 5 sheets.
=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))
Thanks
Pete_UK wrote:
Re: VLOOKUP formula searching multiple worksheets ??
21-Feb-07
You will need a construct along these lines
=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0))
"not present"
VLOOKUP(A2,Wrk3!$F:$G,2,0))
VLOOKUP(A2,Wrk2!$F:$G,2,0))
VLOOKUP(A2,Wrk1!$F:$G,2,0)
This is all one formula, looking in turn at worksheets 1, 2 and 3
I've just manually split it here to avoid awkward line breaks
Hopefully you can see the symmetry, and how to extend it to
worksheets
Hope this helps
Pet
On Feb 21, 7:15 pm, (e-mail address removed) wrote:
Previous Posts In This Thread:
VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheet
using the VLOOOKUP formula or any other formula
I have my VLOOKUP formula(column B) and list of values(column A
(sorted in ascending order) on Worksheet 7 and I need to search fo
these values in Worksheets 1,2,3,4,5& 6 Column F
Is this possible with Excel ? I've tried this by using the formul
VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE
but I get a #VALUE! error.
Re: VLOOKUP formula searching multiple worksheets ??
You will need a construct along these lines
=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0))
"not present"
VLOOKUP(A2,Wrk3!$F:$G,2,0))
VLOOKUP(A2,Wrk2!$F:$G,2,0))
VLOOKUP(A2,Wrk1!$F:$G,2,0)
This is all one formula, looking in turn at worksheets 1, 2 and 3
I've just manually split it here to avoid awkward line breaks
Hopefully you can see the symmetry, and how to extend it to
worksheets
Hope this helps
Pet
On Feb 21, 7:15 pm, (e-mail address removed) wrote:
I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names
Where A2 is the lookup value on the formula sheet
MySheets is a named range consisting of a list of the sheet names you want
to look at
A2:A200 refers to the sheets being looked at
A2:C200 is the Lookup_Array on the sheets being looked at
=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0
Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line
=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0
HT
Regards
Howar
Are you looking to add up all column G values from all sheets when it finds
Are you looking to add up all column G values from all sheets when it finds
the lookup name in column F of each sheet? If so, you could just use SUMIF
=SUMIF(Wrk1!F:F,A1,Wrk1!G:G)+SUMIF(Wrk2!F:F,A1,Wrk2!G:G)+etc
Just a plain old SUM function works with the Wrk1:Wrk6 idea, but summing
based on a condition does not. I also could not get SUMPRODUCT to accept a
worksheet range.
Re: VLOOKUP formula searching multiple worksheets ??
Thanks so much, Pete. The formula worked great for 6 worksheets but I
have 9 worksheets. It errored out when I added the formula for the 7th
worksheet.
Re: VLOOKUP formula searching multiple worksheets ??
KC,
I am not adding the formula result. It is only a look up and tell
whether it is on the 9 worksheets or not.
I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names.
Where A2 is the lookup value on the formula sheet.
MySheets is a named range consisting of a list of the sheet names you want
to look at.
A2:A200 refers to the sheets being looked at.
A2:C200 is the Lookup_Array on the sheets being looked at.
=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)
Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line.
=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)
HTH
Regards,
Howard
Re: VLOOKUP formula searching multiple worksheets ??
eo
at
to
&MyS=ADheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)
his
las
eet5=AD";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Shee=
t1";"Sh=ADeet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A=
2:A200"),A=AD2)>0),0))&"'!A2:C200"),2,0)
Beautiful! Ths formula worked for 9 wrksheets . Thanks a great to all
who contributed!
Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.
Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.
=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)
HTH
REgards,
Howard
I have an error for 6 sheets
Hi Pete,
I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.
I am not good at codes, what can I to use this for more then 5 sheets.
=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))
Thanks
I have an error for 6 sheets
Hi Pete,
I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.
I am not good at codes, what can I to use this for more then 5 sheets.
=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))
Thanks
formula for 6 sheets
Hi Pete,
I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.
I am not good at codes, what can I to use this for more then 5 sheets.
=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))
Thanks
Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorial...df-52898c6aa5d7/iis-70-extensionless-url.aspx