+ 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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,342

    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

    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    If someone helped you, please click on the star icon at the bottom of their post
    If your problem is solved, please go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and 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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,342

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,342

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,342

    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)

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