Data Validation "The list source must be a delimited list, or a reference to single......"

If those are all named ranges that get returned, then you will need to put INDIRECT( ... ) around your existing expression. It might be easier to set up a little table somewhere with:

CSC ........ CSCall
CQC ....... CQC
Findus .... Findus
and so on

and then you can use VLOOKUP instead of all those IFs.

Hope this helps.

Pete

Thanks,

Yes, they are named ranges located on a different worksheet within the same workbook;

Rest = Validation!E7..E8
CSCcall = Validation!E11..E13
Findus = Validation!E16..E18

etc....

I have tried the suggestion above regarding <Index> but it errored, I may have the formula wrong....

Please Login or Register  to view this content.

How would I use VLOOKUP to display a list rather than return a cell value.?



Thanks.

You need to build up a table like this:

CSC .......... CSCcall
CQC .......... CQC
Findus ....... Findus
Motorola .... Motorola
Freescale .... Rest
TFL ............ Rest
UK AEA ...... Rest
SFL ............ Rest
RBFT ......... Rest
Telent ....... Telent
NYSE ..........NYSE
NSN .......... NSN

(That probably won't format very well, but hopefully you get the idea). Suppose this is in columns A and B of Sheet1, then you could do this:

=INDIRECT(VLOOKUP($G$7,Sheet1!$A:$B,2,0))

in your DV List box.

Hope this helps.

Pete

Thanks Pete,

Understood and done ... Now working. I did have to change one thing though - I couldn't reference a worksheet in a 'List' Data Validation so the table has had to go onto the worksheet itself.

Don't know if there IS a way of referencing different worksheets in Data Validation but that is something I will look into at a later date.



Thanks again,
marc.