Remember Me?

#1
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 21
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?

--
David Farber
Los Osos, CA
#2
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 3,872
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 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
#3
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 21
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 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
#4
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 3,872
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
#5
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 21
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?

--
David Farber
Los Osos, CA

#6
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 3,872
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
#7
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 109
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
 external usenet poster Posts: 21
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?

--
David Farber
Los Osos, CA

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post JasonK[_3_] Excel Programming 10 February 8th 10 11:22 PM TraderXL Excel Worksheet Functions 3 June 22nd 09 01:44 PM Cassie Excel Discussion (Misc queries) 1 February 6th 09 04:12 PM vidhya Excel Worksheet Functions 1 October 17th 05 04:31 PM [email protected] New Users to Excel 2 January 6th 05 07:18 AM

All times are GMT +1. The time now is 10:28 PM.