Data Validation - dependant lists - With a number value

Hi folks,

I am familiar with creating named ranges, drop down lists and dependant drop down lists, but what differs in this example is that I want a choice of 4 different lists depending on a specific number being entered rather than a word.

It isn't working because I am unable to name a range of cells with a number - it has to be a word, therefore unless use some sort of lookup then I can't get this working.

I guess the question is therefore: can you cause the dependant list to choose the correct data if entering a value that isn't the same as the named range?

Any advice greatly appreciated.

Originally Posted by dilipandey

Hi philwilleams80,

Please include a sample workbook to support your query. thanks.


Regards,
DILIPandey
<click on below * if this helps>

Hi DILIPandey

My work server is not letting me access the site at present and I can't attach the file from my phone. I can however email the document to you if you let me have your address.

Best wishes
Phil

Just seen you've provided it!! Apologies. Will email now

Phil

Ok it will have to be when I get home from work tonight - it's probably that out IT dept have blocked forum access.
Will update thread later.

Thanks anyway

Pl see the attached file with Example you can select number and choose list using VLOOKUP.

Thanks for this. I have actually used a lookup (well an IF formula) as a work around, but this requires a formula in an adjacent cell.
Because the worksheet will nearly always have to have rows inserted, this creates the problem of the formula not copying down into the inserted rows.

I could protect the sheet against row insertion and use a button with VBA behind which inserted them and copied the formula down, or but ideally I would want a neater solution.

Thanks

pl upload the file with dummy data.

Apologies for the delay - the file is now attached.

I have the 4 entries in the correct cells that require the individual lists. I also have the workaround with the IF formula in place, but obviously this isn't ideal for when rows need to be inserted.

I appreciate you taking the time to have a look.

Thanks

Pl see the attached file with validation in O Column.

Originally Posted by kvsrinivasamurthy

Pl see the attached file with validation in O Column.

kvsrinivasamurthy,
Many thanks for taking the time to review and resolve this for me.

As you can see, I have copied the indirect formula within the data validation source that you inserted in column O into column k where it is required, and the drop down list is now correct for each selection, and most importantly, when additional rows are inserted, the validation copies down into the new rows.

I couldn't be more grateful.

Thanks,
Phil