Display Last Number Used

Try

=INDEX(A:A,MATCH(REPT(CHAR(255),255),A:A))

=INDEX(Progress!BA:BA,MATCH(REPT(CHAR(255),255),BA:BA))

I inputted the above formulat - but it returns #N/A

Have i done something wrong...

Craig

try:

=INDEX(Progress!BA:BA,MATCH(REPT(CHAR(255),255),Progress!BA:BA))

I amended it to

=INDEX(Progress!BA:BA,MATCH(REPT(CHAR(255),255),Progress!BA:BA))

Which seemed to work fine.

But it stopped at 12345/109 - but there are alot more beyond 109....

Any Ideas....

Craig

Is the list a mix of numbers and strings? If so, try

=INDEX(Progress!BA:BA,MAX(MATCH(99^99,Progress!BA:BA),MATCH(REPT(CHAR(255),255),Progress!BA:BA)))

try this:

=LOOKUP(2,1/(Progress!BA1:BA10000<>""),Progress!BA1:BA10000)

The INDEX - MATCH still returns a value of #N/A

The LOOKUP formula stopped at 109 aswell....

The list only contains the PO numbers or Blanks...

Craig

Can you upload example workbook?

make sure to remove classified data.

Try this.....
PO's on Sheet 1
Sheet 2 showing last PO...

Formula showing 109 as the last one, when it clearly isnt...

Craig

¸Last value, that formula return is 671015/109
And that's correct result...

Or you want a row number? 119.

Then yopu can use: =LOOKUP(2, 1/(A1:A10000<>""), ROW(A1:A10000))

109 isnt the last one though - Row 98 shows a 113 which would be the last one.

Is it because of the spaces....

Craig

OK.. So you need biggest value, not last value.. Be back with some result.

This is shortened version with IFERROR function that Marcol mentioned:

="671015/"&MAX(IFERROR(--SUBSTITUTE(A2:A10000, "671015/", ""), ""))

comfirmed with ctrl+shift+enter

but only for XL2007 and newer

Thanks for that.....That works a treat....

Great Job

Craig