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.

 

[oldbrewer]

in a workbook in sheet3 there is a range called bob - say
you can refer to bob from sheet1
so I don't understand the question - yet

 

[My Aswer Is This]

I think the user is trying to use a Excel Table for a Data Validation list.
This way as he adds more rows to the one column Table the values in the Data Validation list increase.

Now you can do this I do this also it's called a Dynamic Data Validation List

But you at least as far as I have found if your Table is Name Mom

You can not put in your DataValidation list

=Mom

But if you were using a Named Range you can use
=Mom


But you can put into your Data Validation list something like this:
=$C$2:$C$11

This is the Table Range but does not include Title

And as you add more rows to the Table the Data Validation list is now Dynamic and adds those new values.

So not sure why you cannot do this.

 

[Kamolga]

Bob is workbook A sheet 3 (let's say Table1, A1:A3), of course I have no issue using it in Workbook A but I want to use it in workbook B sheet 1.

I can define a Name 'Bob' in workbook B with a source [workbookA]sheet3'!A1:A3 but then it is not dynamic (if I add a line, I need to redefine Bob. For business Units it would not be much of a problem but for active staff member, that would drive me nuts)

I don't know if it is possible to link a dropdown to another workbook table...if not, the workaround I thought of would be to import the table on a hidden sheet through query but then I would give up my excel templates to set up access solution.

 

[Kamolga]

I think the user is trying to use a Excel Table for a Data Validation list.
This way as he adds more rows to the one column Table the values in the Data Validation list increase.

Now you can do this I do this also it's called a Dynamic Data Validation List

But you at least as far as I have found if your Table is Name Mom

You can not put in your DataValidation list

=Mom

But if you were using a Named Range you can use
=Mom


But you can put into your Data Validation list something like this:
=$C$2:$C$11

This is the Table Range but does not include Title

So not sure why you cannot do this.

Hi, Indeed what I do with success as long as I am in the same workbook. I would need to reach Mom in Book2. In book 2, I don't now how to add =workbook1'Mom' in my validation list or =workbook1.table1.column1

 

[My Aswer Is This]

But if the Table is in the same workbook and your Table is named Mom

you cannot enter =Mom into the Data Validation List at least I have not been able to do that.

I must Enter

=$C$2:$C$11

Which is the Table Range excluding the First row

Or if you have found a way show that to me it will help me


​

 

[Fluff]

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

 

[My Aswer Is This]

I would think in either case. If one Workbook wants to refer to another workbook

Both Workbooks would have to be open. But I may be wrong.

 

[My Aswer Is This]

But will this work when your using a Excel Table as a Data Validation Range. The reson for using a Table as the range is so the Data Validation range is Dynamic. A Named Range is not Dynamic as far as I know.

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

 

[oldbrewer]

a1=3 a2=4
define a1:a100 as myrange and use that in your dropdown ?
add a3=999 and your drop down updates