VLookup accross multiple worksheets

[jedman]

I am trying to use the vlookup to look at account numbers over 40 worksheets,
Is this possible to have the formula look in each worksheet without having to
type 40 different vlookups? I tried naming the range on each worksheet and
that is not working

 

[John C]

What makes an account appear on one tab, and not the other 39? Is it just
random? Or is it sorted?

 

[Teethless mama]

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/

....then use this formula

=VLOOKUP("your_criteria",THREED(Sheet1:Sheet40!A1:B100),2,0)

 

[John C]

The site has been hacked.
--
** John C **

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/

...then use this formula

=VLOOKUP("your_criteria",THREED(Sheet1:Sheet40!A1:B100),2,0)

 

[Teethless mama]

Try this site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

 

[franciz]

would you elaborate more?
In which Sheet are your lookup value?
Where will be your table arrays and which column do you want to return the
value?
Are your tables in your other sheets identical?

 

[jedman]

I have 40 departments all on separate workbooks. They have the same chart of
accounts but not every department uses all of the accounts. I want my
consolidated sheet to find the account number on each workbook and sum them
to give me a consolidated total. Is this even possible?

 

[John C]

That answers one question. Are you looking for a SUMMATION sheet for each
account? For example, I have account number 12345, it happens to be in 25 of
the 40 departments (and therefore on 25 different tabs), are you looking for
the total for that account for all departmens? Or is your summation sheet
more along the lines of, I have an account, and I need to know that account
amount for a specific department.
I have a feeling it is the former, in which case, Teethless' suggestion
might be your best move.

 

[franciz]

I don't undertsand your explanation very well.
However, I think this would get you started.

Assumptions:

1. B2: The Account number to lookup are in the Summary Sheet
2. A1:C100 is the range to lookup on various sheets, where column A holds
the Account number and column C holds value you want.
3. Sheets to lookup : Sheet1, Sheet2, Sheet3, Sheet4.
4. The Account number to be looked up is on only one of the sheets e.g. if
value 108 is in sheet 2 and in sheet 4, the following formula will return
the value in the first sheet i.e. sheet 2.

=IF(COUNTIF(Sheet1!A1:A100,B2),VLOOKUP(B2,Sheet1!A1:C100,3),IF(COUNTIF(Sheet2!A1:A100,B2),VLOOKUP(B2,Sheet2!A1:C00,3,,0)
,IF(COUNTIF(Sheet3!A1:A100,B2),VLOOKUP(B2,Sheet3!A1:C100,2,0),IF(COUNTIF(Sheet4!A1:A100,B2),VLOOKUP(B2,Sheet4!A1:C100,2, 0),"not found in all sheet"))))

Referencing more than 7 sheets would be complicated (In Excel, 7 is the
maximum number of nested levels of functions - see the Excel help file).

You may consider a macro or using Pivot table