Data Validation Source Box

I am looking for a help how to create a drop-down list using some functions
in a source box. I am using IF function but it seems that a number of "IF"
used is limited to 6. I have tried to use instead of too much IFs (I need 11)
a table and Lookup Function but when using it I receive a message "The list
source must be a delimited list, or a reference to single row or column." If
I put the whole formula (=VLOOKUP(CONCATENATE(H12,"_",H13),AA2:AB11,2)) to
the source box the Excel takes it as a text and not as a range name. I do not
know how to make the Excel to take this formula as a range name. The reason
for all of this is that I need to make a choise from 10 different list on a
base of entries in two fields. These to fields are arguments which list has
to be chosen.
Thanks a lot.

I'm thinking you may need to use the indirect function, but don't have time
to put in more details.

"Drahos" <[email protected]> wrote in message
news:[email protected]...
>I am looking for a help how to create a drop-down list using some functions
> in a source box. I am using IF function but it seems that a number of "IF"
> used is limited to 6. I have tried to use instead of too much IFs (I need
> 11)
> a table and Lookup Function but when using it I receive a message "The
> list
> source must be a delimited list, or a reference to single row or column."
> If
> I put the whole formula (=VLOOKUP(CONCATENATE(H12,"_",H13),AA2:AB11,2)) to
> the source box the Excel takes it as a text and not as a range name. I do
> not
> know how to make the Excel to take this formula as a range name. The
> reason
> for all of this is that I need to make a choise from 10 different list on
> a
> base of entries in two fields. These to fields are arguments which list
> has
> to be chosen.
> Thanks a lot.

Hi

Remove the outer set of brackets, they are not required.
=VLOOKUP(CONCATENATE(H12,"_",H13),AA2:AB11,2)

Regards

Roger Govier


Drahos wrote:
> I am looking for a help how to create a drop-down list using some functions
> in a source box. I am using IF function but it seems that a number of "IF"
> used is limited to 6. I have tried to use instead of too much IFs (I need 11)
> a table and Lookup Function but when using it I receive a message "The list
> source must be a delimited list, or a reference to single row or column." If
> I put the whole formula (=VLOOKUP(CONCATENATE(H12,"_",H13),AA2:AB11,2)) to
> the source box the Excel takes it as a text and not as a range name. I do not
> know how to make the Excel to take this formula as a range name. The reason
> for all of this is that I need to make a choise from 10 different list on a
> base of entries in two fields. These to fields are arguments which list has
> to be chosen.
> Thanks a lot.

Roger and Barb,

both of you are right. This formula works:
=INDIRECT(VLOOKUP(CONCATENATE(H12,"_",H13),AA2:AB11,2))

Thanks a lot for help.
Drahos.


"Drahos" wrote:

> I am looking for a help how to create a drop-down list using some functions
> in a source box. I am using IF function but it seems that a number of "IF"
> used is limited to 6. I have tried to use instead of too much IFs (I need 11)
> a table and Lookup Function but when using it I receive a message "The list
> source must be a delimited list, or a reference to single row or column." If
> I put the whole formula (=VLOOKUP(CONCATENATE(H12,"_",H13),AA2:AB11,2)) to
> the source box the Excel takes it as a text and not as a range name. I do not
> know how to make the Excel to take this formula as a range name. The reason
> for all of this is that I need to make a choise from 10 different list on a
> base of entries in two fields. These to fields are arguments which list has
> to be chosen.
> Thanks a lot.