Dropdown list from external file

scotball

Active Member
Joined
Oct 23, 2008
Messages
375
Hey,

I have created a scorecard which has 4 key drop down lists:

- Site, Coach, Team and Agent

Coach and Team populate the dropdowns dependant on which site is selected and Agent is based on which team is selected.

I have created an external file called dropdowndata.xlsx as the main source. This file has two sheets. Sheet 1 (dropdowndata) is the main Site, Coach and Team data and then Sheet 2 (GLAteam) contains the linking Team and Agent data.

The drop down lists are based on a number of named ranges and lookups.

So the issue I am having is that the drop down lists only work if I have the dropdowndata.xlsx file open on my laptop.

Is there anyway to get this to work without having to actually open the file?

Thanks,
Lee
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hey,

Still not able to get my dropdowns working without having the external file physically open.

Any way round this?

LP
 
Upvote 0
Is there a reason for using an external file?

Coudn't you just put the data in (hidden?) worksheets in the main workbook?
 
Upvote 0
Yeah, the main reason is that i have approx 20 different people using a scorecard and everytime i make a change I have to send them a new version of the scorecard. They will lose data they have stored which eventually annoys too many people.

I did have the dropdown data as a hidden protected sheet.

Therefore i decided to make the dropdowndata file an external file which can be edited without impacting the main scorecard.

Any help appreciated...

LP
 
Upvote 0
There may be 20+ people using their scorecards which would mean the source file being opened by 20+ people.

Will this work?

LP
 
Upvote 0
hey andrew,

i have looked at the link you supplied but to be honest i was very confused by most of the coding. A bit beyond me at this stage.

Could you simplify it for me?

LP
 
Upvote 0
The code is only opening the source workbook. Finding it's name does require some parsing, so I don't think it can be shortened.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top