Lookup a total that changes based on daily input

**PeteBest** · 08-13-2012, 10:46 AM

I have a spreadsheet that I created in Excel 2007. The spreadsheet contains two tabs, one for input and another tab to perform the calculations. This file is a bit strange in that I am calculating changes in weight based on a length(x) times width(y). I would input the daily x y values based on what was used or returned on the first tab. I want a cell at the top of my first tab to reflect the current balances based on the daily changes which are being calculated in a separate tab.

In the separate tab I have a table that I created to perform a calculation using length(x) times width(y) times weight(z) in order to determine the amount of product used or remaining. The math is working right but I was wondering if some kind of lookup or formula could be applied so that the total is reflected at the top of my input tab to reflect this change without having to go to the second tab each time. Each row essentially represents the x and y values used or returned for a given day/date. Hopefully the file will illustrate better than I am stating here.[ATTACH]173961

How can I do this?

Thank you.

Mark

**NBVC** · 08-13-2012, 10:53 AM

Do you mean?

=LOOKUP(9.9999E+307,Calculations!I:I)

copied to next column.

This will return the last number found in column I and J

**PeteBest** · 08-13-2012, 11:31 AM

NBVC,

You're a genius!! How easy was that? Please explain what the formula is doing as I have never seen a LOOKUP used like this.

Thanks for the record breaking speed in replying.

PS I was in Mississauga recently as we have an office there. Brampton to be exact.

---------- Post added at 10:31 AM ---------- Previous post was at 10:27 AM ----------

NBVC,

You're a genius!! How easy was that? Please explain what the formula is doing as I have never seen a LOOKUP used like this.

Thanks for the record breaking speed in replying.

PS I was in Mississauga recently as we have an office there. Brampton to be exact.

**PeteBest** · 08-13-2012, 11:31 AM

NBVC,

You're a genius!! How easy was that? Please explain what the formula is doing as I have never seen a LOOKUP used like this.

Thanks for the record breaking speed in replying.

PS I was in Mississauga recently as we have an office there. Brampton to be exact.

**NBVC** · 08-13-2012, 11:57 AM

The LOOKUP formula looks for the last occurance of a value that is smaller than or equal to a defined value.

In this case we are trying to look for a very large number, 9.99999e+307, which is unlikely to be found, so instead the last number in the column will be the last number found that is less than this large number...

Brampton is just north of Mississauga... I lived in Brampton a while back... I like Mississauga better