Why can't I paste an Array command from one file into another file

[Mighty Magpie]

I readily admit to having only limited knowledge of Excel and would welcome
any advice on the following.

I have created a workbook which contains data covering a period of three
months. This file is named "Sept Oct and Nov"

One sheet called "breakdown" consists of a grid which relies on how the data
on the DATA sheet is interpreted. What I mean is that in the cells on the
grid I have an array command which consists of the following formula

=SUM(('Data'!$AD$5:$AD$15000="Tea")*('Data'!$G$5:$G$15000="Bob")*'Data'!$Z$5
:$Z$15000)

(It might look wrong above but it works properly on the Excel sheet)

As far as I understand it, the first instruction looks in row AD on the
sheet named "DATA" to try and find instances of the word "TEA".

It then looks to row G to look for "BOB" It then looks to row Z for an
indication that it "matters" (basically, on that DATA sheet I have entered a
"1" for when "YES" applies, otherwise the cell will be left blank)

I have similar formulae in other cells, looking for other things from the
"DATA" sheet. This is probably very basic, but it works fine for me.




Having arrived at the method of doing this, I then tried to use the same
idea for data which relates to 3 other months (June, July and August).

I simply took my original workbook and deleted all data entries from the
sheet named "DATA" - I did not alter anything on the sheet named "BREAKDOWN"
I then used "SAVE AS" and gave the file the new name - June July and August

I had expected that when the data for June, July and August had been entered
into the right places on the new "DATA" sheet, the array from the
"BREAKDOWN" sheet would work in the same way as it did on my original sheet.
However, whereas the original version returned the totals in the correct
places, this attempt only manges to produce the result "#VALUE!"

All the data on the new (copied) version is of an identical type to that in
the original version so my (very limited) logic led me to expect it to work
satisfactorily but it does not.

What have i missed, or is it not possible to do this for some reason.

I am using Windows XP and Excel 2002


Many thanks

 

[Debra Dalgleish]

After you enter or edit the array formula, press Ctrl+Shift+Enter as you
exit the cell.

Or perhaps one of the cells in column Z isn't really blank -- it might
have a space character. You could try SUMPRODUCT instead:


=SUMPRODUCT((Data!$AD$5:$AD$15000="Tea")*(Data!$G$5:$G$15000="Bob")*(Data!$Z$5:$Z$15000=1))

It's not an array formula, so you can just press Enter after entering
the formula.

 

[Mighty Magpie]

I shall try your suggested option (Sumproduct) I was already aware of the
need to CTRL+SHIFT etc for the array but thanks anyway.

I did think that all the cells in row Z were empty but I shal try your
suggestion.

Again, many thanks for taking the time to respond

cheers

 

[Mighty Magpie]

Debra,

Your SUMPRODUCT suggestion worked a treat - that's brilliant thank you.

Having changed all the incorrect cells on the copied sheet, I would like to
do the same on the original sheet because that is the one I will use as my
template for 2004 figures.

Is there a quick and easy way to replace the 'bad formulae' with the 'good'
one

(I have just re-typed the SUMPRODUCT formula into 336 cells and will need to
do the same in my other spreadsheet)

Once again, many thanks

 

[Debra Dalgleish]

Retyping 336 formulas sounds like way too much work. Perhaps you could
use cell references, instead of text strings in the formula, to reduce
the work. For example, if you have a table with row and column headings:

A B C
1 Tea Milk
2 Bob 6 1
3 Fred 1 2

Enter the following formula in cell B2:
=SUMPRODUCT((Data!$AD$5:$AD$15000=B$1)*(Data!$G$5:$G$15000=$A2)*(Data!$Z$5:$Z$15000=1))
and copy across to column C, then down to row 3.