Validation of cells using a list

[PollyHorror]

Hi all,

I need to create a bunch of 120 workbooks. In each of these I want certain cells to be restricted values. However, as these restricted values may change, I want them to be read from a master workbook where any change can be applied once, and be read by all the other workbooks.

So for example, in each of 120 workbooks I want say cells d4 to d500 to only allow values defined in the master workbook. I have created the values as a named list in the master workbook, but when I try to apply validation in one of the 120 other workbooks I get an error message to say I can't use a list from another worksheet or workbook.

Any way around this?

Thanks very much....Polly.

 

[VoG]

This should help http://www.contextures.com/xlDataVal05.html

 

[PollyHorror]

Thanks very much for that. I've followed the instructions, and now the cells give me a drop-down menu containing the values listed in the master sheet.

However...it will still not accept validation so that the cells ONLY accept those values - the message I'm getting says "You may not use references to other worksheets or workbooks for Data Validation Criteria".

Any ideas what I'm doing wrong?

Thanks...Polly.

 

[BillP]

It sounds as if you forgot to name the range in the master workbook (or maybe didn't close and open it afterwards or gave it an invalid name). That's the expected error message in that case.

What version of Excel are you using?