If Validation Drop Box Is Blank

[Mooseman60]

I have 2 validation drop down boxes on my worksheet. The first drop down box (c20)populates the second drop down box (20) which populates cells E20, F20, G20 and I20
If data in Cell 20 is cleared then other cells become blank instead of showing Ref#

[NBVC]

You can precede each formula in E20, F20, etc.. with conditional check.

e.g. =IF(OR(C20="",D2=""),"",your_formula)

[romperstomper]

And your question is?

[Mooseman60]

This is the formula currently in Cell F20
=IF(D20="","",VLOOKUP(D20,INDIRECT(C20&"Lookup"),2,FALSE))

I have attached my sample workbook

If you select category in C20 and Product in D20 then clear category in C20 and you will see that the remaining cells are looking for data

I want these cells to clear as well when i clear C20

[romperstomper]

See NBVC's response.

[TMS]

Perhaps:

=IF(OR(C20="",D20=""),"",VLOOKUP(D20,INDIRECT(C20&"Lookup"),2,FALSE))

Regards

[Mooseman60]

Where do i actually insert your formula

[Mooseman60]

That works great now i just need to fix formula in material total and labour total which are both showing #Value!

=IF(D20="","",E20*G20)

[NBVC]

You replace the D20="" in your formulas with OR(C20="",D20="")

[Mooseman60]

Can you supply the full formula line with your added formula in it

[NBVC]

See attached.

[Mooseman60]

Thanks for that it works fine my only problem now is when I clear C20 all the cells clear except D20 still show the original selection. is it possible to get that cell to clear as well

[NBVC]

Not without VBA

[Mooseman60]

Ok thanks for yor help. How do you guys remember all this, you are unbelievable. I have only been teaching myself excel for a couple of months looks like it might take me another 10 years to get my head around all these formulas and codes

Once again thanks for your help

[NBVC]

Actually, see, we don't remember everything... at least not all at once...

You can do something with conditional formatting to "hide" column D if column C is blank.

So try this to see if it suits your needs.

Select D20:D25 and go to Format|Conditional formatting

Select Formula Is and enter formula =C20=""

Then click Format and choose White from Color dropdown in the Font tab.

Click Ok.

Now, if you delete/clear anything in C20:C25, the corresponding D20:D25 cell should "clear" also.

Note: the selected value actually remains, but is not visible....