Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Have one spreadsheet automatically update in others

  1. #1

    Have one spreadsheet automatically update in others



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    Check out pivot tables.

  3. #3
    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.

  4. #4
    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.

  5. #5
    Acolyte Charles's Avatar
    Join Date
    Feb 2013
    Location
    Biloxi
    Posts
    34
    Articles
    0
    Hi,

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

  6. #6
    Quote Originally Posted by Charles View Post
    Hi,

    Would it be possible for you to attach a copy of your file?
    It would make things easier.
    I do not have 5 posts yet so it will not let me.

  7. #7
    Acolyte Charles's Avatar
    Join Date
    Feb 2013
    Location
    Biloxi
    Posts
    34
    Articles
    0
    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.

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by William F. View Post
    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).

  9. #9
    Quote Originally Posted by p45cal View Post
    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.

    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.

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •