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

    From David Farber@21:1/5 to All on Sat May 22 11:52:58 2021
    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.

    This is the formula I concocted for column "I"


    I know it's not foolproof (against multiple entries in the same row) but
    it does seem to work. The problem I've encountered happens if I decide
    to change one of my answers from any of the columns, and then paste it
    into another column. In other words, suppose I put an "X" in the,
    "minimally difficult" column and want to change it to the "not
    difficult," column. I use a copy and paste operation. It seems pretty straightforward, right? However the formula in the column I that figures
    out where the "X" is gets modified to:


    If I open the same spreadsheet in OpenOffice Calc, this problem does not
    exist. Is there some option in Excel that I can enable/disable to
    prevent this from happening?

    Thanks for your reply.
    David Farber
    Los Osos, CA

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