display last entry in column

How about:

=MATCH(9.9E+307,Sheet1!A:A)

HTH,

Smitty

Hi Nick,
If you want to show in A1 of sheet2, the last value in column A of sheet1, no matter what that value is, then the formula Smitty provided should work perfectly.
If however you want to show the last value that is greater than 0, then you might try this: (In A1 of sheet2)
=LOOKUP(2,1/(Sheet1!A1:A65535>0),Sheet1!A1:A65535)
Note, the only weakness is that it must exclude cell A65536 of sheet 1 as it doesn't seem to like working on the entire column. (?)

Hiya Smitty! How's the wife & kid(s)?

Dan

Thanks to both of you. HalfAce's turned out to be the one for me as it returned text strings as well as numbers.

thanks again

Nick

I'm not sure who posted this, but here are some alternatives:

The last used row in column A can be found with:

=ROW(INDIRECT(ADDRESS(MAX(SUBSTITUTE_ME),1,2)))

Where SUBSTITUTE_ME =
MATCH(9.9E+307,A:A) if your data has only numbers
MATCH(rept("z",255),A:A) if your data has only text
or MATCH(9.9E+307,A:A), MATCH(rept("z",255),A:A) if your data has text and numbers

Click to expand...

Smitty

(We're doing great Dan, you?)

Can any of these be adapted to return the 2nd from last used row, 3rd from last etc?I've no idea how they work so have even less idea how to tinker with them.(numbers and text)

Nick

[1] If one is interested in the last numeric value in column A...

=LOOKUP(9.99999999999999E+307,Sheet1!A:A)

[2] If one is interested in the last text value (which can be a formula blank) in column A...

=LOOKUP(REPT("z",255),Sheet1!A:A)

Note that the lexical ordering is ASCII-based.

[3] If one is interested in any value (e.g., number, date, time, truth value (TRUE/FALSE), and error value...

=IF(ISBLANK(Sheet1!A65536),LOOKUP(2,1/(1-ISBLANK(Sheet1!A1:A65535)),Sheet1!A1:A65535),Sheet1!A65536)

This is a pretty expensive formula, so it should not be invoked in lieu if either [1] or [2].

What to invoke if column A consists of numbers (recall that dates and are also sumbers) and text like ""?

Obviously, [3] can be invoked. An alternative would be a set of fast formulas...

B1:

=MATCH(9.99999999999999E+307,Sheet1!A:A)

B2:

=MATCH(REPT("z",255),Sheet1!A:A)

B3:

=INDEX(Sheet1!A:A,CHOOSE(COUNT(B1:B2)+1,"",SUMIF(B1:B2,"<>#N/A"),MAX(B1:B2)))

smokenack said:

Can any of these be adapted to return the 2nd from last used row, 3rd from last etc?I've no idea how they work so have even less idea how to tinker with them.(numbers and text)

Nick

Click to expand...

Would you post 10 rows from column on Sheet1?

I'm at work so can't post my sheet. (unhelpful paranoid IT dept). sheet1 is a form consisting of 9 columns, column B is date, column C is name etc. Sheet2 is another form that takes data from the last used row of sheet1. I have a button on sheet1 that I use to print out sheet2 as I complete each row on sheet1. Obviously once I have completed a row on sheet1 I can no longer print sheet2 with data from previous rows. I would now like to be able to have other sheets that would display the data from 2nd from last used rows etc so that I would now be able to print forms using data from previous rows.

I hope that this makes sense

Nick

smokenack said:

I'm at work so can't post my sheet. (unhelpful paranoid IT dept). sheet1 is a form consisting of 9 columns, column B is date, column C is name etc. Sheet2 is another form that takes data from the last used row of sheet1. I have a button on sheet1 that I use to print out sheet2 as I complete each row on sheet1. Obviously once I have completed a row on sheet1 I can no longer print sheet2 with data from previous rows. I would now like to be able to have other sheets that would display the data from 2nd from last used rows etc so that I would now be able to print forms using data from previous rows.

I hope that this makes sense

Nick

Click to expand...

I didn't ask to post your sheet...