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.

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

"Drahos" wrote in message
...
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:AB1 1,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.