+ Reply to Thread
Results 1 to 11 of 11

Stop cells from changing value when a cell in the same range is changed?

  1. #1
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Stop cells from changing value when a cell in the same range is changed?

    Hi all,

    In column D (range D8 to D27) i have a drop down list, which when you select a value from will enter a code into another column N (range N8 to N27)

    The problem i am having is if N8 to N27 already has a value in it,..and if N8 or any other cell in that range is changed later on (by choosing a different option from drop down list in column D) then the values in the cells after are changed by one (plus one), but i don't want those to change as well. How can i prevent this from happening?

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Stop cells from changing value when a cell in the same range is changed?

    There are some questions that would be answered fastest by providing your file. The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

    If for some reason you can't do that then:

    In column D, I assume you are using Data Validation with a dropdown list. What is the formula you are using for the list?

    What formulas are in column N that cause them to change when something is selected from D?

    How is it possible for cells following a cell in column N to be incremented by 1?

    It sounds like you may have macros. If so, then you need to provide your macro code as well the above information.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: Stop cells from changing value when a cell in the same range is changed?

    I have attached my workbook. My problem is for example if in cell D7 (FLR(FP)) is changed to an S code for example to SET(M) than the other S codes already on this work sheet (also later and previous ones) change by +1 one, I need to be given a new S code number which is not used elsewhere in the workbook and other sheets. I need to apply this condition to any changes and not to just this example. Is this possible?
    Last edited by AliGW; 02-28-2018 at 12:47 PM. Reason: Unnecessary quotation removed.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Stop cells from changing value when a cell in the same range is changed?

    The codes in column N ("codes") use an array formula to create a sequence based on the Item code in column D ("item"). If you change and item, then the codes get resequenced. They don't simply change by +1; it just resequences the whole list. Some might go up by 1, some might go down by 1.

    It sounds like you want to assign a code when an item is selected, and never change that code. To do this, you must assign the codes in VBA, instead of using a formula.

    I started to do this and ended up spending too much time trying to reverse engineer that long formula for your numbering scheme. Please describe how you want numbering to work. Does the numbering accumulate across pages?
    Last edited by 6StringJazzer; 02-28-2018 at 12:39 PM.

  5. #5
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: Stop cells from changing value when a cell in the same range is changed?

    Hi, i want the numbering to be unique, with the starting numbers i already have. I just dont want other cell values to change if a previous item drop down is changed.

    The numbering is stated in sheet called "names"

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Stop cells from changing value when a cell in the same range is changed?

    Does the numbering accumulate across pages? Or does it start again on each page?

  7. #7
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: Stop cells from changing value when a cell in the same range is changed?

    It accumulates across pages. at the moment there are just six pages, but there will be many more so need to accumulate across every sheet added.
    Last edited by defenders; 02-28-2018 at 01:00 PM.

  8. #8
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: Stop cells from changing value when a cell in the same range is changed?

    any luck with this 6stringjazzer?

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Stop cells from changing value when a cell in the same range is changed?

    Looks like a lot more work that I expected. Still trying to find the time. I believe this requires a VBA solution. If you make a change to a code but don't want to change any other existing codes, then you have to scan all codes on all pages to determine the correct sequence number for the changed code. It's a bunch of work. There is not any way (that I know of) to produce a value using a formula, and then "freeze" that value so it never changes.

  10. #10
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: Stop cells from changing value when a cell in the same range is changed?

    Quote Originally Posted by 6StringJazzer View Post
    Looks like a lot more work that I expected. Still trying to find the time. I believe this requires a VBA solution. If you make a change to a code but don't want to change any other existing codes, then you have to scan all codes on all pages to determine the correct sequence number for the changed code. It's a bunch of work. There is not any way (that I know of) to produce a value using a formula, and then "freeze" that value so it never changes.
    Thanks for the reply and looking into this for me. I hope you can figure out a solution. I've been stuck trying to figure this out for awhile.

    I look forward to your positive reply soon. Many Thanks.

  11. #11
    Registered User
    Join Date
    12-31-2017
    Location
    UK
    MS-Off Ver
    7
    Posts
    42

    Re: Stop cells from changing value when a cell in the same range is changed?

    Quote Originally Posted by defenders View Post
    Thanks for the reply and looking into this for me. I hope you can figure out a solution. I've been stuck trying to figure this out for awhile.

    I look forward to your positive reply soon. Many Thanks.
    any update?

+ 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. cell content changed automatically - need to stop it
    By seraphin in forum Excel General
    Replies: 1
    Last Post: 02-20-2014, 06:37 PM
  2. [SOLVED] Stop the range changing when i drag it across cells
    By mickgibbons1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-25-2013, 01:45 PM
  3. If any cell in range is changed to a certain value/text, fill cells to right with pattern
    By suzanchesson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 09:47 AM
  4. Changing values based on which cell is changed within a range.
    By ronnie301184 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-11-2012, 05:25 AM
  5. stop a macro from running everytime a cell is changed
    By bassfisher in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-06-2005, 10:05 AM
  6. stop from running everytime a cell is changed
    By bassfisher in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-03-2005, 05:05 PM
  7. Worksheet Change Event-when a cell with a certain range of cells) are changed?
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2005, 07:06 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