Calculation on most recent figures only

J

Justin C

I'm adding one row of data to a table each day, the first column is the
date. On another worksheet in the same workbook I'd like to show a
calculation based on the most recent data only, the data on the second
worksheet is mainly static apart from a daily update based on the new
figures.

Any suggestions on how to go about this?

Thank you for any help you can give.

Justin.
 
B

Bernie Deitrick

Justin,

To return the latest (lowest down the sheet) values from columns B to F,
enter this anywhere in column B of your other sheet, and copy over to column
F. You can then use it for the basis of your calculations.

Just use a date (other than Christmas, 2025) that is further in the future
than any of the dates in your table. Change the Sheet Name to reflect the
actual name of the sheet with the table.

=VLOOKUP(DATEVALUE("12/25/2025"),'Sheet Name'!A:F,COLUMN())

HTH,
Bernie
MS Excel MVP
 
D

Duke Carey

You can use this formula to find the row number of the last entry in column A

=MATCH(9.99999999999999E+307,A:A)

Once you have the row number, you can pull any item from that row into your
second spreadsheet using the INDEX() function, i.e.,

=INDEX(Sheet1!A1:Z500,MATCH(9.99999999999999E+307,A:A),2)

to get the value in the 2d column (column B) of that row
 
J

Justin C

Justin,

To return the latest (lowest down the sheet) values from columns B to F,
enter this anywhere in column B of your other sheet, and copy over to column
F. You can then use it for the basis of your calculations.

Just use a date (other than Christmas, 2025) that is further in the future
than any of the dates in your table. Change the Sheet Name to reflect the
actual name of the sheet with the table.

=VLOOKUP(DATEVALUE("12/25/2025"),'Sheet Name'!A:F,COLUMN())

HTH,
Bernie
MS Excel MVP
Click to expand...

Yes it does thank you. I can see how that works and will save impliment
it shortly.

Justin.
 
J

Justin C

You can use this formula to find the row number of the last entry in column A

=MATCH(9.99999999999999E+307,A:A)
Click to expand...

I don't understand how this works. I've looked at the MATCH function and
don't follow what's going on above.

Once you have the row number, you can pull any item from that row into your
second spreadsheet using the INDEX() function, i.e.,

=INDEX(Sheet1!A1:Z500,MATCH(9.99999999999999E+307,A:A),2)
Click to expand...

I can see how the INDEX function works and (if the first function does
waht you say) how it would work but don't understand it because of my
confusion with the MATCH function.

Justin.
 
Top