Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculation on most recent figures only
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. -- Justin C by the sea. |
#2
|
|||
|
|||
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 "Justin C" wrote in message e.com... 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. -- Justin C by the sea. |
#3
|
|||
|
|||
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 "Justin C" wrote: 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. -- Justin C by the sea. |
#4
|
|||
|
|||
On 2005-04-27, Bernie Deitrick <deitbe@consumer wrote:
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 Yes it does thank you. I can see how that works and will save impliment it shortly. Justin. -- Justin C by the sea. |
#5
|
|||
|
|||
On 2005-04-27, Duke Carey wrote:
You can use this formula to find the row number of the last entry in column A =MATCH(9.99999999999999E+307,A:A) 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) 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. -- Justin C by the sea. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate minus figures | Excel Discussion (Misc queries) | |||
Calculate minus figures only | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |