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

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

GS explained :
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
***This would be C5 in Example1, C6 in Example2***
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

Assuming the only blank cells in Column C are the two you told us about and
assuming your Lookup formula never returns the empty string (""), then I
think you can use this simple formula...

=INDEX(C:C,COUNTA(C:C)-2)

Rick Rothstein (MVP - Excel)




"mp" wrote in message ...

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

Rick Rothstein wrote :
Assuming the only blank cells in Column C are the two you told us about and
assuming your Lookup formula never returns the empty string (""), then I
think you can use this simple formula...

=INDEX(C:C,COUNTA(C:C)-2)

Rick Rothstein (MVP - Excel)

Rick, this doesn't return the expected value (as per my understanding
of OP's scenario). Keeping in mind that the expected value is always
'relative' to the first blank row below the newly inserted data. This
means as rows are inserted the previous data is now above the new row.

Possibly, I misunderstand what the OP wants...

--
Garry

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

GS laid this down on his screen :
Rick Rothstein wrote :
Assuming the only blank cells in Column C are the two you told us about and
assuming your Lookup formula never returns the empty string (""), then I
think you can use this simple formula...

=INDEX(C:C,COUNTA(C:C)-2)

Rick Rothstein (MVP - Excel)

Rick, this doesn't return the expected value (as per my understanding of OP's
scenario). Keeping in mind that the expected value is always 'relative' to
the first blank row below the newly inserted data. This means as rows are
inserted the previous data is now above the new row.

Possibly, I misunderstand what the OP wants...

Sorry, it works if I fill all cells above the blank rows. Might be an
issue, though, if that condition doesn't persist!

--
Garry

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

Rick, this doesn't return the expected value (as per my understanding
of OP's scenario). Keeping in mind that the expected value is always
'relative' to the first blank row below the newly inserted data. This
means as rows are inserted the previous data is now above the new
row.

Possibly, I misunderstand what the OP wants...

Sorry, it works if I fill all cells above the blank rows. Might be an
issue, though, if that condition doesn't persist!

I tried to clarify the conditions under which the formula would work in the
opening sentence. One of the biggest problems I find in answering questions
online is that those asking the question assume we don't need to know all
the details about their setup and how the formula will be used... makes us
all have to guess our way to a solution for them.

Rick Rothstein (MVP - Excel)

Rick Rothstein was thinking very hard :
Rick, this doesn't return the expected value (as per my understanding
of OP's scenario). Keeping in mind that the expected value is always
'relative' to the first blank row below the newly inserted data. This
means as rows are inserted the previous data is now above the new
row.

Possibly, I misunderstand what the OP wants...

Sorry, it works if I fill all cells above the blank rows. Might be an
issue, though, if that condition doesn't persist!

I tried to clarify the conditions under which the formula would work in the
opening sentence. One of the biggest problems I find in answering questions
online is that those asking the question assume we don't need to know all the
details about their setup and how the formula will be used... makes us all
have to guess our way to a solution for them.

Rick Rothstein (MVP - Excel)

True enough! Ugh...

--
Garry

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

On Sat, 26 Feb 2011 16:35:27 -0600, "mp" wrote:

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


If
your data starts in C1
no blank entries until the last data entry
no entries AFTEr the formula in Column C

then
=OFFSET($C$1,COUNTA($C:$C)-3,0)

"GS" wrote in message
...
Rick Rothstein was thinking very hard :
Rick, this doesn't return the expected value (as per my understanding
of OP's scenario). Keeping in mind that the expected value is always
'relative' to the first blank row below the newly inserted data. This
means as rows are inserted the previous data is now above the new
row.

Possibly, I misunderstand what the OP wants...

Sorry, it works if I fill all cells above the blank rows. Might be an
issue, though, if that condition doesn't persist!

I tried to clarify the conditions under which the formula would work in
the opening sentence. One of the biggest problems I find in answering
questions online is that those asking the question assume we don't need
to know all the details about their setup and how the formula will be
used... makes us all have to guess our way to a solution for them.

Rick Rothstein (MVP - Excel)

True enough! Ugh...

--
Garry

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


Thanks Garry and Rick,
Sorry for not explaining all conditions( i didn't know the possibility of
empty rows above
the area I was looking at would matter)
Garry's method of Offset appears to work for my needs as it doesn't depend
on
the conditions at the top of the column (which have some headers and summary
stuff
and possible blank cells)
It appears I don't even need to name the cell, as =OFFSET(C[x],-4,0) works
perfect.
where [x] is the current row with the lookup function, and -4 takes care of
the two
blank rows above and the one last entered data row above that to get to the
penultimate
data row.
Thanks again for everyone's ideas and help
mark