Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup accross multiple worksheets
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup accross multiple worksheets
What makes an account appear on one tab, and not the other 39? Is it just
random? Or is it sorted? -- ** John C ** "jedman" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup accross multiple worksheets
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) "jedman" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup accross multiple worksheets
The site has been hacked.
-- ** John C ** "Teethless mama" wrote: 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) "jedman" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup accross multiple worksheets
Try this site:
http://www.download.com/Morefunc/300...-10423159.html "John C" wrote: The site has been hacked. -- ** John C ** "Teethless mama" wrote: 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) "jedman" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup accross multiple worksheets
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" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup accross multiple worksheets
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? "franciz" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup accross multiple worksheets
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. -- ** John C ** "jedman" wrote: 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? "franciz" wrote: 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" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup accross multiple worksheets
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!A 1:C100,3),IF(COUNTIF(Sheet2!A1:A100,B2),VLOOKUP(B2 ,Sheet2!A1:C00,3,,0) ,IF(COUNTIF(Sheet3!A1:A100,B2),VLOOKUP(B2,Sheet3!A 1: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 "jedman" wrote: 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? "franciz" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf accross multiple worksheets | Excel Worksheet Functions | |||
Calculate Average Accross Multiple Worksheets | Excel Discussion (Misc queries) | |||
Finding matching fields accross multiple worksheets | Excel Discussion (Misc queries) | |||
Vlookup accross multiple worksheets | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |