• Formula in one cell changes when data in another cell is cut and pa

    From Claus Busch@21:1/5 to All on Sat May 22 21:06:26 2021
    Hi David,

    Am Sat, 22 May 2021 11:52:58 -0700 schrieb David Farber:

    I'm using Excel 2010 Home and Student edition.

    I've created a column, B, of questions which need to be answered on a
    scale from 0-5. Columns C-H are to be checked with an "X" or whatever character they see fit to indicate which answer they have chosen.

    For example, one of the questions in column B says, On a scale of 0-6,
    how painful is it for you to climb one flight of stairs?" The choices
    are, 0, column C, "not difficult," 1, column D, "minimally difficult,"
    etc. all the way up to 5, column H "unable to do." The end result is
    that column I figures out which column the "X" was placed and enters the numerical result.

    try:
    =MATCH("x",$C21:$H21,0)


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David Farber@21:1/5 to Claus Busch on Sun May 23 09:19:41 2021
    On 5/22/2021 12:06 PM, Claus Busch wrote:
    Hi David,

    Am Sat, 22 May 2021 11:52:58 -0700 schrieb David Farber:

    I'm using Excel 2010 Home and Student edition.

    I've created a column, B, of questions which need to be answered on a
    scale from 0-5. Columns C-H are to be checked with an "X" or whatever
    character they see fit to indicate which answer they have chosen.

    For example, one of the questions in column B says, On a scale of 0-6,
    how painful is it for you to climb one flight of stairs?" The choices
    are, 0, column C, "not difficult," 1, column D, "minimally difficult,"
    etc. all the way up to 5, column H "unable to do." The end result is
    that column I figures out which column the "X" was placed and enters the
    numerical result.

    try:
    =MATCH("x",$C21:$H21,0)


    Regards
    Claus B.

    Hi Claus,

    That works fine. Thank you. Do you have any idea why Excel glitches on
    my formula?

    --
    David Farber
    Los Osos, CA

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David Farber@21:1/5 to Claus Busch on Sun May 23 10:05:35 2021
    On 5/23/2021 9:53 AM, Claus Busch wrote:
    Hi David,

    Am Sun, 23 May 2021 09:19:41 -0700 schrieb David Farber:

    That works fine. Thank you. Do you have any idea why Excel glitches on
    my formula?

    for me your formula works fine.


    Regards
    Claus B.

    Does it work for you (meaning Excel doesn't modify the formula in column
    "I") even if you enter an "X" in one cell, and then change your answer
    by cutting and pasting "X" into another cell? Maybe a later version of
    Excel has fixed the problem?

    Thanks for your reply.

    --
    David Farber
    Los Osos, CA

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sun May 23 18:53:10 2021
    Hi David,

    Am Sun, 23 May 2021 09:19:41 -0700 schrieb David Farber:

    That works fine. Thank you. Do you have any idea why Excel glitches on
    my formula?

    for me your formula works fine.


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sun May 23 19:25:08 2021
    Hi David,

    Am Sun, 23 May 2021 10:05:35 -0700 schrieb David Farber:

    Does it work for you (meaning Excel doesn't modify the formula in column
    "I") even if you enter an "X" in one cell, and then change your answer
    by cutting and pasting "X" into another cell? Maybe a later version of
    Excel has fixed the problem?

    when you cut and paste the "x" the references in the formula will be
    changed and the formula isn't working any more.
    You must delete and rewrite the "x".


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From dpb@21:1/5 to David Farber on Mon May 24 08:07:21 2021
    On 5/23/2021 12:05 PM, David Farber wrote:
    On 5/23/2021 9:53 AM, Claus Busch wrote:
    Hi David,

    Am Sun, 23 May 2021 09:19:41 -0700 schrieb David Farber:

    That works fine. Thank you. Do you have any idea why Excel glitches on
    my formula?

    for me your formula works fine.


    Regards
    Claus B.

    Does it work for you (meaning Excel doesn't modify the formula in column
    "I") even if you enter an "X" in one cell, and then change your answer
    by cutting and pasting "X" into another cell? Maybe a later version of
    Excel has fixed the problem?

    That's by design; it's typically the desired result to have the formula
    adapt to the data; your case is an exception but there's no way in Excel
    to change the behavior.

    I've learned the hard way, too, in some cases in which I also did not
    want the behavior. It also follows with sort() in which reordering the
    data can cause formulas to change their targets as well, sometimes with disastrous results.

    --

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David Farber@21:1/5 to dpb on Tue May 25 11:12:05 2021
    On 5/24/2021 6:07 AM, dpb wrote:
    On 5/23/2021 12:05 PM, David Farber wrote:
    On 5/23/2021 9:53 AM, Claus Busch wrote:
    Hi David,

    Am Sun, 23 May 2021 09:19:41 -0700 schrieb David Farber:

    That works fine. Thank you. Do you have any idea why Excel glitches on >>>> my formula?

    for me your formula works fine.


    Regards
    Claus B.

    Does it work for you (meaning Excel doesn't modify the formula in
    column "I") even if you enter an "X" in one cell, and then change your
    answer by cutting and pasting "X" into another cell? Maybe a later
    version of Excel has fixed the problem?

    That's by design; it's typically the desired result to have the formula adapt to the data; your case is an exception but there's no way in Excel
    to change the behavior.

    I've learned the hard way, too, in some cases in which I also did not
    want the behavior.  It also follows with sort() in which reordering the data can cause formulas to change their targets as well, sometimes with disastrous results.

    --


    As I pointed out earlier, OpenOffice's Calc does not exhibit this
    behavior. I'm still curious as to when does this data mismatch occur
    which triggers the #REF! For example if a cell is blank, there is no
    error. If I put anything in the cell, whether it be a number or text,
    there is no error. Now if I use the "Cut" operation, that would leave
    the cell blank again and that certainly was OK back when the formula was created. So why is it a problem now?

    Thanks for your reply.

    --
    David Farber
    Los Osos, CA

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)