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
​

 

[T. Valko]

​

=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​

How about replying with the text left aligned!

Tell us what the actual cell addresses are for a couple of those defined names.

For example, what does DUTY1P and DUTY1 refer to?

 

[kino4city]

DUTY1 = SheetName:"LOOKUP";Range:"C31"
DUTY1P = SheetName:"LOOKUP";Range:"AA31:AA33"

DUTY2 = SheetName:"LOOKUP";Range:"C31:C33"
DUTY2P = SheetName:"LOOKUP";Range:"AD31:AD33"

DUTY3 = SheetName:"LOOKUP";Range:"C34:C35"
DUTY3P = SheetName:"LOOKUP";Range:"AF31:AF33"

DUTY4 = SheetName:"LOOKUP";Range:"C35:C36"
DUTY4P = SheetName:"LOOKUP";Range:"AC31:AC40"

DUTY5 = SheetName:"LOOKUP";Range:"C34:C37"
DUTY5P = SheetName:"LOOKUP";Range:"AE31:AE34"

DUTY6 = SheetName:"LOOKUP";Range:"C35"
DUTY6A = SheetName:"LOOKUP";Range:"AG31"

DUTY7 = SheetName:"LOOKUP";Range:"C36"
DUTY7P = SheetName:"LOOKUP";Range:"AH31:AH33"

DUTY9 = SheetName:"LOOKUP";Range:"C34:C36"

Hope thats ok??

The only reason i slimmed it down and used defined ranges is because the data validation source box, on excel 2010, restricts the entry length massively.

Sorry about the alignment...

Thanks,
Chris

 

[Aladin Akyurek]

​

=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​

Try...

D12:

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

 

[kino4city]

Try...

D12:

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

No luck Aladin, I've tried it in the data validation list source and also the cell itself, but just errors.

if you think of anything else let me know,

ALSO TO ALL I THINK THIS WOULD BE EASIER TO DO IN VBA BUT I JUST DON'T UNDERSTAND VBA, SO JUST LET ME KNOW WHAT TO DO, CHEERS

thanks,
chris

 

[T. Valko]

DUTY1 = SheetName:"LOOKUP";Range:"C31"
DUTY1P = SheetName:"LOOKUP";Range:"AA31:AA33"

DUTY2 = SheetName:"LOOKUP";Range:"C31:C33"
DUTY2P = SheetName:"LOOKUP";Range:"AD31:AD33"

DUTY3 = SheetName:"LOOKUP";Range:"C34:C35"
DUTY3P = SheetName:"LOOKUP";Range:"AF31:AF33"

DUTY4 = SheetName:"LOOKUP";Range:"C35:C36"
DUTY4P = SheetName:"LOOKUP";Range:"AC31:AC40"

DUTY5 = SheetName:"LOOKUP";Range:"C34:C37"
DUTY5P = SheetName:"LOOKUP";Range:"AE31:AE34"

DUTY6 = SheetName:"LOOKUP";Range:"C35"
DUTY6A = SheetName:"LOOKUP";Range:"AG31"

DUTY7 = SheetName:"LOOKUP";Range:"C36"
DUTY7P = SheetName:"LOOKUP";Range:"AH31:AH33"

DUTY9 = SheetName:"LOOKUP";Range:"C34:C36"

Hope thats ok??

The only reason i slimmed it down and used defined ranges is because the data validation source box, on excel 2010, restricts the entry length massively.

Sorry about the alignment...

Thanks,
Chris

I'm getting ready to call it a day.

I'll check back tomorrow.

 

[kino4city]

I'm getting ready to call it a day.

I'll check back tomorrow.

No probs T. Valko, it is one that really does get annoying, i've been trying for 3 days lol, thanks for your help so far, much appreciated

Thanks,
Chris

 

[Aladin Akyurek]

No luck Aladin, I've tried it in the data validation list source and also the cell itself, but just errors.

if you think of anything else let me know,

ALSO TO ALL I THINK THIS WOULD BE EASIER TO DO IN VBA BUT I JUST DON'T UNDERSTAND VBA, SO JUST LET ME KNOW WHAT TO DO, CHEERS

thanks,
chris

What kind of errors?

 

[kino4city]

what kind of errors?

when entered in the cell =#ref!
When entered in data validation = just doesn't load options on the menu

 

[Aladin Akyurek]

when entered in the cell =#ref!
When entered in data validation = just doesn't load options on the menu

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.