To have a 'fixed cell' be equal to the last data entered cell in a column

[Mathew P Bennett]

Good Evening All,

I have a worksheet, example below.
I have frozen the panes to always show rows 1-3.
I wish the cell A3 to be the same as the last 'non-blank' cell in Column
A.(See explanation below).

A B
1
2 Header Header
3__________________
4 M1004
5 M1005
6 M1002
7 M1003
8 M1006
9 M1001
10

So in this case, at present, A3 would be M1001,(A9), but when I enter a new
value in A10, (eg M1008), I would like A3 to automatically update to A10 ie
M1008.
I think that maybe INDIRECT or OFFSET maybe involved but am fairly
unfamiliar with these functions.
Any help on this would be most appreciated.
Cheers,

Mathew

 

[Mathew P Bennett]

Hi Dave,
Thank you very much, I had the feeling that OFFSET would be involved.
However, the range in Column A is ever increasing, and I would like the
formula to accommodate this ever increasing range.
Any thoughts?
Cheers for your help
Mathew

 

[Mathew P Bennett]

Got it to work, Cheers again Dave

Hi Dave,
Thank you very much, I had the feeling that OFFSET would be involved.
However, the range in Column A is ever increasing, and I would like the
formula to accommodate this ever increasing range.
Any thoughts?
Cheers for your help
Mathew