Data Validation List by Formula

Happy New Year to all!

I have created 6 seperately named lists of contacts for six different offices. In this case, the lists are named 'HQ', 'SE', 'SW', 'MID', 'NORTH' and 'SCOT'. I am trying to have the appropriate dropdown list in one specific cell (C23) which is populated according to the region selected in another cell (C5). For instance, I have tried the following formula as the Source in the Date Validation Settings tab (having first chosed 'List'):

=IF($C$5="HQ",=HQ,IF($C$5="South East",=SE,IF($C$5="South West",=SW,IF($C$5="Midlands",=MID,IF($C$5="North",=NORTH,IF($C$5="SCOT",=SCOT,""))))))

Perhaps this is not possible or the formula needs a tweak or there is another solution? Any suggestions appreciated.

Try removing the = operator before each Named Range reference.

If the Named Ranges are not Dynamic and are in fact hard wired references (Sheetx!A1:B10) you might find you can use:

Please Login or Register  to view this content.

Did you try the INDIRECT option (this would replace your existing formula in full)

If in doubt best to post a sample file which mimics the set up you have.

If you can't do that please specify how the Named Ranges have been created - ie the RefersTo: value for say HQ & SOUTH EAST

On a final note - are you using ; delimiters or , ?

P.S. I'd recommend you not use MID as a Named Range if you're using the English function names

edit: ignore the INDIRECT - given C5 contains the long version rather than Named Range name

OK - I've had my mid morning cup of tea now and the brain is functioning...

Remove the Null from your formula and try again (assuming , delimiter)

Please Login or Register  to view this content.

(I'd probably rename MID myself)

If the option selected in C5 is not one of the above there will be no DV options from which to select.

Per sample - the formula from the prior post should work if you revise:

a) references to C5 to B1

b) reference to MID to MIDLANDS

of course this assumes you want the A.N.OTHER entries to appear when DV is active.

Referring to the issues you outline in your above post re: latest sample file

Note the following:

a) G9 : you have a trailing space: "North " <> "North" -> hence no DV options when "North " is Selected -> modify G9

b) G10: mismatch between DV options and formula -> "Scotland" <> "Scot" -> hence no DV options when Selected -> modify formula "SCOT" to "Scotland")