Validation list (dropdown) - source from table in another workbook?

[Kamolga]

Hi,

I am used to set up dropdowns from tables. For example I set up a table called TblBU that I fill with all business units. I select then the column without the tiltle (Business Units) and name it BU_List (so it refers to =TblBU[Busuness Units]), which I use as dropdown source. The reason for the effort is to have a dynamic dropdown (if I add, modify or delete a business Unit, I don't have to update my dropdowns).

Now if this table is in a workbook called Entities.xlsm on sheet called Lists, it seems I can get a dropdown with a source

='[Entities.xlsm]Lists'!$C$2:$C$10

but is it possible to refer to the named range or the table?

Thanks in advance for any hint.

 

[Fluff]

Yes it will, because the named range is looking at the table. So if the table changes in size, the named range will follow suit.

Forgot to mention that both workbooks need to be open.

 

[oldbrewer]

				dropdown > > >	7				3
									4
									5
									6
									7
									8
									9
									10
					myrange is J1 to j20
					it was down to 9
					drop down worked
					I added the 10
					drop down updated correctly

<colgroup><col span="4"><col><col span="10"></colgroup><tbody>
</tbody>

 

[Kamolga]

On the workbook with the DV create a named range like
=ZFluff.xlsm!Accounts[County]
then use that named range in the DV

Indeed I finally got it. My mistake was trying to directly set it up in data validation source.

For table 1, column 1 in Book1, I create a name DropSource in book2 that refers to

[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]=Book1.xlsm!Table1[Column1][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]

and then I can use =Dropsource in data validation.​

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[My Aswer Is This]

Well that is something new I learned Today. Thanks for that.

Yes it will, because the named range is looking at the table. So if the table changes in size, the named range will follow suit.

Forgot to mention that both workbooks need to be open.

 

[Kamolga]

				dropdown > > >	7				3
									4
									5
									6
									7
									8
									9
									10
					myrange is J1 to j20
					it was down to 9
					drop down worked
					I added the 10
					drop down updated correctly

<tbody>
</tbody>

It used to be 'dirty' with empty rows so I took the table and names habit (it also gives the possibility to have dropdowns that depend on each other, if you select car in Transport dropdown, another dropdown will give you only the car items). The names I use for my dropdowns can also be used in look up formulae and macros, so I like to have clean dynamic ranges to minimise the range to look into.

 

[Kamolga]

Forgot to mention that both workbooks need to be open.

That made it useless, the idea was having small excel files as lists (worker list, business unit list, department list, tasks list, processes list, etc.) to be used in Excel, Visio and Project and dropdowns to secure data entry in a template folder I could reproduce from client to client.

By importing the table (data tab, get data / from file / from workbook) to book2, I can have a dropdown from values in book 1 even when it is closed. If both files are closed and I open, modify and close book1, book 2 dropdown has the updated values when I open it.

In the import phase, I tried to import as 'connection only' and I can read one value at a time using

=CUBErankedMEMBER("Thisworbookdatamodel";[table1].[column1].children;2)

for example but I did not succeeded in setting the connected table as a defined name for the dropdown without loading the table in a hidden sheet.

 

[Fluff]

If you've imported the table then you probably don't need to use a named range, you can just use the table itself