• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA: Populate multiple drop-down list (in excel) based on external excel file

inddon

Member
Hello There,

I am looking towards making a drop-down list on a cell which is based on a external master file

The 2 files attached are:
1. Master file (File Master Data.xlsx)
2. Invoice data entry (File Invoice.xlsm)

File 1 consists of worksheet (Master) where master data is defined and (Setup) where the columns needed in the drop-down list is defined.

File 2 consists of a Table, and the drop-down list defined is on column 'Amount From File Master Data'. On clicking the drop-down list, it should refer File 1 worksheet (Setup) to collect column names, then go to worksheet (Master) get data from the columns and display the multiple columns drop-down list and return sum of amounts value on the cell.

I have explained the same in the 2 excel files

Never done this before and look forward for your help in achieving this.

Thanks & regards
Don





Based on File 1's worksheet (Setup), it should pick up the columnsThis drop down list should
 

Attachments

  • File Master Data.xlsx
    9.2 KB · Views: 10
  • File Invoice.xlsm
    13.6 KB · Views: 9
Is there a strong reason you need this to be in 2 separate files? It would be much easier with one. Additionally, there is a limit as to what functions we can use on a closed workbook, so we may need to setup some 'simple' linking formulas from Invoice file to Date file in order to pull over all information, and then perform our calculations (defeating purpose of having information segregated).
 
Hello Luke,

Periodically the master data file is received and it contains approx 2500 rows and columns up to Q, so that is why this separate file

I had in mind to link and extract the related columns from the master into the Invoice file (in a new worksheet),and then perform...Hope this is the same as to what you are proposing, then it is perfect.

This is quite a new topic and I will be very happy to learn something new.

Look forward to hearing from you.

Thanks & regards
Don
 
My next proposal then would be to use a PivotTable. You can have a PivotTable pull data from an external workbook, even one that's closed. A PT would let you easily choose which columns/information you want, and still give you lots of flexibility to analyze your data.
 
If it is easy to use for a non excel user it is okay, else just linking the data in the Invoice file will also do. Which ever way that suits best to build this multi column list is fine

Regards
Don
 
They're fairly intuitive, makes use of filters/dropdowns. Example I made with your data
View attachment 10755

To setup the linking properly between two files is hard to do via forum, as we obviously have different local folder structures.

How to setup:
http://www.contextures.com/CreatePivotTable.html


Hello Luke,

Thank you for the Pivot sample and also the web link. It looks to be a bit difficult for a non excel user to use the pivot.


Lets assume that we already have the data (selected columns from master file) copied in a new worksheet in the Invoice file. We don't have any linking for this case but directly work out from the Invoice file and build up the list.

Would that be a simpler way to go ahead?


Regards
Don
 
Perhaps...can you build on your Invoice example, showing what the data would look like in final layout?
 
Perhaps...can you build on your Invoice example, showing what the data would look like in final layout?


Hello Luke,

The selected master data can be copied in the Invoice file, so it is a relief. I have uploaded the Invoice file. It has an extra worksheet 'FormMaster' where the selected columns are inserted.

In the worksheet 'Invoice Entry' I have displayed how the drop-down list should work on cell 'Amount From Woksheet FormMaster'

Thanks for taking up my case and helping me out.

Regards
Don
 

Attachments

  • File Invoice.xlsm
    16.8 KB · Views: 1
Here's worksheet with formula. Is that what you needed, or was there more?
 

Attachments

  • File Invoice Formula.xlsm
    17.4 KB · Views: 3
Here's worksheet with formula. Is that what you needed, or was there more?


Hello Luke,

Thank you for your help.

This solution of yours is an extra learning bonus for me in this case.

What I was looking for was a multiple columns Drop Down list on cell 'Amount From Worksheet FormMaster'

I have attached a printscreen of the actual result for your reference. For the result output of this list is null.

My apology for the misunderstanding.

Looking forward to hear from you.

Regards
Don
 

Attachments

  • Multiple Column Drop Down List.jpg
    Multiple Column Drop Down List.jpg
    122.2 KB · Views: 7
If I understood correctly, you're wanting the image of the other sheet to be displayed when you hit the dropdown on first sheet? This isn't really possible...not w/o getting complicated.
 
If I understood correctly, you're wanting the image of the other sheet to be displayed when you hit the dropdown on first sheet? This isn't really possible...not w/o getting complicated.


Hello Luke,

I came across combobox option, and it serves the purpose for referencing the master.
Please find attached the workbook.

I would like that it displays only those rows in the Combobox where the customer name, VAT Number matches to the current row.


Could you please advise on that.

Regards
Don
 

Attachments

  • File Invoice.xlsm
    22.5 KB · Views: 4
Last edited:
Hi Don,

