Data Validation - IF statements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CC - 09 Sep 2007 01:24 GMT

I am having problems with 7 Nested IF functions in data validation.
What is the best way around formulas using more than 7 IF functions?
How do you write an IF function in VBA where there are more than seven
statements?
Thanks

Reply to this Message

T. Valko - 09 Sep 2007 04:28 GMT

Can you provide a more detailed explanation of what you're trying to do?

There are many ways to get around a bunch of nested if statements.

Signature

Biff
Microsoft Excel MVP

>I am having problems with 7 Nested IF functions in data validation.
> What is the best way around formulas using more than 7 IF functions?
> How do you write an IF function in VBA where there are more than seven
> statements?
> Thanks

Reply to this Message

CC - 09 Sep 2007 05:50 GMT

I am trying to do the following:

I have established various combinations of cells to create a frame type and
called given it a name.

e.g. =IF(R17="Brown",OR(AG17="Grey",AG17="Red")), "Brown Frame") I have
placed these formulas at the bottom of my spreadsheet, where it is not
visible.
In another cell I want that framename (which is the name of the list
created) to be displayed with a dropdown list of variations of type that go
with this framing.
e.g. IF(M217="Brown Frame",brownframe,IF(P217="Green Frame",greenframe))etc
(I have placed this under Validation-List. I come unstruck when I have too
many of these IF statements.
Thanks for your help.
CC

> Can you provide a more detailed explanation of what you're trying to do?
>
[quoted text clipped - 5 lines]
>> statements?
>> Thanks

Reply to this Message

T. Valko - 09 Sep 2007 06:29 GMT

Ok...

>IF(M217="Brown Frame",brownframe,IF(P217="Green Frame",greenframe))etc

It looks like row 217 contains your formula results. But, how do you control
which list to use? Will there only be 1 cell on row 217 that actually
contains a frame name?

This formula returns an error.

>=IF(R17="Brown",OR(AG17="Grey",AG17="Red")), "Brown Frame")

Maybe you meant:

=IF(R17="Brown",IF(OR(AG17="Grey",AG17="Red"),"Brown Frame"))

This will return either Brown Frame or FALSE. So, does that mean row 217
contains 1 frame name and a bunch of FALSEs ?

Signature

Biff
Microsoft Excel MVP

>I am trying to do the following:
>
[quoted text clipped - 22 lines]
>>> statements?
>>> Thanks

Reply to this Message

CC - 09 Sep 2007 08:28 GMT

M217 to Y217 each contain a different frame name

My formula on each of these cells reads:

IF(AND(R17"Material Type",OR(AG17="Hinge type",AG17="Hinge
direction"))"Frame Name",0) each of these formulas determine what list I
want the validation to choose and create a drop down list.

Only one cell on R17 contains the list from the validation.

Your help is so appreciated. I am learning new things all the time and at
moment doing some e-learning on Excel but have yet to learn the more
advanced skills.

CC

> Ok...
>
[quoted text clipped - 41 lines]
>>>> statements?
>>>> Thanks

Reply to this Message

T. Valko - 09 Sep 2007 17:54 GMT

Ok...

> M217 to Y217 each contain a different frame name

If each cell contains a name then what determines which of those names is
the source for your list?

Signature

Biff
Microsoft Excel MVP

> M217 to Y217 each contain a different frame name
>
[quoted text clipped - 58 lines]
>>>>> statements?
>>>>> Thanks

Reply to this Message

CC - 10 Sep 2007 05:42 GMT

This formula creates the frame name in M217.

IF(AND(R17"Material Type",OR(AG17="Hinge type",AG17="Hinge
direction"))"Frame Name",0)

My validation reads

IF(M217="Frame Name",framewhatever) and this creates the list. This works
up to 6 IF statements and then creates an error. I want to be able to
expand this.

Thanks
C

> Ok...
>
[quoted text clipped - 66 lines]
>>>>>> seven statements?
>>>>>> Thanks

Reply to this Message

T. Valko - 10 Sep 2007 05:58 GMT

We're not understanding each other!

I understand you have formulas in row 217. A drop down list resides in a
single cell and this list can have only 1 source. If the formulas on row 217
return these values:

blue, red, green, brown

What determines whether you want to use blue, red, green, or brown as the
source of the list? You can only use 1 of them.

Is there anyway that I can see your file? That would give me a better
"picture" of what you're trying to do.

Signature

Biff
Microsoft Excel MVP

> This formula creates the frame name in M217.
>
[quoted text clipped - 80 lines]
>>>>>>> seven statements?
>>>>>>> Thanks

Reply to this Message