LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default Formula in one cell changes when data in another cell is cut andpasted.

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to place real time in a cell when data entered in an adjacent cell JasonK[_3_] Excel Programming 10 February 8th 10 11:22 PM
formula to lookup data in cell NEXT to another cell TraderXL Excel Worksheet Functions 3 June 22nd 09 01:44 PM
Formula to populate data in a cell based on another cell's color Cassie Excel Discussion (Misc queries) 1 February 6th 09 04:12 PM
how to extract data from a cell in a formula in another cell vidhya Excel Worksheet Functions 1 October 17th 05 04:31 PM
inserting data from a row to a cell, when the row number is specified by a formula in a cell [email protected] New Users to Excel 2 January 6th 05 07:18 AM


All times are GMT +1. The time now is 10:23 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"