3-D reference on multiple spreadsheets

[jwintsch]

I have several spreadsheets that I want to reference defined names from one spreadsheet. The lists are for two drop down columns with column A just a list and column B has data validation using the indirect function dependent on what is selected from the drop down in column A. Everything works as wanted in the first spreadsheet "define name" refers to(=NAME1!$V$4:$V$20). However, when I use the 3-D reference and change the "refers to" (NAME1:NAME15!$V$4:$V$20) in the first spreadsheet neither drop down works on any spreadsheet? Help!

[JBeaucaire]

Yeah, you can't do that.

You can set names ranges locally on each sheet, or you'll need separate tables on each sheet, or you'll need a separate sheet with your "list" on it defined in a way that you can refer to it from any sheet, INDIRECT() won't work if that list is dynamic.

Click GO ADVANCED and use the paperclip icon to post up a sample of your workbook. We can look at it together.

[jwintsch]

hopefully attached

[JBeaucaire]

Those lists on each sheet, those are the same on all sheets?

[jwintsch]

not quite...the "butler" tab is the most up to date with changes. if I have to copy the lists and use on each SS I will. was hoping to just keep one set on one SS
thanks for the help

[JBeaucaire]

I've pretty much abandoned INDIRECT() because of the problems. It requires you to manually create and maintain the size of all the named ranges you need, you can't use spaces so your data looks weird.

Here's how I do it... a single sheet with a starting named range of Category, an AnchorCell which cell A1 on that sheet, and a named range of row1 on that sheet called Categories. With those in place, we can use OFFSET() in our Data Validation formula to find the item chosen in a prior cell by looking across the Categories row, then providing a list of all the options listed under that heading. The possibilities are endless, you can use spaces so things look normal, and there's no maintenance on the named ranges, they create themselves.

Like so...

[jwintsch]

thanks for the help...new to the site but, hopefully posted a kudo correctly to your reputation...thanks again!

[JBeaucaire]

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[jwintsch]

done...thanks again