Have one spreadsheet automatically update in others

William F.

New member
Joined
Apr 28, 2014
Messages
7
Reaction score
0
Points
0
Not entirely sure how to explain what I am trying to do but I'll give it a shot.

What I have is master list of workboots with various columns of data.

What I want to be able to do is have several different worksheets pull data from this. For instance I want one Spreadsheet that has everything on the Master sorted by one column. Then another Spreadsheet that has only certain columns sorted differently. Then several other spreadsheets that have some columns, but to only include rows that contain certain data(I think I can do this on with Filter, not sure though).

As it is right now we need all those different sheets, but every time a customer asks for a list of boots that meet certain requirements I just copy the master, delete the columns I do not need and do a sort. Then, I need to go and manually update all spreadsheets when something changes in the Master list. Anytime we receive a price increase I need to go into dozens of different spreadsheets and update prices. Anytime we bring on a new item I have to manually add it to several places. I think you get the picture.

Is something like this even possible? I have done some googling and looking around but not finding anything very helpful.

Hope that makes sense.
 
Check out pivot tables.
 
Pivot tables do not really help. I found VLookups to be of some help though. Pivot tables do not bring over all the columns I want.

Let me try to explain what I have. There is a Workbook called Master with a table containing Part#, Name, ST, SR, EH, WP, PR, ESD, MG, MFG, Gender, Style, Cost, Retail, Price 1, Price 2, Price 3. ST is one of 3 values. SR, EH, WP, PR, ESD, MG are Y or N. Gender is M or F. Style is one of 9 different things. Price 1 is derived from Cost with a formula. Price 2 is 10% off price 1 with a formula and price 3 is 15% off price 1 with a formula. I can already filter the data with a table so that it only shows me the items with Y in certain columns.

What I need is to create a seperate Workbook that has only some columns from Master. For instance, I want a Workbook with only Part#, Price 1, 2 and 3, sorted by Part# for my sales associates.

I then want another Workbook that has everything except Cost and only one of the prices to send to customers based on what price level they are at. Some of these will be Filtered by the table to show only certain items.

I also want 2 seperate tables, based on master that again have all the data except cost, one sorted by Style and another sorted by Price. These 2 are so I can have publisher pull the data from them to create a catalouge. This brings me to another problem. I have not been able to get publisher to pull a picture from Excel. I am guessing this is because the picture in not -in- the cell but on top of it. If there is a way to fix this it would also be nice.


What I can do to create various workbooks I need is use VLookup to get the text from the table though. However, with VLookup if I add or remove from the Master I have to go add the Part# manually to every other Workbook it is in. Since I cannot e-mail customers the Master containing cost, the VLookup does not have the data to draw from when they open it. I convert to a .pdf and send that. So again, each workbook needs to have the discontinued items removed, new items added, then be converted to a pdf.

When I have 50 customers, all with custom Price lists and catalouges, it would be much easier to just update the Master and let my sales team know to send out the new workbooks to their customers. Instead I lose a lot of time to manually updating everything then notifying them.

Hope this makes more sense.
 
Ok, I figured out a little more, and found out the one piece of the puzzle I need.

Is there a way to tell Excel I want to take all the data in column 1 on table 1 in workbook1 and list it here starting in this cell?

From that point, I can either repeat that for multiple columns or just use VLookups.
 
Hi,

Would it be possible for you to attach a copy of your file?
It would make things easier.
 
Hum,

I have a file that I created several years ago that dealt with customer service.
If you want it I can send it to you. Just PM me.
 
Pivot tables do not bring over all the columns I want.
I'm almost certain they do!

I don't see any problems with any of the paragraphs in your msg#3.

It will even cope with your "add or remove from the Master" negating your having to "add the Part# manually in every other workbook". You'll be able to convert to pdf still or perhaps copy paste-special values. Better, this could be automated with a macro.

You'll be at, or very close to being at: "When I have 50 customers, all with custom Price lists and catalouges, it would be much easier to just update the Master".

If you want, link to, or attach a workbook with Master and other sheets showing what you want the other workbooks to show and I'll try to make the pivot tables. If the data is sensitive do some search-and-replacing to make it less so, or if it's ultra sensitive, Private Mesage me here for an email addres with a view to sending something to me privately.


Regarding pictures in Excel, if pictures are reliably placed relative to cells, macro code can identify them (among other properties of a picture, they have a topleftcell property which can be used).
 
I'm almost certain they do!

I don't see any problems with any of the paragraphs in your msg#3.

It will even cope with your "add or remove from the Master" negating your having to "add the Part# manually in every other workbook". You'll be able to convert to pdf still or perhaps copy paste-special values. Better, this could be automated with a macro.

You'll be at, or very close to being at: "When I have 50 customers, all with custom Price lists and catalouges, it would be much easier to just update the Master".

