Quick Excel (OpenOffice Calc) code query

cormie

Hey folks,
I'm using the below formula:

=IF(ISNUMBER(FIND("F";J2680));(VLOOKUP(J2680;$Foreign.$A$1:$O$67423;15;0));(VLOOKUP(J2680;$Purchases.$A$1:$O$67423;15;0)))

but I need to add this to it somehow:

=IF(J2680="";"0";

but I can't figure out the order I need to put it in.

Basically the code is looking up two separate sheets for the entry I put into J2680, one sheet I'm listing items with F at the beginning, the other just numbers.

The code does return the correct entries when the cell is populated, but if the other cells for more entries beside it are left blank, they return an N/A and make the calculation turn out to be N/A too.



The image shows the code being used in the Green boxes, the previous code, which I had when I just needed to look up one sheet is this:

=IF(N2681="";"0";(VLOOKUP(N2681;$Purchases.$A$1:$O$67434;15;FALSE())))

and is in all the other red boxes and returns the correct 0 fill.

So I just need a way to add the same request to add the 0 fill to the new code

Sounds complicated and I've had no luck, but hopefully easy for any experts out there

cormie

bump

Dravokivich

I don't use Open Office, But in Excel there's "nested if statements," it might be what you are looking for.

My reading of your formula, might be something like this:

=IF(J2680="";"0";IF(ISNUMBER(FIND("F";J2680));(VLOOKUP(J2680;$Foreign.$A$1:$O$67423;15;0));(VLOOKUP(J2680;$Purchases.$A$1:$O$67423;15;0))))

cormie

Thanks a lot for the input, that's actually appeared to have done something and no errors finally, I'm not too up to speed with Calc (or excel) so I'm not sure what's happened here, but when I pasted the above code into the next red cell in the row beside €15.85, the red cell became white and also populated with €15.85 and changed the total on the left to €31.70.

So it appear to at least give the correct total, but I don't think it's returning the 0 to the cell, but maybe it's reading from the wrong cell?

cormie

Oh wait, I'm not sure how I copied and pasted it earlier, but I just tried again and I think it might be working perfect now!!

cormie

Ah I think I know what I did, I pasted the code you typed above into K2680 and it seemed to work fine for the code put into J, but then I pasted it into M2680 and it was reading from J instead of L, but that's because I pasted from your post, and didn't copy the code once I had pasted it into K. Now if I copy from K and paste it into M, it changes J to L automatically and I think it's working perfect now!

Thanks so much, that's really appreciated