Hi all, the problem ...in a column I may have a user enter a receipt
number which is incremental so day 1 seperate sheet may have no
receipts and day two (seperate sheet) may have 3 receipts. I want to be
able to pull out the last receipt number from the column for auditing.

Day 1
column a column b column c

day 1 nothing
so use the last receipt number on other worksheet
Day 2
5555



5556

receipt 5556 will be placed as last receipt number day 2 on other
worksheet.

Hope you can help

I really should stop thinking that mine is a unique question and has
not been answered before.

I found the answer in the search function

the answer that works for me is

=LOOKUP(2,1/(1-ISBLANK(r1:r1000)),r1:r1000)

From now on search first ask question later....

pano wrote:
Hi all, the problem ...in a column I may have a user enter a receipt
number which is incremental so day 1 seperate sheet may have no
receipts and day two (seperate sheet) may have 3 receipts. I want to be
able to pull out the last receipt number from the column for auditing.

Day 1
column a column b column c

day 1 nothing
so use the last receipt number on other worksheet
Day 2
5555



5556

receipt 5556 will be placed as last receipt number day 2 on other
worksheet.

Hope you can help

Now that I have fully tried this out, how do I stop it #N/A when there
is no number in the column?????


pano wrote:
I really should stop thinking that mine is a unique question and has
not been answered before.

I found the answer in the search function

the answer that works for me is

=LOOKUP(2,1/(1-ISBLANK(r1:r1000)),r1:r1000)

From now on search first ask question later....

pano wrote:
Hi all, the problem ...in a column I may have a user enter a receipt
number which is incremental so day 1 seperate sheet may have no
receipts and day two (seperate sheet) may have 3 receipts. I want to be
able to pull out the last receipt number from the column for auditing.

Day 1
column a column b column c

day 1 nothing
so use the last receipt number on other worksheet
Day 2
5555



5556

receipt 5556 will be placed as last receipt number day 2 on other
worksheet.

Hope you can help

How about just using an IF(ISNA(..),"",(..)) construct:
=IF(ISNA(LOOKUP(2,1/(1-ISBLANK(R1:R1000)),R1:R1000)),"",LOOKUP(2,1/(1-ISBLANK(R1:R1000)),R1:R1000))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pano" wrote in message
oups.com...
Now that I have fully tried this out, how do I stop it #N/A when there
is no number in the column?????

Here's another way...

=IF(COUNT(R1:R1000),LOOKUP(9.99999999999999E+307,R 1:R1000),"")

Hope this helps!

In article m,
"pano" wrote:

Hi all, the problem ...in a column I may have a user enter a receipt
number which is incremental so day 1 seperate sheet may have no
receipts and day two (seperate sheet) may have 3 receipts. I want to be
able to pull out the last receipt number from the column for auditing.

Day 1
column a column b column c

day 1 nothing
so use the last receipt number on other worksheet
Day 2
5555



5556

receipt 5556 will be placed as last receipt number day 2 on other
worksheet.

Hope you can help

Thanks Max
=IF(ISNA(LOOKUP(2,1/(1-ISBLANK(R1:R1000)),R1:R1000)),"",LOOKUP(2,1/(1-ISBLA*NK(R1:R1000)),R1:R1000))


works a treat....

Domenic wrote:
Here's another way...

=IF(COUNT(R1:R1000),LOOKUP(9.99999999999999E+307,R 1:R1000),"")

Hope this helps!

In article m,
"pano" wrote:

Hi all, the problem ...in a column I may have a user enter a receipt
number which is incremental so day 1 seperate sheet may have no
receipts and day two (seperate sheet) may have 3 receipts. I want to be
able to pull out the last receipt number from the column for auditing.

Day 1
column a column b column c

day 1 nothing
so use the last receipt number on other worksheet
Day 2
5555



5556

receipt 5556 will be placed as last receipt number day 2 on other
worksheet.

Hope you can help

Welcome, pano.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
pano wrote:
Thanks Max
=IF(ISNA(LOOKUP(2,1/(1-ISBLANK(R1:R1000)),R1:R1000)),"",LOOKUP(2,1/(1-ISBLA*NK(R1:R1000)),R1:R1000))


works a treat....