Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
not reconizing cell reference
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?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
not reconizing cell reference
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 "Spidey" wrote: 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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
How do I anchor a cell reference? | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Cell Reference Math | Excel Worksheet Functions |