If you want, link to, or attach a workbook with Master and other sheets showing what you want the other workbooks to show and I'll try to make the pivot tables. If the data is sensitive do some search-and-replacing to make it less so, or if it's ultra sensitive, Private Mesage me here for an email addres with a view to sending something to me privately.


Regarding pictures in Excel, if pictures are reliably placed relative to cells, macro code can identify them (among other properties of a picture, they have a topleftcell property which can be used).

From what I could tell, a Pivot Table can only be used within the same workbook. I need the data to go to a separate workbook. Also, when playing with the Pivot Tables, any time I added a column with values to it all it would display is a 1. Here is the file with our everything in the cost column changed to $2. Can't have people knowing what we pay.

View attachment TestMaster.xlsx

The things I need, which I should be able to figure out how to do everything else from are:

1) Price List showing only Part#, Retail, Price 2, Price 3. It should be clean and printable with no other data. Headers on each page listing what each column is. This is for sales associates.

2) The second thing I want is a list of Part#'s sorted by Style and has all the data from everything except Cost. This is for customers.

3) Same as 2 but sorted by Retail Price.

4) A list of Part#'s, with all columns except for cost and only retail price that have a Y in SR and PR.

All of these need to be in separate workbooks, not worksheets.

Thanks for the help. Eventually, we will be stocking a lot more boots and when we get our new software it will be able to pull cost and pricing from the spreadsheet as well. Only having one place to update everything will be super nice.
 
See attached. It contains sheets which I think addresses all your requirements.
They're on sheets, not workbooks. You can convert the sheets to workbooks in a one-time operation by right-clicking the sheet's tab, choosing move or copy… then in the first field choose new book, (or another existing workbook if you want). Again, a one-time operation, you can confirm the source data (yes it can be in another workbook, and the Master workbook doesn't have to be open for the data to be fetched/updated) by first selecting any cell in the pivot table, then from the Data section of the Options part of the PivotTable Tools part of the ribbon, choosing Change Data Source, Change data source… (again), then if it's not correct (showing the workbook name and something like: [ExcelGuru2000.xlsm]Sheet1!Table1), you just have to select the table with the mouse, it should resolve to a table name. This last is important so that the pivot tables update properly after you've added/removed data from the table.
When data's been changed in the Master table, you just need to click the refresh (all) button or there is an option for the pivot to update itself on file open.

Regarding the production of files to send out, the process of creating either workbooks with just data (no connections to original data) or .pdf files, can be automated.
 

Attachments

  • ExcelGuru2970TestMaster.xlsx
    81 KB · Views: 146
While testing post posting, I noticed I had a problem with "This last is important so that the pivot tables update properly after you've added/removed data from the table." It turns out that could be me talking rubbish; sure it applies within a workbook but it seems pivot tables don't like using named ranges or tables in external workbooks. The solution is to use a range address bigger than the Master table will ever be, like: [ExcelGuru2970TestMaster.xlsx]Sheet1!$A$3:$S$500
This will introduce some (blank)s into the pivot which can be filtered out. I wouldn't use entire columns for this.

Another solution is to query the external workbook, this may be a slicker answer - I'll explore this tomorrow and come back.
 
See attached. It contains sheets which I think addresses all your requirements.
They're on sheets, not workbooks. You can convert the sheets to workbooks in a one-time operation by right-clicking the sheet's tab, choosing move or copy… then in the first field choose new book, (or another existing workbook if you want). Again, a one-time operation, you can confirm the source data (yes it can be in another workbook, and the Master workbook doesn't have to be open for the data to be fetched/updated) by first selecting any cell in the pivot table, then from the Data section of the Options part of the PivotTable Tools part of the ribbon, choosing Change Data Source, Change data source… (again), then if it's not correct (showing the workbook name and something like: [ExcelGuru2000.xlsm]Sheet1!Table1), you just have to select the table with the mouse, it should resolve to a table name. This last is important so that the pivot tables update properly after you've added/removed data from the table.
When data's been changed in the Master table, you just need to click the refresh (all) button or there is an option for the pivot to update itself on file open.

Regarding the production of files to send out, the process of creating either workbooks with just data (no connections to original data) or .pdf files, can be automated.

Ok. Now my question is, how did you do that? Every time I put something in Row Labels it would put it all in the same column. For instance if I started with Part# then added Name, each name would be right below its corresponding Part#. Adding Sizes Available is then below that and so on.

Ok, now the tricky part. My Boss wants the price list formatted like so:
View attachment FormattedPriceList.xlsx

Other custom price lists will need to be formatted differently as well, depending on customer needs. Some want pictures, some don't. Sometimes it needs to be able to be a voucher for the employees to bring in so we need to add places for them to write in names above and PO#'s and such, causing me to have to split the Table and have it in 2 sections.

I do not think it is possible, though you've already proven me wrong, to "break up" a pivot table so that the data is displayed in such a way. Also while still maintaining the ability to receive changes from the master table. I was able to achieve this effect with VLookup, again though, if the file was sent to someone it would not have the data to lookup.

In other words, I need the ability to have a formatted workbook that is able to update itself from the master but when sent to someone retains the data.

If this is not possible, it is not a big deal. I can continue to do things the way I have. I just need to update individual customers vouchers/price lists/catalouges each time we renew a contract. When we add a new shoe and go to send them the new forms, it gets done, customer by customer. As we grow and get more customers it will take me longer, but that's ok if it has to be that way.

Thanks for all the help, it is really appreciated.
 
Last edited:
I'm aware of time passing and I haven't had time today to look very much at this, so in the interim:
Ok. Now my question is, how did you do that? Every time I put something in Row Labels it would put it all in the same column. For instance if I started with Part# then added Name, each name would be right below its corresponding Part#. Adding Sizes Available is then below that and so on.
When you're in a Pivot table, in the Pivot Tools part of the ribbon, in the Design tab, in the Layout section, you should see Report Layout, choose Tabular, while you're at it remove all Grand- and Sub- totals using the neighbouring icons.




Ok, now the tricky part. My Boss wants the price list formatted like so:
I will look at this soonest.




Other custom price lists will need to be formatted differently as well, depending on customer needs. Some want pictures, some don't.
I'll need more info when I come to the pictures part.




In other words, I need the ability to have a formatted workbook that is able to update itself from the master but when sent to someone retains the data.
If you were to send a workbook to one of your customers with vlookup formulae in, they might be able to see the data to start with, but as soon as the sheet is recalculated (and maybe you can prevent a recalculation taking place in the options for that file (I could look into this if you want)) the cells would all come up with errors because the formulae wouldn't be able to see the Master file. It's more common to send a file where the formulae have been removed by copy/pasting values in situ, but then you remove the file's capability of updating itself, so you can't really use a single file to send and to update. Generally, people copy the file, remove all the formuale and send that, keeping the version that can update itself. You've effectively been doing this already by sending pdf files.
Your problem was the increasing length of time it took to update the reports, and I'm suggesting Pivot tables or SQLing the Master file can get over that. I can also write you some code to automate the preparation of new files (pdf or excel files), perhaps even to prepare a message in Outlook and attach said files, displaying the email ready for you to add the email addreses you're sending them to (even this last can be automated if you have a list of customers, their adresses, and what they want to see).




I do not think it is possible, though you've already proven me wrong, to "break up" a pivot table so that the data is displayed in such a way. Also while still maintaining the ability to receive changes from the master table. I was able to achieve this effect with VLookup, again though, if the file was sent to someone it would not have the data to lookup.
I'd probably make up two pivot tables and then copy/paste-values them together for customer consumption


I will come back here when I get the opportunity…
 
I'm aware of time passing and I haven't had time today to look very much at this, so in the interim:
When you're in a Pivot table, in the Pivot Tools part of the ribbon, in the Design tab, in the Layout section, you should see Report Layout, choose Tabular, while you're at it remove all Grand- and Sub- totals using the neighbouring icons.




I will look at this soonest.




I'll need more info when I come to the pictures part.




If you were to send a workbook to one of your customers with vlookup formulae in, they might be able to see the data to start with, but as soon as the sheet is recalculated (and maybe you can prevent a recalculation taking place in the options for that file (I could look into this if you want)) the cells would all come up with errors because the formulae wouldn't be able to see the Master file. It's more common to send a file where the formulae have been removed by copy/pasting values in situ, but then you remove the file's capability of updating itself, so you can't really use a single file to send and to update. Generally, people copy the file, remove all the formuale and send that, keeping the version that can update itself. You've effectively been doing this already by sending pdf files.
Your problem was the increasing length of time it took to update the reports, and I'm suggesting Pivot tables or SQLing the Master file can get over that. I can also write you some code to automate the preparation of new files (pdf or excel files), perhaps even to prepare a message in Outlook and attach said files, displaying the email ready for you to add the email addreses you're sending them to (even this last can be automated if you have a list of customers, their adresses, and what they want to see).




I'd probably make up two pivot tables and then copy/paste-values them together for customer consumption


I will come back here when I get the opportunity…

Again, thanks for all the help. Any time you can give is greatly appreciated and I understand that it may take time for responses. Forums are generally that way. You cannot expect an answer straight away. I have other things to take care off to.

I think I am almost where I need to be. As far as the pictures, just being able to attach them to a cell so they will be in the table would likely work I think. That way I can just have the pictures in the master and pull them when needed either via a pivot table or vlookups.

As far as sending with vlookup formulae, I think when you send something to someone it does not have editing enabled. Unless they enable it, they shouldn't lose the data. I thought you could PW protect a file to prevent enabling editing on in the file menu. Not sure, will look at this as well. Though as you said, I've essentially been doing this anyway. What I think I will do instead is just make the .xlsx files available to the sales team, they will be automatically updated when I update master, then teach them to convert it to .pdf before sending it. Pretty easy with something like CutePDF. This eases up my work and is not much to ask of them IMO.

Two Pivot Tables could work. Within the Master I will simply create a separate sheet for each Customer, labeled with their customer number anyway. Will just set up each sheet with the Pivot Tables in the way I need them for their particular form, then transfer that data to the individual workbooks.

I also test adding a new shoe to the Master Table and the Pivot Tables updated. I think the is because the PivotTable is pulling From a Table instead of a Range of cells so it sees any changes to the table itself regardless of how many rows are in it.

I must thank you again for all the help. I believe you have made my job a lot easier. I may work myself out of a job now though, lol.
 
See what you think of this macro, but before you run it you must makes some adjustments so it knows where your master file is:
The line:
Code:
Set myListObj = .ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=Excel  Files;DBQ=C:\Users\P45cal\Documents\ExcelGuru2970\ExcelGuru2970TestMaster.xlsx;DefaultDir=C:\Users\P45cal\Documents\ExcelGuru2970;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;",  Destination:=.Range("$B$48"))
has:
DBQ=C:\Users\P45cal\Documents\ExcelGuru2970\ExcelGuru2970TestMaster.xlsx;DefaultDir=C:\Users\P45cal\Documents\ExcelGuru2970;

Adjust all instances of the folder name to your folder name and the filename to your master filename.

You should also create a named range called PTData in your Master file, being your table in the master file including its headers. The new named range seems (so far) to expand and contract with the table.

Also the Part# column in the Master file table should be formatted as Text or you might get some blank Part#s

It creates a new workbook each time you run it.

I attach a workbook with just a blank sheet with a button on it which calles up the code in case you're not sure what to do with the code.

If you make all the above adjustments properly, the new book should be created with data in it without further interaction after clicking the button, however, if you make a mistake with the folder or filename you'll get a dialogue which if you click OK on will allow you to select the file manually (though it still must have a named range PTData in it).

I haven't put the report title and date in the new workbook, just wondering if this is a route you might like to go down?

Code:
Sub blah()
With Workbooks.Add(xlWBATWorksheet).Sheets(1)
  'With Sheets.Add(After:=Sheets(Sheets.Count))
  Set myListObj = .ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=Excel Files;DBQ=C:\Users\P45cal\Documents\ExcelGuru2970\ExcelGuru2970TestMaster.xlsx;DefaultDir=C:\Users\P45cal\Documents\ExcelGuru2970;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", Destination:=.Range("$B$48"))
End With
With myListObj.QueryTable
  .CommandText = "SELECT PTData.`Part#`, PTData.Retail, PTData.`Price 1`, PTData.`Price 2`, PTData.`Price 3` FROM PTData PTData ORDER BY PTData.`Part#`"
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .PreserveColumnInfo = True
  .ListObject.DisplayName = "some report name"
  .Refresh BackgroundQuery:=False
  .CommandType = xlCmdSql
End With
Set tableRng = myListObj.Range
myListObj.Unlist
With tableRng
  RowCount = .Rows.Count
  .ClearFormats
  With .Font
    .Name = "Calibri"
    .Size = 12
  End With
  .Columns(1).ColumnWidth = 18.86
  .Columns("B:E").ColumnWidth = 12
  .Columns("B:E").NumberFormat = """$""#,##0.00"
  If RowCount > 42 Then
    For rw = Application.WorksheetFunction.Floor(RowCount, 41) + 2 To 41 Step -41
      .Rows(rw).Resize(3).Insert
      RowCount = RowCount + 3
    Next rw
  End If
  For Each are In .Rows(1).Resize(RowCount).SpecialCells(2).Areas
    With are
      If NotFirstArea Then
        Set are = are.Offset(-1).Resize(are.Rows.Count + 1)
      Else
        NotFirstArea = True
      End If
      .Borders(xlInsideHorizontal).LineStyle = xlContinuous
      With .Rows(1)
        .Value = Array("Part#", "Retail", "Discount1", "Discount2", "Discount3")
        .Font.Size = 14
        .NumberFormat = "General"
        .Borders(xlEdgeBottom).Weight = xlMedium
      End With
      .BorderAround xlDouble, xlThick
      .Borders(xlInsideVertical).LineStyle = xlContinuous
    End With
  Next are
End With
Application.Goto tableRng.Cells(1).Offset(-2, -1), True
End Sub
 

Attachments

  • FirstReport.xlsm
    20.4 KB · Views: 15
Back
Top