Imported data, cell looks blank but is not??

[cvansickle]

Hello all, and thanks in advance...

I have a small excel sheet that I am importing from Microsoft access..

There are 10 feild that show the last amount someone paid. What I want to do is find which cell that is.. (If there were only 2 payments the 2 fields would have data and the other 8 blank)

My formula is:

=IF(AN2>0,10,IF(AM2>0,9,IF(AL2>0,8,IF(AK2>0,7,IF(AJ2>0,6,IF(AI2>0,5,IF(AH2>0,4,IF(AG2>0,3,IF(AF2>0,2,IF(AE2>0,1,0))))))))))

The problem I am having is, that although the cell looks empty, there must me something in there that is showing a vaule. I even tried a ISBLANK() but it comes back false.

Any ideas on how I skip over that cell unless there is a true vaule?

Thanks again..

 

[Gerald Higgins]

Something like this may simplify your basic problem, although it doesn't solve the problem of blanks.

=MATCH(VLOOKUP("zzz",A1:A10,1,TRUE),A1:A10,0)

To help solve the problem of blanks, do you know what character is appearing in the cells that look blank ?
Is it a space ?
Try

=code(a1)

if a1 looks blank.
This will tell you the code for the (first) character in the cell.

 

[cvansickle]

Would that forula work if each cell had a different vaule? The person may pay $560.34 one time then the next time pay $897.45. I goal is to find last payment made, with out concern of amount?

Make Sense??

When I place the =code(), I get a #VALUE! ???

 

[Gerald Higgins]

Sorry, for some reason I thought you were looking at text strings - my mistake.

Try this, which I think will deal with the blanks as you want...

=MATCH(VLOOKUP(999999999999999,A1:A10,1,TRUE),A1:A10,0)

This will work, as long as you don't have values above 999999999999999

 

[cvansickle]

Thank you so much for this help.. But I am still a little lost (nothing new on this end)

so this is my cells

A1 985.00 A2 586.20 A3 Blank A4 Blank A5 Blank A6 Blank A7 Blank A8 Blank A9 Blank A10 Blank.

In that line my goal is to get the last paid amount. So the 586.20.

But there are other cases when the last payment may be in A7 or somewhere different.

 

[cvansickle]

I did find a work around by adding 10 additional cells

Then using IFERROR(AE2,1) in each cell

Then doing the if/then statement on those new 10 cells.

But there should be an easier way right??

Correction: That did not work either

 

[Gerald Higgins]

In your example, my formula will return 2, to indicate that the last value was on row 2.

To return the value itself, try this

=INDEX(A1:A10,MATCH(VLOOKUP(999999999999999,A1:A10,1,TRUE),A1:A10,0))

 

[cvansickle]

It is returning an #N/A. Could it be the fact that Access is maybe sending the info as a Text instead of a currency??

Would that be an issue??

 

[Gerald Higgins]

It could be.
Is Access sending it as text ?
You might be able to change it in Access.

If you can't change the text formating, then this will work as long as the blank cells really are blank, and from what you said in post #3, it looks as if they are really blank.

=INDEX(A1:A10,MATCH(VLOOKUP("zzz",A1:A10,1,TRUE),A1:A10,0))

 

[cvansickle]

Is there a formula like this that will work with rows instead of columns?

Like A1 B1 C1 D1 etc....