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'!C5:D5] in Q1 C5-D5 and it happily
displayed the value "100." But when I entered the formula [='FAC2'!
C5:D5] 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