+ Reply to Thread
Results 1 to 19 of 19

Data affected across sheets by Moving cells

  1. #1
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Post Data affected across sheets by Moving cells

    Hi there,

    I have a workbook where cells are referenced between separate sheets on the book, by the way of =Master!F17 (where "Master" is the name of the first sheet). However, when I then move the cell F17 in the original sheet, it gets very confused in a number of ways:

    first effect: It changes the formula in the secondary sheet to reflect the new Cell position of the data in the "Master" sheet eg. moving down one would change to =Master!F18
    second effect: if the cell moves to a position being referenced by a separate formula on the secondary sheet, eg =Master!F18 already there, it returns an error of #REF! where Master!F18 was originally being relayed.

    Is there any way of reflecting data across sheets, that is not prone to the destructive effects of moving Cells within the "master" sheet?

    (the purpose of all of these fomulas is to generate visual labels for printing on the secondary sheet - therefore data from the first sheet needs to be displayed in the secondary sheet. However when editing the original sheet often requires moving cells, due to the nature of how data is organised at this company)

    Thanks

    Joey

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data affected across sheets by Moving cells

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

    Please read the yellow banner at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Data affected across sheets by Moving cells

    Hi Glenn

    Okay will do, apologies. About to finish work however, will have to return to this tomorrow.

    Thanks!

    Joey

  4. #4
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Data affected across sheets by Moving cells

    Hi

    PFA a small example of the setup. I realise now that the function CONCATENATE (now CONCAT) is also used to ensure result is blank on Auto-Labels sheet, and therefore avoids an "0" appearing, where the cell being referenced in the formula is blank on the Master spreadsheet.

    It won't let me attach anything even after uploading on "Manage Attachments" as stated. Tried Chrome and Explorer... Attachments Dialogue always remains blank

    If you add this to the usual URL it should hopefully work -> attachment.php?attachmentid=652898&stc=1&d=1575623072

    Thanks

    Joey
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Data affected across sheets by Moving cells

    Ah. There it is. Rookie badge earned.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data affected across sheets by Moving cells

    It attached OK. What EXACTLY do you mean by:
    "when I then move the cell F17 in the original sheet"

    Why do you have all those merged cells? They cause huge problems downstream. If you need a wider column, widen it.

  7. #7
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Data affected across sheets by Moving cells

    Hi

    This is a workbook that my company uses. I did not design it.

    When I say "Move the cell F17 in the original sheet" I mean hover the mouse over the border of the cell, and drag it to a new position. Eg. if I wanted to move "item two" down to packing list 4, ie move F17 to F19.

    ... As you point out, the key to this issue may indeed lie within all of those merged cells. It does leave a path of destruction above as those original F17 cells are then un-merged once moved.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data affected across sheets by Moving cells

    No need for concatenate. This is the usual way to achieve this, and use absolute formula references. I only changed the horrible merged cells shaded yellow:

    =IF(Master!$N$30="","",Master!$N$30)

    This will follow the taregt cell around.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Data affected across sheets by Moving cells

    This has not solved it. Even if I unmerge the cells on the original packing list before moving, it still returns a #REF! error on Auto-Labels when I drag Cell N30 to N31 on the Master sheet.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data affected across sheets by Moving cells

    Show me. Post the sheet.

  11. #11
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Data affected across sheets by Moving cells

    Here you go.
    Attached Files Attached Files

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data affected across sheets by Moving cells

    You are not describing th eproblem. You moved "Item 3" from N30 to N31. Item 3 is still showing in a130.

    What you have done is overwritten the value that was formerly in N31, so the formula that was looking in that cell (A178 now does not know where to look and returns a #REF error.

    that's the way it works. If you want to have "Item 3" in N31.... type it in there.

  13. #13
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Data affected across sheets by Moving cells

    Okay I am with you.

    So I suppose there is no way of getting a cell in Auto-Labels to monitor N31 purely as a cell-position itself, rather than be affected by the 'overwritten value' inside it? Could be a useful function that avoids *a lot* of re-typing for me, for example if I need to reshuffle hundreds of packing list numbers. Though I get this idea probably runs antipathetic to fundamental piece of Excel behaviour.

    I get the reasoning better now, just looking for a solution for what feels like quite an intuitive action to me... maybe it is just not possible.

    Thanks for your help thus far!

    Joey

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data affected across sheets by Moving cells

    AFAIK, you're stuck with it. Isn't there some sort of helper column, which you could add, to facilitate sorting??

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data affected across sheets by Moving cells

    Or have all your items on a dropdown list, so rather than typing you can simply select? You might also need a helper column to count them, just to make sure you're only packig one item 1 in the lorry, not 2...

    DD's can also be made a bit smarter, with a little effort, to be searchable.

  16. #16
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Data affected across sheets by Moving cells

    Well we already use filtering when we have to temporarily sort items, which is helpful.

    It is not the end of the world - it is more a question of mechanics of every day usage and me anticipating the behaviour of the many people that use this sheet, gleaned from my own experience of it, and now looking to error proof it for people who may not understand the (admittedly straight forward) coding behind it!

    Joey

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data affected across sheets by Moving cells

    OK. Good luck!

  18. #18
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Cool Re: Data affected across sheets by Moving cells

    Danke schoen

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data affected across sheets by Moving cells

    Bitte schoen!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Detecting which other cells are affected by a cell
    By Kiredoryor in forum Excel General
    Replies: 6
    Last Post: 03-28-2019, 11:31 AM
  2. keep track of changes in the cells as comments in the affected cell.
    By srvgonzalez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2015, 01:17 PM
  3. [SOLVED] Need to limit the range of cells affected by code
    By Rednecknerd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2015, 05:27 PM
  4. Moving cells from different sheets to one
    By ExMAN999 in forum Excel General
    Replies: 9
    Last Post: 11-22-2011, 03:59 AM
  5. automatically moving data between cells/sheets in excel
    By angelabu in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-28-2011, 04:44 AM
  6. Locking & Protecting Cells Affected by Macro
    By tahira in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-17-2008, 11:36 AM
  7. [SOLVED] Moving cursor to identical cells across multiple sheets
    By Sue Sch in forum Excel General
    Replies: 4
    Last Post: 11-02-2005, 06:17 PM

Tags for this Thread

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