# Updating cell from table

#### marquez313

##### New Member
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### nbrcrunch

##### Well-known Member
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)

#### HalfAce

##### MrExcel MVP
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.

#### marquez313

##### New Member
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!

#### nbrcrunch

##### Well-known Member
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:

#### marquez313

##### New Member
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.

#### nbrcrunch

##### Well-known Member
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.

#### nbrcrunch

##### Well-known Member
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.

#### marquez313

##### New Member
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.)

#### nbrcrunch

##### Well-known Member
=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.

Replies
0
Views
177
Replies
1
Views
496
Replies
2
Views
178
Replies
1
Views
287
Replies
3
Views
337

1,191,690
Messages
5,988,107
Members
440,126
Latest member
duque00

### 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.

### Which adblocker are you using?

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

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