Help with data validation list and if formula

[kino4city]

=IF($C$12=DUTY1P,DUTY1,IF($C$12=DUTY2P,DUTY2,IF($C$12=DUTY3P,DUTY3,IF($C$12=DUTY4P,DUTY4,IF($C$12=DUTY5P,DUTY5,IF($C$12=DUTY6P,DUTY6,IF($C$12=DUTY7P,DUTY7,DUTY9)))))))

The above is what i have tried to do, the DUTY1P and DUTY1 etc. are my defined ranges.

I'm trying to get excel to enter a list in D12, based on what C12 value is.

If anyone can help, this would be much appreciated, as this is really annoying me now.


Thanks in advance,
Chris
​

 

[kino4city]

Sorted it guys, thanks for your input,

i made a table and f*** off the defined ranges, and used a if equation with index and match in...

Sssswwwwwwwweeeeeeeeetttttttt such a relief..

Anyone know how to close down a thread or does it just stay open??

 

[Aladin Akyurek]

Sorted it guys, thanks for your input,

i made a table and f*** off the defined ranges, and used a if equation with index and match in...

Sssswwwwwwwweeeeeeeeetttttttt such a relief..

Anyone know how to close down a thread or does it just stay open??

Great, but also an unsatisfactory end... I'd like to know why the proposed
scheme seems to fail for you...

 

[kino4city]

DUTY2P refers to LOOKUP!$AD$31:$AD$33 and

DUTY2 refers to LOOKUP!$C$31:$C$33, right?

If C12 houses the string DUTY2P, data-validating D12 with the List option and as Source the formula:

=INDIRECT(IF(RIGHT($C12)="P",LEFT($C12,LEN($C12)-1),$C12))

should yield the items of DUTY2 in D12.

As i stated above on the thread C12 houses only one of upto ten possibilities, this selection for example is stored as DUTY2P. DUTY2P is not just cell C12, C12 contains one of ...

Great, but also an unsatisfactory end... I'd like to know why the proposed
scheme seems to fail for you...

Hopefully this puts your mind at rest. and satisfys you, as you seem to be persistant that your solution was correct. when cleary you didn't read the thread properly

 

[Aladin Akyurek]

As i stated above on the thread C12 houses only one of upto ten possibilities, this selection for example is stored as DUTY2P. DUTY2P is not just cell C12, C12 contains one of ...

I know that... This formula

=INDIRECT(IF(RIGHT($C12)="P",LEFT($C12,LEN($C12)-1),$C12))

is meant to work with all of them, that is, with names having a P at the end and without.

Hopefully this puts your mind at rest. and satisfys you, as you seem to be persistant that your solution was correct. when cleary you didn't read the thread properly

What did I miss? I was just trying to understand why the suggestion would not work, that is, why I failed, so to say...

 

[T. Valko]

Glad you got it sorted!

Hopefully this puts your mind at rest. and satisfys you, as you seem to be persistant that your solution was correct. when cleary you didn't read the thread properly

Some people get mad if you don't do what they want you to do!