Find the summary of a cell connected to the most recent date.

[tomsmith]

I need to find the summary of a cell, but as more data is input by the user the summary needs to change to always identify the most recent date.

I have a table with Date, Forecast Weight and Actual Weight columns on tab 3 (called 'Weight'). This will be filled out by the user.

On tab 1 (called 'Summary') I need to show the Actual Weight connected to the latest date. The formula =WeightTab!A:1 for example won't work because as soon as another row of data is added, the cell A1 will be out of date.


I fear I may not have explained my problem very well so I've put together an example spreadsheet.


Thanks in advance for any help.

Tom

[benishiryo]

hi Tom, welcome to the forum. you can try:
=LOOKUP(99^99,Weight!$A$2:$A$7,Weight!$C$2:$C$7)

extend the ones in red to whatever range you need.

[:) Sixthsense :)]

I believe the Weight Sheet Data will be continuous

In B5 cell of Summary Sheet

Please Login or Register  to view this content.

In C5 cell of Summary Sheet

Please Login or Register  to view this content.

[zbor]

benishiryo result will give you naswer for last ENTERED date.
If you want last ABSOLUTE date, no matter when entered use:

Please Login or Register  to view this content.

this will also sum together if on same date are more wights entered.

[tomsmith]

Thank you all for your suggestions, I ended up using Zbor's solution in the end.

As an extra favor, could you explain the formula pleas? I would like to learn it rather than just being a Ctrl C&V jockey

Thanks again

Tom

[zbor]

Actually it's better to use:

Please Login or Register  to view this content.

And it's more intuitive

[tomsmith]

Will that not just return the biggest weight value?

Tom

[zbor]

Nope. It has criteria to SUM all values (c2:C100) where date (A2:A100) is equal to last date (MAX(A2:A100)).
In your case that's only one value.

[benishiryo]

my formula
=LOOKUP(99^99,Weight!$A$2:$A$7,Weight!$C$2:$C$7)
my formula will find a huge number (99 to the power of 99) in A2:A7. when it cannot find an exact match, it will return the last row of the result vector in C2:C7. i am assuming the latest date is always at the last row. as mentioned by zbor, it will not work if that's not the case

sixthsense's formula
=OFFSET(Weight!C1,COUNTA(Weight!C:C)-1,0)
he uses 3 parts in this formula.
- first, the reference is where the starting cell is anchored. and that is C1.
- the 2nd portion will decide how many rows from C1 you want to move to. he uses COUNTA in column C to count how many cells are filled. there are 4 filled cells. since there is a header, he deducts 1.
- the 3rd portion is for the columns to move from C1. since you want to return the results in column C & C1 is anchored, he puts a "0" to not move it
so the end result is from C1, move 3 cells down. this will also extract the last row like mine

zbor's latest formula
=SUMIF(Weight!A2:A100, MAX(Weight!A2:A100), Weight!C2:C100)
in the range A2:A100, find the maximum date (dates are actually numbers in Excel by the way) & sum up the weight in column C if it's the maximum date. it will work if you dont have repeated dates. otherwise, it will sum up the weight of 2 dates. should be safe for you too

hope that helps

[zbor]

also, in additon to above post, benishiryo formula will look for last entered date and look it value.
Mine formula will look for maximum date and return SUM of ALL values to that data.

Neither of them is better: they return different result regarding input data.

In your case BOTH of them will return same result (last date is max date and dates are unique).
But in that and only in that case.
And for other cases use what you would expect (if someone for example enter two same dates).

Example with more than one same date:

Please Login or Register  to view this content.

Will return:

Please Login or Register  to view this content.

[zbor]

Another example with different date only in random order:

Please Login or Register  to view this content.

Result:

Please Login or Register  to view this content.

[tomsmith]

That's really useful thanks everyone.

It always helps to know exactly how it works I feel.

Rep for all!

Tom