Dynamic Drop Down lists

[Excellearnerva]

Hi,
I am trying to set up a spreadsheet with linked dynamic drop down lists, based on a table.
Using the OFFSET function, I have managed to get this to work if the table is on the SAME sheet at the drop down lists. But I want to be able to put the drop down lists onto a different sheet, so that I can lock and hide the table.
Any idea how ?

I don't want to use the 'name based on the previous column' idea, as the table is large and will change weekly.

Also, related issue, the drop downs show all values, even duplicates, which I would like to get rid of.
Any idea how ?

[ragulduy]

Hi - to use a data validation list on a different worksheet, you need to use a named range. So instead of putting your offset formula into the data validation box, create a named range and put the offset formula in for the named range reference, then your data validation formula is =named_range

I don't believe it is possible to remove duplicates from a data validation drop down directly, so you would need to create another table to give you a list of the unique entries and reference your data validation list to that table.

[Excellearnerva]

Many thanks for your help !
After a bit of fiddling I have managed to get the formulas to work from the Name Manager. BRILLIANT!