List as a value in IF statement

[iainjsimons]

Hi All,

This is my first post here after having used this site many times in the past to look into excel issue I have encountered. This appears to be a simple thing to do but for the life of me I cannot make it work.

=IF(D2="No",0,Vars!B4:B5)

I need to check the value of a cell (D2) this contains a list of Yes and No and if I select No I get the correct value of 0 but if I select Yes I get #VALUE! in cell, in the Vars sheet B4&5 have two different values which I need to choose between. Am I trying to achieve this to simply and need to expand on what I have already?

Thanks for taking the time to read this.

[davsth]

the reason is a cell can only contain 1 values, you are wanting it to display 2 which it can't

you could do something similar as the validation for the cells, but this would not change existing values and you would have to select

[Special-K]

You will def get a #VALUE error with that formula.

What are you trying to do?

If D2 is "No" return 0

What do you want to happen if D2 is not "No" ?

[iainjsimons]

As there are only choose able options in D2 I know if they don't choose No it must be Yes so the outcome is controlled.

[Glenn Kennedy]

Does this help?? Select value in D2 and then in E2....

[iainjsimons]

Hi Glen , many thanks. It is perfect but I cannot see how you achieved it

[Glenn Kennedy]

Black magic...
I created two named ranges, one called Yes, the other, No. CTRL-F3 to view them. "Yes" refers to B4 & B5 and "No" to a single cell containing a zero.

I then used Data Validation in D2:

Data/Data Validation/List/ and put Yes,No in the "Source" box.

And in E2:
=INDIRECT(D2)

That's it...

[iainjsimons]

Glenn you are a gent!

[Glenn Kennedy]

You're welcome and thanks for the rep.

[FDibbins]

Glenn, stay away from the dark (black) side, anger and hate only, will you find there

MTFBWY

[Glenn Kennedy]

I'm in an RV in France at the moment and have just noticed that the end box on the exhaust is about to drop off. I have 3hrs drive tomorrow and plenty of anger, hate and fear about the size of The Invoice That Is To Come...

[FDibbins]

ouch!! good luck my friend

[Glenn Kennedy]

Only one thing for it.... Hi-volume Willie Nelson while on the road.

[FDibbins]

Yup, told the wife if her car makes funny noises, turn up the radio