change cell values of to reflect most current entry in a range

[MARYINNEED]

I display (link) one cell on a worksheet page "Houses Sold" from a table
containing a range of cells on another page.

I manually enter the column of numbers, and then go to the next page in the
workbook and change the cell with "Todays" amount.

How do I make the next page reflect the most current entry in my dated row
of data?

Example:

DATE # of houses sold
1 jan = 1
2 jan = 2
3 jan = 3
4 jan = 4
5 Jan = 5

No one wants to see the raw entry data, they only want to see how many
houses I sold today on a seperate page. How do I make that cell on that page
reflect the most current entry from the column of data ....(from today)?

Thanks, please help. Mary

 

[Mike H]

Hi,

I'm not sure I fully understand the question but if you want to return the
value from the last cell in another sheet try this

=INDEX(Sheet2!B:B,MATCH(9.99999999999999E+307,Sheet2!B:B))

On any sheet this will return the last numeric value of sheet 2 column B

Mike

 

[Billy Liddel]

Another way, if you ever need to to show numbers sold on a particular date?

If list is in Sheet2 A1:A20, results in Sheet3

Write the LAbels in B2 & B3 i.e
B2 = Date
B3 = Houses Sold

Insert a comboBox, View, Toolbars, Forms, Combo. Select the combobox tool
and draw it on the sheet. Right-click the combobox and choose Format, in the
input Range type; Sheet2!$A$1:$A$20

Set the cell reference to cell D2 (under the combo box) and click OK.

In C2 enter the formula; =INDEX(Sheet2!A1:A20,D2)
In C3 enter the formula:
=INDEX(Sheet2!B1:B20,MATCH(Sheet3!C2,Sheet2!A1:A20,0))

Together with Mike H's formula you will have more reports of your sales

If the list is ever expanding you might like to create dynamic range names
in the future. You can always post on this later,

Regards
Peter

 

[T. Valko]

This will return the last numeric value from Sheet2 column B:

=LOOKUP(1E100,Sheet2!B:B)