S
Steve
howdie all.
Now, I'll forewarn you-- this is an odd situation that I'd never seen
before, and I've been using Excel for a long time now. So, please bear with
me, and if you need to read this through a couple of times, or more to get
it, I'm trying to be clear enough, and concise enough to keep you from
getting too confused.
a colleague of mine opened a workbook that had some worksheet functions in
it, as well as some named fields.
As she explained to me what was happening, it struck me as odd-- not
something I'd seen happen before.
There were numerous named fields. I found them using the name mgr, and
deleted them all. As none of the worksheet functions were associated with
fields, none of the equations failed.
I'd also checked to ensure that there were no macros in the workbook before
doing anything else to it.
There was one non-descript link to a cell that I'd never seen before.
Initially there was an if() equation in the cell which tested an adjacent
cell. If there was a value in the adjacent cell, that value was placed as a
linked cell-- something we use regularly for testing conditions.
However, since the adjacent cell had no value, the contents of the primary
weree empty.
This is where it gets odd.
In deleting the contents (clicking the delete key) of the primary cell, we
received an error message stating that the cell's contents were merged, and
could not be removed. However, in looking at the worksheet, it then went to
another worksheet, and somehow-- not by equation, or field name-- was linked
to two other cells that had absolutely nothing to do with the primary cells.
Then, to bypass that error, I activated the primary cell, highlighted the
contents, delted them manually, and moved on. My colleague then showed me the
secondary worksheet. The contents of the cell that the initial link was to,
was empty.
I then re-entered the cell's value.
We then went back over to the primary worksheet, and the cell whose contents
we'd entered on the secondary sheet, was in the cell that had the if()
equation in it, and I'd manually deleted.
As I think about what I'm saying, I will try the trace dependents tool.
Does any one have any experience where after the equations, and fields were
removed, the contents of a completely unrelated cell were affected by
deleting the contents of another cell?
I.e.,
Sheet1, a24 has =if(g24<>"",g24,"")
g24 has a sumproduct equation linking to Sheet2.
Sheet2, a36 has a 12 digit number in it. xxx-xxx-xxx
i.e.,
=sumproduct((sheet2!$e$1:$e$40=sheet1!$a10)*(sheet2!$f$1:$f$40=sheet1!$c10)*(sheet2!$b$1:$b$40))
WE've been using this general form of sumproduct for 3 years now, and never
had this type of error arise before.
Now, I'll forewarn you-- this is an odd situation that I'd never seen
before, and I've been using Excel for a long time now. So, please bear with
me, and if you need to read this through a couple of times, or more to get
it, I'm trying to be clear enough, and concise enough to keep you from
getting too confused.
a colleague of mine opened a workbook that had some worksheet functions in
it, as well as some named fields.
As she explained to me what was happening, it struck me as odd-- not
something I'd seen happen before.
There were numerous named fields. I found them using the name mgr, and
deleted them all. As none of the worksheet functions were associated with
fields, none of the equations failed.
I'd also checked to ensure that there were no macros in the workbook before
doing anything else to it.
There was one non-descript link to a cell that I'd never seen before.
Initially there was an if() equation in the cell which tested an adjacent
cell. If there was a value in the adjacent cell, that value was placed as a
linked cell-- something we use regularly for testing conditions.
However, since the adjacent cell had no value, the contents of the primary
weree empty.
This is where it gets odd.
In deleting the contents (clicking the delete key) of the primary cell, we
received an error message stating that the cell's contents were merged, and
could not be removed. However, in looking at the worksheet, it then went to
another worksheet, and somehow-- not by equation, or field name-- was linked
to two other cells that had absolutely nothing to do with the primary cells.
Then, to bypass that error, I activated the primary cell, highlighted the
contents, delted them manually, and moved on. My colleague then showed me the
secondary worksheet. The contents of the cell that the initial link was to,
was empty.
I then re-entered the cell's value.
We then went back over to the primary worksheet, and the cell whose contents
we'd entered on the secondary sheet, was in the cell that had the if()
equation in it, and I'd manually deleted.
As I think about what I'm saying, I will try the trace dependents tool.
Does any one have any experience where after the equations, and fields were
removed, the contents of a completely unrelated cell were affected by
deleting the contents of another cell?
I.e.,
Sheet1, a24 has =if(g24<>"",g24,"")
g24 has a sumproduct equation linking to Sheet2.
Sheet2, a36 has a 12 digit number in it. xxx-xxx-xxx
i.e.,
=sumproduct((sheet2!$e$1:$e$40=sheet1!$a10)*(sheet2!$f$1:$f$40=sheet1!$c10)*(sheet2!$b$1:$b$40))
WE've been using this general form of sumproduct for 3 years now, and never
had this type of error arise before.