I tried to use some code form here:
http://www.contextures.com/xlDataVal11.html

It's still pretty rough, but it does a drop down of sorts. New Formulas worksheets does the work of gathering desired records in one place to feed to the combo box.


Hello Luke,

My first reaction: Wow!!, this looks pretty wonderful.

Thanks a lot for taking the time. I have learned another new thing from you :).

I will study the case you have done.



Please would you be able to help me out with the combo-box on the excel sheet in my previous post(I have attached over there file 'File Invoice Dynamic' post number #13 from Yesterday at 9:54 PM

Thanks again & regards
Don
 
Glad you like it. Actually, I think I did use the combo box from your previous post. I just added the code so that the box moves and becomes hidden/unhidden. Overall structure is the same...with multiple columns and option select.
 
Hello Luke,

You are right.

In regards to my previous post, I moved the combobox outside the table. The filter didn't work.

Could you guide me how to realize the functionality:
1. Moving the combo box outside the table
2. Not returning any value to the cell (i.e. only for reference purpose)

Thanks & regards
Don
 
In the sheet module (right-click on sheet, view code), you'll see this snippet near end. (sans comments)
Code:
With Me.ComboBox1
    .Left = Target.Left 'Delete this line...
    .Top = Target.Top 'and this line to stop box from moving around
    .Visible = True
    .ListFillRange = "ComboRange"
    .LinkedCell = Target.Address 'Delete this line to stop the linking
    .Activate 'This line shifts focus to combo box
    .DropDown 'This line opens the dropdown
End With

If you don't want it moved or linked delete the lines I commented as appropriate.
 
In the sheet module (right-click on sheet, view code), you'll see this snippet near end. (sans comments)
Code:
With Me.ComboBox1
    .Left = Target.Left 'Delete this line...
    .Top = Target.Top 'and this line to stop box from moving around
    .Visible = True
    .ListFillRange = "ComboRange"
    .LinkedCell = Target.Address 'Delete this line to stop the linking
    .Activate 'This line shifts focus to combo box
    .DropDown 'This line opens the dropdown
End With

If you don't want it moved or linked delete the lines I commented as appropriate.

Hello Luke,

Thank you for your reply.

I applied your code. It seems I am missing out something and cannot figure it out.


I have brought the combobox outside the table. When I enter Customer and VAT, the list is empty.

Could you please review the attached file and advise.

Thanks & regards
Don
 

Attachments

  • File Invoice Dynamic.xlsm
    30.3 KB · Views: 7
Box works correctly on my end...note that you need to select a cell in the "Amount From Woksheet FormMaster" column of the worksheet. That is what the Intersect(Taget,...) bit of the code is checking for.

Overall, I must admit, I'm confused as to what the purpose of this drop down is supposed to be. If you don't mind, you could explain?
 
Box works correctly on my end...note that you need to select a cell in the "Amount From Woksheet FormMaster" column of the worksheet. That is what the Intersect(Taget,...) bit of the code is checking for.

Overall, I must admit, I'm confused as to what the purpose of this drop down is supposed to be. If you don't mind, you could explain?


Hello Luke,

The purpose of the combobox is as follows:

There are some transportation charges mentioned in the Invoice and these needs to be cross-checked with the transportation company (transportation charges) excel file.

When the User enters the transportation cost in, he needs to check this against another excel file (master data of transportation cost per company), if they have correctly charged. It is used only for reference purpose. Hope you are getting the picture.

Yes it works, when I am on the columns 'Amount From Woksheet FormMaster'.

I was looking to have the combobox physically placed outside the table and get it work.

Would that be possible?

Thanks & regards
Don
 
Hi,

Filling the combobox list with values from a closed workbook can be done with an ADODB connection using SQL Select statement

Which are the columns with which you need this data

i.e. In the 'File Invoice' file you have posted earlier what is the output you expect and how did you arrive that output
 
In order for the combo box to know which row you are concerned with, we need some sort of trigger. What trigger would you like? When table is edited, when a specific column is selected/changed, etc.?
 
In order for the combo box to know which row you are concerned with, we need some sort of trigger. What trigger would you like? When table is edited, when a specific column is selected/changed, etc.?


Hello Luke,

It would be nice whenever the user clicks on the combobox, the population of the combobox is filled in for the row in the table (i.e. Customer Number and VAT)

This can be while entering the row or even when the user is just on a row and he clicks the combobox, then for it takes the value of customer & VAT and fills up the combobox and returns no value. It is just for viewing.

Would that be possible?

Thanks & regards
Don
 
I think I understand. Modifed my previous file to show combo box when user has selected a cell within table. Dropdown does not take focus automatically. Drop down has information related to active row.
 

Attachments

  • File Invoice Dynamic Sept22.xlsm
    30.3 KB · Views: 8
Back
Top