?
Solved

Drop Down Data on external Excel Sheet

Posted on 2011-10-18
26
Medium Priority
?
309 Views
Last Modified: 2012-05-12
I would like the two attached sheets to be connected so when you fill in the form it looks to the Data.xlsx sheets for the relevant information to pre fill in data based on drop down selections.


Data.xlsx
Form.xlsx
0
Comment
Question by:Glyn Merritt
  • 13
  • 8
  • 4
  • +1
26 Comments
 
LVL 34

Expert Comment

by:Rob Henson
ID: 36987369
Can the form not be in the same workbook?

Then you could have anumber of dynamic range names to feed the drop down boxes.

Thanks
Rob H
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36987751
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36987754
Sorry with some very minor adjustments the two above attached documents should do what you want.

-SA
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:Glyn Merritt
ID: 36987877
robhenson:
Unfortunately the data will be updated and the data will be based in a central location.

ScriptAddict:
I don’t seem to be able to get the form working. I've amended the path in the VBA code and I don’t get the sheet names to display.

Thank you
G.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36988047
Are macro's enabled on your PC?  I tested it before posting, and it was working perfectly on my PC.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36988067
Path is the directory of the data.lxsx file.  File name shouldn't be part of that path.

I'm assuming that the file name is data.xlsx.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36988116
It is currently set to refresh when you toggle to the form worksheet.

If you add this code it should also refresh when the dropdown button is clicked.

Private Sub ComboBox1_DropButtonClick()
Dim ws As Worksheet
Dim Path As String
Dim File As String
File = "Data.xlsx"
Path = "J:\John\Experts Exchange"
'   Make sure the file exists
    If Right(Path, 1) <> "\" Then Path = Path & "\"
    If Dir(Path & File) = "" Then
        MsgBox "File Not Found"
        Exit Sub
    Else
        If Len(Application.Workbooks(File).Name) > 0 Then
        Else
        Workbooks.Open Filename:=Path & File
        End If
    End If
 
    ComboBox1.Clear
    
    For Each ws In Workbooks(File).Worksheets
        ComboBox1.AddItem ws.Name
    Next ws

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Glyn Merritt
ID: 36988185
thanks for your prompt help.

When I change the sheet I error on the line below.

If Len(Application.Workbooks(File).Name) > 0 Then

Once you have selected the Supplier from the drop down does it enable you to select the items in that sheet and auto fill in the details?

Cheers
G
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36988370
There are two places in the code where it assigns the file that it is looking for

well three with the new code block.  

you'll see File="Data.xlsx"    If the actual name of the file is not Data.xlsx you'll need to change those to the correct file name.  If it changes we might want to make it so it references a cell and can be easily changed.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36988443
If you open the datafile, I think you'll find it works perfectly, that code was to detect if if the file was open or not and must need to be tweeked.  I'll have something shortly.
0
 
LVL 1

Author Comment

by:Glyn Merritt
ID: 36988553
Ok cool... I now have it working. What I was hoping rather than it apply the contents of the supplier that cells B10 and below would be drop down boxes and you could select the product and then the Product Ref and Unit price would be auto filled.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36989453
Ok,

I don't think I ever do anything to the data sheet, but here is the updated form sheet.  Let me know if you hit any additional snags! Form1.xlsm
0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36989844
Why not put a table datalink from Data.xlsx into Form.xlsx?
You can give the linked table a Name and use this for your dropdownlist.

Regards,
Davy
0
 
LVL 1

Author Comment

by:Glyn Merritt
ID: 36995325
ScriptAddict
That seems to be working ok but I would like to have a little play if that is ok?

Davy2270:
This option sounds interesting as my VBA ability is not great. How would you propose I achieve this with the external linked data.
0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36995808
You need to go through 3 simple steps, but before we start it is important to have the Data.xlsx file in a fixed place on your PC or networkdrive.

Step1 - set up the connection:
Click Data tab on the ribbon - Connections - Browse for more...
Then select the Data.xlsx file
You will be prompted for the different sheets and Named ranges in the Data.xlsx workbook.
Choose the appropriate table or named range which you want to be linked.
Close the Workbook Connections dialog screen.

Step 2 - display the data in your workbook:
Click Existing Connections in the ribbon.
Select the connection you have just added. Click open.
In the import data dialog choose for Table and New Worksheet.

Now the data is linked.
Step 3 - Name the data for your dropdownlist
Select the Column which you need for the dropdownlist and give it a Name, by clicking Define Name in the Formula tab of the ribbon. You can then use this name to link the values to the dropdownlist.

You can set the Refresh behaviour of your linked table by selecting a cell in it and then click Properties in the ribbon.

The VBA syntax for refreshing the data is:
Application.RefreshAll

Regards,
Davy

0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36996054
Davy,

Does this approach work when he is using multiple worksheets?  If I understand correctly the number of worksheets could change as well.

Will he need a new sheet in the form document for each page in the other document?

-SA
0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36996113
You do not necessarily need a new sheet for each linked piece of data. At step 2 - display the data in your workbook, you can choose for New Worksheet or Existing Worksheet. When you choose for Existing Worksheet, you also need to assign a startcell address. Assign a celladdress which is outside the former linked table.

Regards,
Davy
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36996258
I think the challenge here is that the supplier name, one of the items he want's to populate on is based on the worksheet(tab) names in the data file.  With each supplier having their own worksheet in the data file.  My guess is that the number of sheets can vary as each supplier is added or removed.
0
 
LVL 1

Author Comment

by:Glyn Merritt
ID: 36996314
ScriptAddict:
That is correct the suppliers will come and go. Ideally I would not have to make changes to the form in order to facilitate the changes.

Cheers
G
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36996340
The Form1 that I sent will automatically adjust for however many suppliers are in the workbook, but I think it will only list a set number of items.  To change the maxinum number of items a supplier can have you just need to edit the range that it pulls.  

If you find it isn't doing enough items just let me know it's easy to adjust, a mater of moments.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36996344
I think the default is 15 items.
0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36996348
Ok, that was not clear to me. Thanks for pointing that out.
In this case I suggest you put all suppliers in one table on one sheet. This will make life a lot easier.
Whenever you need to remove a supplier, you sort the table on suppliername and delete the rows.

Or do you have a good reason to keep the suppliers on separate sheets?

As a last piece of advice, I would recommend you to use MS Access for this kind of data administration, that's what it is built for. MS Excel is a 'calculator', a very enhanced one, but still not appropriate for data administration.

Regards,
Davy

0
 
LVL 1

Author Comment

by:Glyn Merritt
ID: 37005319
Thank you both.

On the basis of the recent few posts I'll crack on with the VBA solution. If it is ok I'm out of the office until Tuesday when I'll be able to look into it, I have a few questions though:

Is it possible for the drop down to be located where the Product description is and only display the Product description  in the drop down as we have longer product descriptions with some suppliers?

Is it possible for the drop downs to default to blank?

Let me know if I'm asking for too much as effectively the question is answered I’m just requesting a few tweaks.

Many thanks,
Glyn
0
 
LVL 1

Author Comment

by:Glyn Merritt
ID: 37005353
Just to confirm I have a supplier with 140 products but I don’t expect any to go over 150.

If we could fit about 30 items on the page so it can print on an A4 Sheet that would be great. Obviously I can do the shuffling around with the columns and row widths.
0
 
LVL 11

Accepted Solution

by:
ScriptAddict earned 2000 total points
ID: 37008812
Combo Box is now in the product description location, and only displays the product description.
I've added a blank option to both the supplier and the item selection lists.
I've added 30 Combo Boxes.
I've set the default number of supplier items allowed at 999.

I done some additonal polishing that should make this usable if you just change the path on the combobox 1 change and dropdown vba code.

Best of luck!

-SA

Form2.xlsm
0
 
LVL 1

Author Closing Comment

by:Glyn Merritt
ID: 37017404
Fantastic, everything I needed and more.

Thank you.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question