Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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" =IF(NOT(ISBLANK(C21)),0,IF(NOT(ISBLANK(D21)),1,IF( NOT(ISBLANK(E21)),2,IF(NOT(ISBLANK(F21)),3,IF(NOT( ISBLANK(G21)),4,IF(NOT(ISBLANK(H21)),5,"Missing entry")))))) 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(NOT(ISBLANK(#REF!)),0,IF(NOT(ISBLANK(C22)),1,I F(NOT(ISBLANK(E22)),2,IF(NOT(ISBLANK(F22)),3,IF(NO T(ISBLANK(G22)),4,IF(NOT(ISBLANK(H22)),5,"Missing entry")))))) 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
formula to place real time in a cell when data entered in an adjacent cell | Excel Programming | |||
formula to lookup data in cell NEXT to another cell | Excel Worksheet Functions | |||
Formula to populate data in a cell based on another cell's color | Excel Discussion (Misc queries) | |||
how to extract data from a cell in a formula in another cell | Excel Worksheet Functions | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel |