Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding VLOOKUPS across multiple worksheets
Hi,
I am trying to add several VLOOKUP results obtained from data in several worksheets. My formula is : - =VLOOKUP(A5,'Common Costs - Budget'!A5:X74,2,FALSE)+VLOOKUP(A5,'Brighton - Budget'!A5:X53,2,FALSE)+VLOOKUP(A5,'Hove - Budget'!A5:X56,2,FALSE) I have several worksheets all with similar budget information on and, for example, I need to all the payroll costs from each of the sheets. Not all the sheets will always have costs on so I was thinking of doing lookups and adding them together but I just keep getting the #N/A result. Is there a way to do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding VLOOKUPS across multiple worksheets
Try this€¦
=IF(A5="","",IF(ISNA(VLOOKUP(A5,'Common Costs - Budget'!$A$5:$X$74,2,FALSE)),"",IF(VLOOKUP(A5,'Com mon Costs - Budget'!$A$5:$X$74,2,FALSE)=0,"","["&VLOOKUP(A5,'Common Costs - Budget'!$A$5:$X$74,2,FALSE)&"] "))&IF(ISNA(VLOOKUP(A5,'Brighton - Budget'!$A$5:$X$53,2,FALSE)),"",IF(VLOOKUP(A5,'Bri ghton - Budget'!$A$5:$X$53,2,FALSE)=0,"","["&VLOOKUP(A5,'Brighton - Budget'!$A$5:$X$53,2,FALSE)&"] "))&IF(ISNA(VLOOKUP(A5,'Hove - Budget'!$A$5:$X$56,2,FALSE)),"",IF(VLOOKUP(A5,'Hov e - Budget'!$A$5:$X$56,2,FALSE)=0,"","["&VLOOKUP(A5,'Hove - Budget'!$A$5:$X$56,2,FALSE)&"]"))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "NCarter" wrote: Hi, I am trying to add several VLOOKUP results obtained from data in several worksheets. My formula is : - =VLOOKUP(A5,'Common Costs - Budget'!A5:X74,2,FALSE)+VLOOKUP(A5,'Brighton - Budget'!A5:X53,2,FALSE)+VLOOKUP(A5,'Hove - Budget'!A5:X56,2,FALSE) I have several worksheets all with similar budget information on and, for example, I need to all the payroll costs from each of the sheets. Not all the sheets will always have costs on so I was thinking of doing lookups and adding them together but I just keep getting the #N/A result. Is there a way to do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding VLOOKUPS across multiple worksheets
Hi
Are all of the costs on the same row of each sheet? If so, then insert 2 new sheets called First and Last. Drag them so that they "sandwich" each of your sheets that you wish to add, and then use =SUM(FIrst:Last!B5) or whatever is the cell reference for Payroll Ensure that your Summary sheet is outside of the sandwich, either before First or after Last. -- Regards Roger Govier NCarter wrote: Hi, I am trying to add several VLOOKUP results obtained from data in several worksheets. My formula is : - =VLOOKUP(A5,'Common Costs - Budget'!A5:X74,2,FALSE)+VLOOKUP(A5,'Brighton - Budget'!A5:X53,2,FALSE)+VLOOKUP(A5,'Hove - Budget'!A5:X56,2,FALSE) I have several worksheets all with similar budget information on and, for example, I need to all the payroll costs from each of the sheets. Not all the sheets will always have costs on so I was thinking of doing lookups and adding them together but I just keep getting the #N/A result. Is there a way to do this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding VLOOKUPS across multiple worksheets
Use the ISNA function to "trap" cases where you risk to get a N/A value. Use
that in conjunction with an IF statement. So, e.g. IF (ISNA(<your VLOOKUP formula), <put a blank or zero depending on your need, <your VLOOKUP formula) Keep in mind that the resulting formula will be extremely lengthy to read - but otherwise it should do the trick. Hope this helps. "NCarter" wrote: Hi, I am trying to add several VLOOKUP results obtained from data in several worksheets. My formula is : - =VLOOKUP(A5,'Common Costs - Budget'!A5:X74,2,FALSE)+VLOOKUP(A5,'Brighton - Budget'!A5:X53,2,FALSE)+VLOOKUP(A5,'Hove - Budget'!A5:X56,2,FALSE) I have several worksheets all with similar budget information on and, for example, I need to all the payroll costs from each of the sheets. Not all the sheets will always have costs on so I was thinking of doing lookups and adding them together but I just keep getting the #N/A result. Is there a way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a Row to Multiple Worksheets at Once | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Adding multiple vlookups | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions |