Matching to last value entered

[Guest]

I was wondering whether it is possible to reference to the newest data entry in a column (which will be the last entry in the column).

New data will be continually added so a reference would need to update whenever another piece of data is added.

Thankyou.

 

[Juan Pablo González]

What kind of data are you looking for ? Numeric ? Text ?

 

[Guest]

It will be numerical data that i am wanting to refer to.

Thanks.

 

[Barrie Davidson]

Assuming your data is in column A (and it's all numeric), the following formula will return the value of the last number in that column:

=INDIRECT("A"&COUNT(A:A))

 

[Derek]

This could be one way:
If the column you want to reference is column E, select the entire column below your last entry, press the spacebar once then Control+Enter (to put a single space in every cell). Now put this formula in a different column, say in F1
=INDIRECT("E"&MATCH(" ",E:E,0)-1)
(between " " is a single space).
F1 will now display the value in the cell above the first cell with a single space.
Other formulas that need to refer to the last entry in column E should now reference to cell F1
Hope this helps
Derek
PS Actually adding a single space down to row 65536 uses too much memory, so do it for say 5000 rows. (This method still gives you the last entry even if you have blanks in your column)
This message was edited by Derek on 2002-02-17 21:17

 

[Guest]

Thankyou both very much. Problem solved. Your expertise is appreciated greatly.

 

[Guest]

On 2002-02-17 19:15, Anonymous wrote:
It will be numerical data that i am wanting to refer to. <snip>

Lets say that E is the column of interest:

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

will give you the last row used in E;

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

the last entry in E, and

=ADDRESS(MATCH(9.99999999999999E+307,E:E),5)

the address of the last entry.

 

[Aladin Akyurek]

On 2002-02-17 22:07, Anonymous wrote:

On 2002-02-17 19:15, Anonymous wrote:
It will be numerical data that i am wanting to refer to. <snip>

Lets say that E is the column of interest:

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

will give you the last row used in E;

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

the last entry in E, and

=ADDRESS(MATCH(9.99999999999999E+307,E:E),5)

the address of the last entry.

I didn't mean to post what is above as Anonymous, whence this reply.

Aladin