If blank leave blank

[Dibbley247]

Hi

I have lots of lookup formulas in my workbook which looks up prices. But all the N/A really annoys me when some cells are not needing to lookup so I seen a formula briefly which looks along the lines of

Please Login or Register  to view this content.

But I want this incorporated with my lookup and wrote the following:

Please Login or Register  to view this content.

which will display £- all the time?????

What have I done wrong?????

Thanks.

[Fotis1991]

Maybe use IFERROR..

=IFERROR(VLOOKUP($B6,Costings!$B$5:$E$139,2,FALSE)),""")

[Dibbley247]

Comes up with "you've entered few to arguments"

[JSallen]

Please Login or Register  to view this content.

, perhaps?

[Fotis1991]

=IFERROR(VLOOKUP($B6,Costings!$B$5:$E$139,2,FALSE),""")

"Play" with parenthesis....

[jeffreybrown]

@Fotis1991,

The error comes from an extra quote...

=IFERROR(VLOOKUP($B6,Costings!$B$5:$E$139,2,FALSE),""")

[Dibbley247]

So far so good! I'll start putting this across the first set of cells and see what happens!

Thanks!!!!

[Fotis1991]

@Fotis1991,

The error comes from an extra quote...

=IFERROR(VLOOKUP($B6,Costings!$B$5:$E$139,2,FALSE),""")

This happens when i don't use my glasses.

[Dibbley247]

It will automatically insert an extra " then will display " in place, which looks better but would be great if it was actually blank?

Please Login or Register  to view this content.

[jeffreybrown]

Did you try...


=IFERROR(VLOOKUP($B6,Costings!$B$5:$E$139,2,FALSE),"")

...which basically says, if the VLOOKUP produces an error, show a blank ("")

[Dibbley247]

Yes that works, This gives the result I'm looking for! I did this earlier without realising but made a mistake else where so had to start again, then went with the 3" which led to 4 of them and so on blah blah, lol. Thanks guys!

[jeffreybrown]

In the end, glad you have your solution

Thanks for the feedback and we are happy to have helped.

[Dibbley247]

I have gone to save it as 97-2003 due to compatibility at work.
then it comes up with.

"one or more functions in this workbook are not available in earlier versions of excel. When recalculated in earlier versions, these functions will return a #NAME? error instead of location"

So does this mean where i would of had a list of #N/A I will now have lists of #NAME? when running on the previous version of excel, which basically means I'll be wasting my time putting this into pages of formulas???

[jeffreybrown]

What's happening, IFERROR applies only to 2007+, therefore, because of compatibility issues with versions below 2007 you receive the #NAME? error.

Instead you would need to use...

=IF(ISNA(VLOOKUP($B6,Costings!$B$5:$E$139,2,FALSE)),"",VLOOKUP($B6,Costings!$B$5:$E$139,2,FALSE))

[Dibbley247]

Ok I'll paste this in and check when I'm in work next!

How would I go about the following then??

Please Login or Register  to view this content.

AND

Please Login or Register  to view this content.

I would guess that I would have to write =IF(ISNA(K6:M6),"",(K6:M6)) ?????


Thanks! You've been a great help!

[jeffreybrown]

What do you want to happen?

=(J6*N6)-(G6*L6) >> If this is in a cell and all the linked cells are blank it returns 0

=SUM(K6:M6) >> Same Here

[jeffreybrown]

If you merely do not want to see the zero if zero...

Two options to consider before modifying the formulas...

Hide Zero’s

• Highlight range >> Ctrl + 1 >> Number tab >> Category: Custom >> Type: 0;;;@
• Office Button >> Excel Options >> Advanced >> Display Options for this worksheet: Uncheck Show as zero

[Dibbley247]

the one with the LOOKUP formula now works well (on this PC at least)

This one

Please Login or Register  to view this content.

Displays #VALUE now but when things are in-putted it works as normal. Its just when nothing is in the lookup cell I dont want all these ugly #VALUE texts on the sheet


and this one

Please Login or Register  to view this content.

Just displays £0.00 which don't look bad, but I like things looking neat


I need to shoot of myself now, so will have a look tomorrow sometime!


Thanks for your help

[jeffreybrown]

If you don't particularly like the idea of hiding a zero value with the custom format...

Change this

Please Login or Register  to view this content.

To this >> which will only sum K8:M8 if all three cells contain a number

Please Login or Register  to view this content.

For this

Please Login or Register  to view this content.

Not sure why you would get #VALUE when those four cells are empty. As I said, I get zero.

If I happen to put text in one of those cells I will get #VALUE, but not sure that would be the case.

Can you post a small sample showing the #VALUE?

[Dibbley247]

I'm on my phone at the moment. I'll try them, but will have to change the count to 1 on your first example due to there will only be a maximum of two cells which will return a value depending on what the lookup returns. Basically the lookup goes through and return in each of the 3 cells a UNIT price per "part" so two cells may return a zero or only one, then the second example times a total from elsewhere times by the unit costs.
So if there is nothing to lookup, the first 3 cells will be blank but that last cell will say VALUE.
If this plays up, I will attach an example sheet which will be much easier/clearer than my explanations

[Dibbley247]

Here is a sample, everything is unprotected and some names changed. And a couple of sheets have been deleted to bring the size down.

Basically look at sheet: 3DAYS and columns B,K,L,M,N,O. And this should make sense.

K,L,M have been modified, but if you need to see the original formulas, look on sheets 4 days, 3 nights, 4 nights in the same columns.

These formulas when modified, could also affect data in the OVERVIEW sheets?????

many thanks

sample.xls

[Pete_UK]

Put this formula in cell K6 of the 3_nights sheet:

Please Login or Register  to view this content.

Then you can copy it into L6 and M6, and then copy these down.

You have a complex custom format applied to cells N6:N19 which shows you the £ sign without any data - you might like to change the formatting to Currency with 2dp, though you will still see £0.00 with no data. In O6 you can use this formula:

Please Login or Register  to view this content.

and then copy this down.

Hope this helps.

Pete

[Dibbley247]

To be honest, I thought I had already changed all the formats to currency £. So I'll have to change these by the sounds of it! I must have changed one, but when pasting down, I must have copied from the wrong cell to begin with.
I'll try this out when I'm back on again!
Thanks

[Dibbley247]

All works well now! Didn't think it would lead to all this just to make the pages look tidier! Thanks guys for your help!

[jeffreybrown]

You're welcome and thanks for the feedback. We are glad to have helped and happy it worked out for you ...