mp submitted this idea :
example 1
I have a column of data
last entered data is in C4
C5 and C6 are blank
C7 has a Lookup formula

example 2
when data is added, a row is inserted below C4
new data is entered in C5
C6 and 7 are blank
C8 has the lookup

etc

I'm trying to figure out a formula I can put into
another cell, say A1, that would =
in example 1 = C3
in example 2 = C4
(the row above the last entered data)
(not the row above the last nonblank cell(the lookup formula)

in other words, in example 1, A1.Value = C3.Value
in example 2, A1 changes to = C4.Value

is there a way to do this with builtin excel formula?
Thanks
mark

Try naming the cell containing the date using local (worksheet) scope,
then ref the named cell in your formula.

Example:
Select the cell containing the blank below the last data entered
In the Namebox type: 'Sheet Name'!DataEntered
Press 'Enter'

Subtitute the actual Sheet.Name between the apostrophes.

In A1 type: =OFFSET(DataEntered,-2,0)

Now, as new rows are inserted at the blank row below the last data
entered, the value in A1 updates to the new data.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc