not reconizing cell reference

[Guest]

I've been struggling with this for a while...

The following formula works, and returns the needed answer; however, i would
like to link the "1123" to cell G5, so I can copy down the formula and have
it calculate for the different building codes. My problem is that when I
link the "1123" to the cell, it returns a -0- everytime. In the second
array, I have referenced cell Q$1, which works fine.

I have tried formating G5 as general, text, number. I have recalculated the
spreadsheet. I have named the cell, then tried to reference that.

I am fresh out of ideas.

=SUMPRODUCT(--('3Q06 Occupancy.xls'!Building_code_Column="1123"),--('[3Q06
Occupancy.xls]Occupancy'!$G$4:$G$159=Q$1)*('3Q06 Occupancy.xls'!HC_Column))

Help Please??

 

[Guest]

As far as I can see the formula should work unless the value in cell G5 is a
number instead of text. If that is the case then changing the format to text
will not change it to a text value unless you also reenter the value
afterwards. Alternatively, you could just enter '1123 and the leading
apostrophe will indicate to Excel that the 1123 is a text string.

If that's the problem it sounds as if you will also need to change a whole
column of numbers to text. You can quickly do this by inserting a column next
to the numeric values and copying a formula like =TEXT(G5,"0000") from row 5
on down to convert the values to text, then you can copy these text values
back over the initial column using the copy / paste special option.

Hope that helps.

TK