Can it be done for 5 or more spreadsheets? Just trying to figure out if
there is a way I can get it to look it up across the board so I don't have to
make an Access Database.

Thanks,
Brandon

No, VLOOKUP does not support 3D references.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Brandon" wrote:

Can it be done for 5 or more spreadsheets? Just trying to figure out if
there is a way I can get it to look it up across the board so I don't have to
make an Access Database.

Thanks,
Brandon

Hi,

Although you can't do 3-D references with VLOOKUP, you can write a formula
of the form

=VLOOKUP(A1,Sheet2!A1:D100,2,FALSE)+VLOOKUP(A1,She et3!A1:D100,2,FALSE)+VLOOKUP(A1,Sheet4!A1:D100,2,F ALSE) and so on.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Brandon" wrote:

Can it be done for 5 or more spreadsheets? Just trying to figure out if
there is a way I can get it to look it up across the board so I don't have to
make an Access Database.

Thanks,
Brandon

Im trying to use the formula
=SUMIF('P&Ldata'!$E:$E,$A6,'P&Ldata'!I:I)
It looks at a column on another sheet to determine all the instances of the
word Sales and return the subtotal of figure in a corresponding column.
I have a cell which has in it the text P&Ldata and I want to use this to
change the worksheet the formula looks at, I've looked at lots of suggestions
involving INDIRECT but can't get it to work.



"Shane Devenshire" wrote:

Hi,

Although you can't do 3-D references with VLOOKUP, you can write a formula
of the form

=VLOOKUP(A1,Sheet2!A1:D100,2,FALSE)+VLOOKUP(A1,She et3!A1:D100,2,FALSE)+VLOOKUP(A1,Sheet4!A1:D100,2,F ALSE) and so on.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Brandon" wrote:

Can it be done for 5 or more spreadsheets? Just trying to figure out if
there is a way I can get it to look it up across the board so I don't have to
make an Access Database.

Thanks,
Brandon

Try it like this:

=SUMIF(INDIRECT("'"&B6&"'!E:E"),$A6,INDIRECT("'"&B 6&"'!I:I"))

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
I'm trying to use the formula
=SUMIF('P&Ldata'!$E:$E,$A6,'P&Ldata'!I:I)
It looks at a column on another sheet to determine all the instances of
the
word Sales and return the subtotal of figure in a corresponding column.
I have a cell which has in it the text P&Ldata and I want to use this to
change the worksheet the formula looks at, I've looked at lots of
suggestions
involving INDIRECT but can't get it to work.



"Shane Devenshire" wrote:

Hi,

Although you can't do 3-D references with VLOOKUP, you can write a
formula
of the form

=VLOOKUP(A1,Sheet2!A1:D100,2,FALSE)+VLOOKUP(A1,She et3!A1:D100,2,FALSE)+VLOOKUP(A1,Sheet4!A1:D100,2,F ALSE)
and so on.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Brandon" wrote:

Can it be done for 5 or more spreadsheets? Just trying to figure out
if
there is a way I can get it to look it up across the board so I don't
have to
make an Access Database.

Thanks,
Brandon

See example http://cjoint.com/?citAACtV8t

=SUMPRODUCT(SUMIF(INDIRECT("Feuil"&ROW(1:4)&"!A2:A 6"),$A$2,INDIRECT
("Feuil"&ROW(1:4)&"!B2:B6")))

JB
http://boisgontierjacques.free.fr

On 6 fév, 17:54, Brandon wrote:
Can it be done for 5 or more spreadsheets? *Just trying to figure out if
there is a way I can get it to look it up across the board so I don't have to
make an Access Database.

Thanks,
Brandon

This does most of what I want it to do, is there anything that can be added
to it to make it show text results as well?

"JB" wrote:

See example http://cjoint.com/?citAACtV8t

=SUMPRODUCT(SUMIF(INDIRECT("Feuil"&ROW(1:4)&"!A2:A 6"),$A$2,INDIRECT
("Feuil"&ROW(1:4)&"!B2:B6")))

JB
http://boisgontierjacques.free.fr

On 6 fév, 17:54, Brandon wrote:
Can it be done for 5 or more spreadsheets? Just trying to figure out if
there is a way I can get it to look it up across the board so I don't have to
make an Access Database.

Thanks,
Brandon

Hi Brandon,

Peo Sjoblom sent me a workbook that does a Vlookup across 8 worksheets,(more
if needed). It is brilliant in my estimation. He even e-mailed me an
explanation of how the formula works, which I cannot completely get my head
around.

Here is the formula and must be array entered (Ctrl + Shift + Enter).

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)0) ,0))&"'!A2:C200"),3,0)

Whe A2 is the look-up value
Whe MySheets is a named range consisting of a list of all the worksheet
you want to look up.
Whe A2:C200 is the lookup array of each sheet.

This will return the lookup value of column C that the lookup value finds on
whatever sheet, note the 3 just before the ,0) at the end of the formula.

You will need to adjust the ranges to suit your workbook.

This is what the formula would look like if you did not use a named range
for the worksheets but instead entered the worksheets names in the formula.
Much better to use a named range. Note that in this formula it returns the
value in Column B per the 2 at the end.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2:A200"),A2)0),0))&"'!A2:C200"),2,0)

I will be glad to help you get this set up if you need additional help.

HTH
Regards,
Howard

"Brandon" wrote in message
...
Can it be done for 5 or more spreadsheets? Just trying to figure out if
there is a way I can get it to look it up across the board so I don't have
to
make an Access Database.

Thanks,
Brandon