Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Maddening reference error
Using Excel 2003 on an XP pro machine I created a 5 sheet workbook.
On (named) sheets 2-5 column A has a date, columns B and C has a number, column D is equal to C-B for each row. Row 1 of all four sheets is a text label. On sheet one I created a summary with five rows (row one is text labels only) Column A has a text label, B is: =SUMIF(~sheet~!C:C,"0",~sheet~!B:B) column C is =SUM(~sheet~!C:C) column D is =SUM(~sheet~!D:D) column E is (one example) =1-B3/ABS(C3) column F =AVERAGE(IF(~sheet~!B:B<0, ~sheet~!B:B,"")) 3 of the four rows in column F work. One column returns #VALUE! When I look at the step by step evaluation it says Reference: Summary!$F$4 Evaluation AVERAGE( "") For the life of me I can't figure out what is going wrong here. All other references to that sheet work, I've typed in the formula manually as well as copied from one of the working cells then changed the name of the referenced sheet. I'm out of ideas - any suggestions would be greatly appreciated. |
#2
|
|||
|
|||
Your formula:
=AVERAGE(IF(~sheet~!B:B<0, ~sheet~!B:B,"")) appears to be an array formula, which cannot accept full column references. Also, my Excel didn't like the ~sheet~ name, and put it in single quotes. Try array entering (using Ctrl-Shift-Enter) a formula like: =AVERAGE(IF('~sheet~'!B1:B100<0, '~sheet~'!B1:B100,"")) HTH, Bernie MS Excel MVP "Wowbagger" <none wrote in message ... Using Excel 2003 on an XP pro machine I created a 5 sheet workbook. On (named) sheets 2-5 column A has a date, columns B and C has a number, column D is equal to C-B for each row. Row 1 of all four sheets is a text label. On sheet one I created a summary with five rows (row one is text labels only) Column A has a text label, B is: =SUMIF(~sheet~!C:C,"0",~sheet~!B:B) column C is =SUM(~sheet~!C:C) column D is =SUM(~sheet~!D:D) column E is (one example) =1-B3/ABS(C3) column F =AVERAGE(IF(~sheet~!B:B<0, ~sheet~!B:B,"")) 3 of the four rows in column F work. One column returns #VALUE! When I look at the step by step evaluation it says Reference: Summary!$F$4 Evaluation AVERAGE( "") For the life of me I can't figure out what is going wrong here. All other references to that sheet work, I've typed in the formula manually as well as copied from one of the working cells then changed the name of the referenced sheet. I'm out of ideas - any suggestions would be greatly appreciated. |
#3
|
|||
|
|||
I was using "~sheet~" to represent the respective sheet names... kind of
like a wildcard, not the actual name. This particular sheet is called "Northeast" - but if I change the sheet name to anything else it still won't work. On my summary sheet this particular sheet is referenced in row 4 - column A is nothing but the text "Northeast", the formulas in each of the next columns are as follows: =SUMIF(Northeast!C:C,"0",Northeast!B:B) =SUM(Northeast!C:C) =SUM(Northeast!D:D) =1-B4/ABS(C4) =AVERAGE(IF(Northeast!B:B<0, Northeast!B:B,"")) The other rows have identical formula constructs with only the referenced sheet name changed. To further confuse me I did a little experimenting and discovered that the formula works in some rows but not in others. I copied the formula and pasted it into several other rows. Starting with row 6 this is how the sheet now appears: 56.16666667 #VALUE! 56.16666667 56.16666667 #VALUE! #VALUE! 56.16666667 56.16666667 56.16666667 56.16666667 56.16666667 #VALUE! Why do some rows work and some rows don't? "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Your formula: =AVERAGE(IF(~sheet~!B:B<0, ~sheet~!B:B,"")) appears to be an array formula, which cannot accept full column references. Also, my Excel didn't like the ~sheet~ name, and put it in single quotes. Try array entering (using Ctrl-Shift-Enter) a formula like: =AVERAGE(IF('~sheet~'!B1:B100<0, '~sheet~'!B1:B100,"")) HTH, Bernie MS Excel MVP "Wowbagger" <none wrote in message ... Using Excel 2003 on an XP pro machine I created a 5 sheet workbook. On (named) sheets 2-5 column A has a date, columns B and C has a number, column D is equal to C-B for each row. Row 1 of all four sheets is a text label. On sheet one I created a summary with five rows (row one is text labels only) Column A has a text label, B is: =SUMIF(~sheet~!C:C,"0",~sheet~!B:B) column C is =SUM(~sheet~!C:C) column D is =SUM(~sheet~!D:D) column E is (one example) =1-B3/ABS(C3) column F =AVERAGE(IF(~sheet~!B:B<0, ~sheet~!B:B,"")) 3 of the four rows in column F work. One column returns #VALUE! When I look at the step by step evaluation it says Reference: Summary!$F$4 Evaluation AVERAGE( "") For the life of me I can't figure out what is going wrong here. All other references to that sheet work, I've typed in the formula manually as well as copied from one of the working cells then changed the name of the referenced sheet. I'm out of ideas - any suggestions would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
ERROR | Excel Discussion (Misc queries) |