Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.misc




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 05. Columns CH 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 06, 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 
#2
Posted to microsoft.public.excel.misc




Formula in one cell changes when data in another cell is cut and pasted.
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 05. Columns CH 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 06, 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 
#3
Posted to microsoft.public.excel.misc




Formula in one cell changes when data in another cell is cut andpasted.
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 05. Columns CH 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 06, 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 
#4
Posted to microsoft.public.excel.misc




Formula in one cell changes when data in another cell is cut and pasted.
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 
#5
Posted to microsoft.public.excel.misc




Formula in one cell changes when data in another cell is cut andpasted.
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 
#6
Posted to microsoft.public.excel.misc




Formula in one cell changes when data in another cell is cut and pasted.
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 
#7
Posted to microsoft.public.excel.misc




Formula in one cell changes when data in another cell is cut andpasted.
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.  
#8
Posted to microsoft.public.excel.misc




Formula in one cell changes when data in another cell is cut andpasted.
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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
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 