+ Reply to Thread
Results 1 to 26 of 26

Linking sheets - Basic Requirements

  1. #1
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Linking sheets - Basic Requirements

    I've 2 sheets linked, the source file helps me auto populate data in the other one.

    What are the basic precautions I need to keep in mind, that the sheet which gets auto populate does not lose the formula.

    The source file gets updated by several people and keeps traveling over email.

    My guess is:

    I need to make sure that the name of both should not change.
    Will the file in a folder on my desktop get populated if someone else is updating the master at their work station? If not, will it get updated when I get the Master back and paste it in the designated folder on my system?

    How does this work is all I want to know, as I've already applied all formula?

    Insights please....

    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    your desktop file should update if you are on the n/w
    my concern is the emailed file. if it is not saved in exactly the same folder/path as your link, the link will not work with the "new/emailed" file, and will instead pull from the "old" file
    if you have a concern that the auto populate file may have formulas messed with/over-written, then maybe consider protecting them
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    FDibbins, Thanks for your prompt response! Much appreciated

    But if this is the scenario, I'll be in a mess.

    The "Master File" is not on a network. It is passed on through email, gets updated and returned back. If I save it in exactly the same folder after downloading from the email (once it is returned), will the file sitting on system get updated automatically? If not, what alternatives do I have? Please advise.

    I'm using 2007 Excel.

    Thanks again!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    if the links to the other file/s contain the full path to where they are, it should not make a difference where the Master file is saved. and even tho the Master may not be saved on the N/W, if that PC is linked to the N/W, the linkls should still work

  5. #5
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    Fantastic!

    One more thing I am able to change the source file (Master) once it is back after updation, but for the formulas (in the file on my system) to pick the values requires me to copy data into another xls. Otherwise, the Update Values, Change Data Source options appear as greyed out.

    Is this something normal or am I missing out a trick?

    Once again thank you! Not sure how to acknowledge your efforts to help me out!

    Cheer!!!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    can you talk/walk me thru (in detail) what you do, to help me better understand your process?

  7. #7
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    FDibbins, thanks!

    It would be my pleasure to walk you through the intricacies in detail to help you understand it better, and conclude my apprehensions...

    Please let me know how would you prefer to goo about it an Email / IM / Phone call. I'm happy to do any of the following @ a time which is suitable to you. Please advise.

    Thank you!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    here would be good, that way maybe others could help and/or learn as we go along

  9. #9
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    Sure! So here's the process:

    I have 2 sheets:

    - Master is updated and passed from one person to another via email
    - The second file has been linked and is saved on my desktop

    Steps taken:

    I've linked the second file with the Master to facilitate auto populate, which is working fine on my system.

    Requirement:

    Need to be 100% sure that the data on my system file gets updated, with the changes to the Master file - shared on email - being made every hour.

    Questions:

    Master is shared and updated via Email, so will the file sitting on my system get updated when someone else updates it on theirs?
    How do I ensure my requirement is met flawlessly, given the Master file name changes daily?
    If not, what process do I need to adhere to have the file get auto populated once I get the Master back?
    Any other specifics as you may deem fit, that need to be kept in mind.

    Hope I've been able to explain my requirements and concerns! Please let me know if you need any further clarity!

    Much much appreciated!

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    just so i understand you, which way does the linking go? is the Master fed by the file of your desktop, ie the file on your D/T has info that the Master uses, or is it the other way round?

  11. #11
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    My desktop file gets updated through the Master file. The linking formulas are on my desktop file, that pull data from Master.

    Thanks!

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    Master is shared and updated via Email, so will the file sitting on my system get updated when someone else updates it on theirs?
    How do I ensure my requirement is met flawlessly, given the Master file name changes daily?
    i think you said somewhere that the master file gets sent to you with a different name every time? if this is the case, your desk top file will have no way of knowing where to find its data because the links will point to a different file name.
    2 ways around that - change the emailed file name when you download it to the same name as what your links point to (easiest) or change the links in your desk top file every time you get a new update, altho the linking formula can be modified such that you enter the new file name in a specified cell in the desktop file and formula adjusts accordingly (more complex, but do-able)

  13. #13
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    Hmmm...that solution makes sense...thanks!

    Although, I was successful in getting the file sitting on my desktop updated with the new file today simply by using the Change Source Option using Edit Link Option, but this gave me an error when I added a column to my Master file, primarily cause the cell reference changed. Is there a work around that?

    Can I freeze the formula to pick up the references assigned irrespective of the change in the Columns?

    Please advise...

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    not sure how you did your linking (vlookup? =cell?), but if you intend to add columsn, it might be better to use index/match to find the column heading/name instead, that way, it shouldnt matter if you add columns

  15. #15
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    This is the formula I'm using to link sheets: =SUMIF('[Product File.xlsx]Sheet1'!$B:$B,$A5,'[Product File.xlsx]Sheet1'!L:L).

    Say if the column is moved to D instead of B and the second one to N instead of L. What modification would be required to ensure that the formula doesn't get affected?

    Where / How should exactly should Index/Match be added to the existing linking formula above? Please advise!

    And a BIG thank you for your immense help and patience with this request of mine!

    You're a genius by all means!

    Cheers!!!
    Last edited by lifeisaspreadsheet; 10-05-2012 at 03:41 PM.

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    happy to help

    note that sumif() to a linked file only works if the linked file is open too.
    this formula should eliminate the need to change your formula if you add/delete extra columns. i dont think you uploaded a sample file, so i made it based on some of my own files. change the file names, ranges and headings (in the formula) to suite

    =SUMIF([desktop.xlsx]sheet1!B$3:B$12,A14,OFFSET([desktop.xlsx]sheet1!$B$3,0,MATCH("services",[desktop.xlsx]sheet1!$B$3:$F$3,0)-1))

    (its not as bad as it looks) let me know how you make out

  17. #17
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    Certainly not Its is awesome. I'll apply it as soon as I get to my system, and will let you know of the outcome.

    As I understand, "services" in your formula = Column Header.

    Match I understand, but was wondering if what the OFFSET function is doing or should I go to go to Tools|Formula Auditing|Evaluate Formula to understand the way the formula is working?

    You are a magacian of Excel! I'm feeling proud to be your student.

    Regards

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    hehe thanks for the kind words, but many on here are FAR more advanced than i am. as far as the offset() is concerned, it is "moving" the column reference 1 way or the other, dependant on which column number (not letter) "services" is found in. so this allows you to add/delete columns, and the formula will adjust accordingly. and yes, you are correct, "services" is the column header

    as a further refinement, IF your file name will be changing from time to time, that formula can be further modified to allow for the file name to be manually added to a specified cell, and the formula will use that (instead of "desktop.xlsx". but try that 1 1st, and if needed, we can take the next step

  19. #19
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    Dear FDibbins,

    I tried hard but the irony is that I'm badly stuck trying to apply the OFFSET and Match function to the linked file.

    Here's the current formula: =SUMIF('[Project Report.xlsx]Sheet1'!$BP:$BP,A77,'[Project Report.xlsx]Sheet1'!$BT:$BT)

    Can you please help modify this...I've spent hours but to no avail

    Thanks!

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    can you upload 2 sample work books for me, that way i can see your data and what you are trying to do where?

  21. #21
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    Sure thing, I'll do it right away! Thanks

  22. #22
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    Here are the sheets attached.

    Project Report is the Master file from which the data is to be linked on to the Project Summary. I've applied the formulas I'm currently using.

    I need to ensure that If any columns are added my formula doesn't get affected, because the columns and rows are bound to increase by each passing day. I'm not sure how to use the Match and OffSet function along with my current formual in column C.

    Secondly, may be I'm asking this again, but since you have the data files now, is it possible to auto add rows and their corresponding data on the Project Summary sheet everytime such an action is taken on the Project Report? I would mainly like the Project name updated on that...

    Please advise.
    Attached Files Attached Files
    Last edited by lifeisaspreadsheet; 10-07-2012 at 04:30 AM.

  23. #23
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    ok try the attached, i have modified the formulas somewhat, check to make sure this is what you want?

  24. #24
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    Dear FDibbins,

    The sheet attached above "Copy of Lookup Key Modified Again not working" doesn't seem to be a part of my request.
    Can you please check and see if this was eroneously posted against my request or if I'm missing anything.

    Thanks!

  25. #25
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Linking sheets - Basic Requirements

    LOL im really sorry, i uploaded the wrong file for you.

    try this 1 instead. i didnt make anyt changes to your data file, just your summary file.
    Attached Files Attached Files

  26. #26
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: Linking sheets - Basic Requirements

    Dear FDibbins,

    Doesn't seem it is working correctly, unless I'm missing something badly!

    The formula in Column C of the summary sheet is getting affected as I add new columns.

    Could you please have a look at it and advise...

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1