referencing a separate workbook

[thewiseguy]

hi all

before i dig in and research this, i thought i would ask here as you all have been extremely helpful in the past.

i have a proposal generating workbook. i enter in a bunch of “stuff” and my customer proposals are generated.

the proposals all require specific products be selected. these products are currently part of a table. the products are selected via validation lists and we use xlookuos throughout the workbook.

the problem i am having is that new products are always being added and so i add the product to the workbook i am working on and tgen for the next proposal, i open up the original workbook. i never have a true expanding database of products.

is there a way to create a separate workbook with just the products and have my proposal worksheet pull data from that workbook? that would allow me to update 1 file and would allow the list of products to expand.

any help or just general direction on what to research/learn would be greatly appreciated!!

 

[thewiseguy]

bumping this up, as this has again become a priority. thanks in advance.

 

[RoryA]

You could use one master workbook with the product list in it, then use Power Query to return that data as a table in any other workbook, which you can then use as the data validation list source. You can set the query to refresh automatically whenever the workbook opens if you wish.

 

[thewiseguy]

is this the "get data--> from file" option? If so, I've tried this but the formulas and data validations in my active workbook don't work b/c the table they were originally pulling from is now removed (and now in a separate workbook).

 

[RoryA]

Yes, it's that. You return the data as a table in your current workbook, and the DV and formulas refer to that table (you'll need to amend them but it's a one-off operation).

 

[thewiseguy]

ok - i think that was my original problem. You are saying to amend them once, but wouldn't i have to amend the DV's each time I created a new proposal and hence "linked" the data?

 

[RoryA]

No, because they would always be linked to the table in the same workbook. That table refreshes from the data in your source workbook.

 

[thewiseguy]

got it. that makes sense. thank you

 

[thewiseguy]

i was able to get this to work, however now I would like to change the source file location and name if possible. is it possible to do this without having to start over?

 

[jolivanes]

Sub Copy_Sheet()
Dim nm As String
nm = ActiveSheet.Name
Application.ScreenUpdating = False
With Workbooks.Open("C:\Folder Name Here\Products Workbook Name.xlsm")    '<---- Change
    .Sheets("All Up_To_Date Products").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)    '<---- Change
    .Close False
End With
ActiveSheet.Name = "All_Products"    '<---- Change if required
Sheets(nm).Select
Application.ScreenUpdating = True


'<---- Do all your stuff here. Reference your added sheet in code


Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("All_Products").Delete    '<---- Change if required
Application.DisplayAlerts = False
End Sub