Sum of Values Accross Multiple Columns in Multiple Worksheets Using VLOOKUP

[DrowningInQuicksand]

Hello,

I am looking for the proper syntax to generate a sum of the bottom-most cell in each column across multiple worksheets.
My current formula looks like this: =VLOOKUP(9.99999999999999E+307,'Z:\Warehouse\Inventory 2013\Spray Pens\[Spray Pens.xls]Anti-Static'!D$1:D$300,1)
but the above formula only returns required value from only one of the columns.
The only relevant columns in the following spreadsheet are those named Balance: https://www.dropbox.com/Spray Pens.xls.

Thank you.

 

[Jerry Sullivan]

Hi DrowningInQuicksand,

If the row holding the last value varies for different columns and different sheets, I don't think there's a formula alternative that eliminates the need to repeat a similar expression for each column.

You can shorten the formula a bit through the use of a defined Name to hold BigNum, but I'm not seeing how you could improve significantly beyond that.

=Sum(VLOOKUP(BigNum,'Z:\Warehouse\Inventory 2013\Spray Pens\[Spray Pens.xls]Anti-Static'!D$1:D$300,1),
VLOOKUP(BigNum,'Z:\Warehouse\Inventory 2013\Spray Pens\[Spray Pens.xls]Anti-Static'!R$1:R$300,1),
VLOOKUP(BigNum,'Z:\Warehouse\Inventory 2013\Spray Pens\[Spray Pens.xls]Anti-Static'!Y$1:Y$300,1),
VLOOKUP(BigNum,'Z:\Warehouse\Inventory 2013\Spray Pens\[Spray Pens.xls]Odor-Less'!I$1:Y$300,1),...and so on....

 

[DrowningInQuicksand]

Thank you for the feedback.