+ Reply to Thread
Results 1 to 10 of 10

Can this be done: Link Cells / Update one cell, other cells in same column are updated?

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Can this be done: Link Cells / Update one cell, other cells in same column are updated?

    I am using Excel to keep inventory. Let's assume the following situation: tracking newspaper articles by dates that I cut out. So, in Columns B to F and Row 1 for all of them, they each have dates. In Column A, Rows 2 to 10, are the newspaper articles' titles. Some of these articles are directly behind another article I am interested in. In other words, if I cut out an article about apples, on the back of it was an article about shoes, but the lengths were different, with the shoes article being longer, and so the article on shoes is no good / incomplete.

    So, let's take Column B, Row 2 for example. If I put an X there or do something to that cell, I would like an X to automatically appear in Row 3 and Row 7 (so, in this case, cutting the article out listed in Row 2, resulted in two articles on the back being cut out completely or partially for one or both). With this same example, if I put an X in Row 3, I would like an X to automatically go in Rows 2 and 7. If I put an X in Row 7, I would like one to automatically appear in Rows 2 and 3.

    To summarize:

    X in B2, X to appear automatically in B3 and B7
    X in B3, X to appear automatically in B2 and B7
    X in B7, X to appear automatically in B2 and B3

    Can this be done in Excel? If not, do you have any idea of what program I can use to keep track of something in this manner?

    UPDATE: I have attached a file to show what I mean. The concept is the same, but different cells than those specified above are demonstrated. For each column, the cell with a yellow background was marked / had an X manually placed, and those in red, for the same column, are supposed to have the X added automatically. As you can see, for each column, the red cells corresponding to the yellow cells can be any set of cells. I would like a way to specify and link a set of cells per column, so that when any one cell of the linked set of cells have an X placed, the rest of the linked / grouped cells are updated / have an X placed in them. Note: I am saying to place an X to make things simple, but colored backgrounds without an X as I have in the attached file is also acceptable.

    UPDATE 2: I have updated the file, making changes in Column B, tho show that I would be interested in more than one linked set of cells per column. So, for Column B, for the cell with the green background, the X was entered manually, and for the cells with the blue background, the Xs are supposed to appear there manually. As you can also see, in such a case, the Xs are not really needed / not enough to distinguish one set of linked cells from another. Colored backgrounds seems to be the best way to do this, unless there is some other way that I am not thinking of--some other way to structure / organize the data and achieve the same set of results.

    So, again: Can this be done in Excel? If not, do you have any idea of what program I can use to keep track of something in this manner?
    Last edited by roseuz; 02-05-2015 at 08:24 AM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Can this be done: Link Cells / Update one cell, other cells in same column are updated

    Hi i don't really understand what you are trying to achieve.

    But I can do what you define in the summary quite easily.

    I will create something to get you started.

    This code will do what you want.

    It is a bit of an over kill but will be easy for you to modify.

    Please Login or Register  to view this content.
    right click on the sheet name at the bottom of excel and select view code to view and edit the code.

    The following code would have the same result but may not be as adaptable as the above.


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 02-01-2015 at 09:33 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Can this be done: Link Cells / Update one cell, other cells in same column are updated

    Thanks, but keep in mind what I said above was an example. At other times it will be different cells and different amounts of cells, but always connected per column.

    So, other times I would want

    An X in B4 and for one to automatically appear in B7, B8, and B9
    and also
    An X in C3, and for Xs in C5 and C9 or in C5, to appear in C3 and C9
    and so on.

    Would your code work for any example, and for multiple columns? Would I have to add / copy and paste and edit portions for every set of cells I want linked per column?

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Can this be done: Link Cells / Update one cell, other cells in same column are updated

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Can this be done: Link Cells / Update one cell, other cells in same column are updated

    A file is attached.

  6. #6
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Can this be done: Link Cells / Update one cell, other cells in same column are updated

    So, I guess what I am asking about is not possible. Does anyone have any idea as to what other way or other program I can use to accomplish what I am asking?

  7. #7
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Can this be done: Link Cells / Update one cell, other cells in same column are updated

    This seems very random, how is excel mean't to know which cells are tied on each date? Is there a formula to this or is there a commonality in the names in column A?

    Once excel is told which cells are to be linked it can work it out.
    Since any of the 3 (or 4) linked cells can trigger the others, I'm pretty sure you'll need a macro to make it happen.

    If there is no pattern, formula, or commonality of names, then I think you'd need an extra column beside each date to tick or mark the cells that need to be linked for that day. But then that's what you want the formula to do.

    I don't know what else to say, there needs to be a formula that states "on this day the groupings will be this"

  8. #8
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Can this be done: Link Cells / Update one cell, other cells in same column are updated

    Using your analogy of the newspaper articles, maybe you could give each cell a grid reference for where the article is on the page including front or back. Then excel could cross-reference these grids and mark the ones that overlap between front and back.
    Most papers are organised by columns but you'd also need to break it down into certain sized rows (possibly inches) and then you need a start and end position or row for each article.

  9. #9
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Can this be done: Link Cells / Update one cell, other cells in same column are updated

    Thanks for your response. I don't know if it makes a difference, but the real reason I want this is for when I am working with coupons. The coupons are of different sizes and positions, and so as I said with the newspaper example, cutting one, ends up cutting one or more than one on the back.

    Regarding your last post, are you able to modify my attached Excel file so I can better understand what you are saying.

  10. #10
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Can this be done: Link Cells / Update one cell, other cells in same column are updated

    I was trying to come up with a way to modify your sheet but I kept running into different problems.
    What I am now thinking is maybe make a list of all coupons and their locations in the paper and then populate your sheet from that.
    So in Sheet2 of the attached file, I created a list where you can place all coupons (even in different papers if you wish.
    Then maybe a VBA expert here could create a code to read that list and create your sheet 1 from that.
    You could have Drop-down selection boxes to choose the the voucher type you prefer (e.g. Shoes) and maybe the date(voucher expiry date?) then it would populate a list of all papers that have a Shoe voucher as well as which vouchers would be destroyed by using that particular voucher.
    Attached Files Attached Files

+ 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. How do I update a cell in column C with a date stamp anytime cells A,B,D,E,F
    By nikolastrum in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2014, 01:19 PM
  2. How do I cause my macro to 'auto-update' whenever linked cells are updated??
    By Scottie in BKK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-06-2012, 05:26 PM
  3. Update Cells Based on Updated Files
    By Spreadsheet Surfer in forum Excel General
    Replies: 0
    Last Post: 01-07-2010, 01:14 PM
  4. Link two cells to automatically update
    By Nathan in forum Excel General
    Replies: 15
    Last Post: 07-26-2006, 03:25 PM
  5. [SOLVED] How do I link a row of cells in wks 1 to update diff cells wks 2
    By Andy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2006, 12:35 PM

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