Updating cell from table

marquez313

New Member
Joined
Mar 10, 2009
Messages
18
I'm using Excel 2007 to keep a database of daily settlements for financial futures as well as a variety of studies for these daily values. I want to have one page that I can print every morning with the relevent information for the day. So what i'm trying to figure out is how to make the cell on my "Settlements" page always read the last cell of my data column or find the average of the last five cells, for example.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
you really didn't provide that much detail.

If column B contained a series of numeric values & I wanted to find the last value entered (in other words, the last record/row)

=LOOKUP(9.9999999999999E+307,B2:B28)
 
Upvote 0
Hello marquez313, welcome to the board.
Here's a way to display the last numeric value in a column from a different sheet.
On your Settlements sheet, in the column of interest, give that range (or the entire column) a defined range name. (In my example I named it 'LookupRng'.)
Then in the cell you want that last value to be displayed you can use the formula:
Code:
=LOOKUP(9.99999999999999E+307,LookupRng)

This (and the averaging, etc.) could be done easily with vba but I don't know if that's an option for you or not.

Hope it helps.
 
Upvote 0
a little more info:
column b on sheet 1 contains the daily settlement prices for the 10 year traesury note future from January 2 to present. I want cell A1 on sheet 2 to display the most recent settlement value. also, i want cell A2 to return the five day moving average from column b on sheet 1. I was under the impression that the lookup function was replaced with VLOOKUP. as such, i am unclear on the set-up of this function. thanks!
 
Upvote 0
Try it first, then post what doesn't work. Since you say that column A on the first sheet is what your after, then:

=LOOKUP(9.9999999999999E+307,Sheet1!A2:A28)

Where "Sheet1" is the name of the sheet that contains the data. Merely replace with whatever your sheet is named.
 
Last edited:
Upvote 0
we have a winner! so now i have all the settlements updating on one main page. the last thing i was hoping to do is have 5-day moving average that will update with each new settlement. any ideas? thanks again for your time and attention.
 
Upvote 0
I don't understand the concept of: " 5-day moving average." Explain in detail.

Also, I don't remember if you mentioned the version of Excel you are using. Please post version.
 
Upvote 0
The more I think about, the more I come to the tenative conclusion you are looking to use the SUMIF() function. Look at your inline help (built-in application help)

Essentially, you are looking for a formula that is something like

sumif Dates>=(today()-5)

If the values you are summing are column A and dates Column B, then

=sumif(B:B,">="&today()-5,A:A)/countif(B:B,">="&today()-5)

I may not have the syntax 100% correct, but it will get you started.
 
Upvote 0
5 day moving average is the average price looking back 5 consecutive days. so as a new day of data is added, the oldest is dropped. popular way of looking at stocks, futures, etc. at different points (i.e. 5 day MA, 50 day MA, etc.)
 
Upvote 0
=average(if(a:a > = today()-5,b:b))

The above is an array formula. Instead of just pressing Enter, you must hold down Control AND Shift and then press Enter.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top