Summing from multiple wksts - incomprehensible #VALUE error

[Sydney]

Hello! I am an experienced user of Excel who is being quickly driven
to madness today by an incomprehensible error I've never see before.

I have a spreadsheet with quality data by quarter for several
facilities. Each facility has its own worksheet. My manager would like
to see the data by facility for each quarter, necessitating four new
worksheets. It seems like this should be an easy task.

For my example, let's say I have worksheets FAC1, FAC2 and FAC3. Each
one has 10 variables in rows (column A, rows 5-15) with both an "n"
and a "%" column for each of four quarters (columns C & D for Q1, E &
F for Q2, etc). The first two variables define the denominators for
the other variables and therefore have no % figure, so the cells are
merged. I have now added a new worksheet, Q1. (Q1 is a copy of FAC1
with the labels changed and data deleted to save time formatting.) It
has 10 variables in rows (column A, rows 5-15) with both an "n" and
"%" column for each of the three facilites (columns C & D are for
Facility 1, E & F for Facility 2, etc).

SO... In worksheet FAC1, C5-D5 are merged cells with a value "100," E5-
F5 are merged cells with a value "110," and so on. In worksheet FAC2,
C5-D5 are merged cells with a value "50." In worksheet FAC3, C5-D5 are
merged cells with a value "36." In worksheet Q1, I want merged cells
C5-D5 to display the value "100," E5-F5 to display the value "50" and
G5-H5 to display the value "36."

I entered the formula [='FAC1'!C55] in Q1 C5-D5 and it happily
displayed the value "100." But when I entered the formula [='FAC2'!
C55] in Q1 E5-F5, it returned a #VALUE error and said "formula omits
adjacent cells." When I allowed it to "fix" the error, it adjusted the
formula to [='FAC2'!C5-F5] and displayed the value from FAC2 merged
cells E5-F5.

In the error checking options menu, I turned off "formula omits cells
in region," "inconsistant formula in region" and "number stored as
text." The content of the cell was no longer flagged, but it still
said "#VALUE." I deleted the formula and tried again - same result:
#VALUE. But now the error message reads "a value used in the formula
is of the wrong data type."

I then changed all the cells with numbers to the "number" category in
the cell formatting menu. No change. I deleted the formulas and re-
entered them. No change.

I then changed all the cells with numbers to the "general" category in
the cell formatting menu. No change. I deleted the formulas and re-
entered them. No change.

I thought it might be the fact that the cells were merged, so I tried
it with rows 7 and 8 - (i.e. Q1 F7 = [='FAC2'!D7]). #VALUE.

I don't know what to do and this is a terrible way to start one's
morning. Can anyone explain what I'm doing wrong? I have summarized
multiple worksheets this way before and have never had this kind of
trouble. I do not want to have copy and paste this data each quarter
by hand, but that's what it's looking like right now.

Thank you in advance for any and all help!!

-Sydney

 

[Pecoflyer]

Errrr, quite a bit to read !
Maybe posting a sample of your data will help understand easier?

 

[Sydney]

Errrr, quite a bit to read !
Maybe posting a sample of your data will help understand easier?

Sorry! I thought I had posted some of the data, but in prose.

Worksheet: FAC1

A B C D
E F G H I J
3 Variable Benchmark Q1
Q2 Q3 Q4
4 % n
% n % n % n
5 100
110 107 113
6 90
95 94 97
7 10% 10 11%
12 9% 10 10% 11


Worksheet: FAC2

A B C D
E F G H I J
3 Variable Benchmark Q1
Q2 Q3 Q4
4 % n
% n % n % n
5 50
52 47 46
6 48
50 40 45
7 10% 5 11%
6 12% 5 10% 5


Worksheet: FAC3

A B C D
E F G H I J
3 Variable Benchmark Q1
Q2 Q3 Q4
4 % n
% n % n % n
5 36
30 40 32
6 35
30 35 31
7 9% 3 13%
4 5% 2 15% 5


Worksheet: Q1

A B C
D E F G
H
3 Variable Benchmark FAC1
FAC2 FAC3
4 %
n % n %
n
5 ='FAC1'!C55
='FAC2'!C55 ='FAC3'!C55
6 ='FAC1'!C66
='FAC2'!C66 ='FAC3'!C66
7 ='FAC1'!C7 ='FAC1'!D7 ='FAC1'!C7
='FAC1'!D7 ='FAC1'!C7 ='FAC1'!D7


I hope that helps!

Thank you!
Sydney

 

[Sydney]

Nuts. That doesn't look right at all.

 

[Pecoflyer]

Hi Sydney
my fingers getting somewaht older I try not to type too much
Could youplease add an xls file ( Go Advanced - Manage attachments
Th

(if the admin reads this one, I'm fried !

 

[Sydney]

Sorry - I don't see an "advanced" and my dummy data in a new sheet is
not doing the same thing. I really don't want to have to remake this
whole file from scratch, but I can't share the actual data. I was
really just hoping someone could tell me if this is a question of
settings or something Excel just can't do. But now that I've tried it
with a fresh workbook, I know that Excel CAN do it. I just don't know
why it won't do it in the original document.

 

[Sydney]

Sorry - I don't see an "advanced" and my dummy data in a new sheet is
not doing the same thing. I really don't want to have to remake this
whole file from scratch, but I can't share the actual data. I was
really just hoping someone could tell me if this is a question of
settings or something Excel just can't do. But now that I've tried it
with a fresh workbook, I know that Excel CAN do it. I just don't know
why it won't do it in the original document.

Sorry - by "this file" I mean the thing I have to do for work. I have
a dummy data file ready to go - I just don't see how to attach it.

 

[Sydney]

Nevermind - I've found a solution. ::sigh:: I'm sure there's a logical
explanation as to why single cells wouldn't copy any better than
merged cells, but I changed the formula on the merged cells to
[='FAC1'!C5] instead of [='FAC1'!C55] and not only did those cells
reference the correct number without an error message, but suddenly
the other, single cell references below started working too. Just
another reminder of how much there is in the world that I just don't
understand.

 

[John C]

Word of advice, lose the merged cells

 

[Sydney]

Thanks - that seems to be the solution.

I just wish Excel was consistent. Or maybe I don't. Maybe I benefit
from it's quirks and oddities more than I know.