Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED
=COUNTIF('[4DLWEDNESDAY.XLS]9501'!$I2:$U2,$AJ$3)-COUNTIF('[4DLWEDNESDAY.XLS]9501'!$W2:$AA2,$AJ$3)
I WOULD USE AN ARRAY FORMULA OF =COUNT(IF(( BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND WILL NOT TAKE AN ARRAY FORMULA. $AJ$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST BILLING CODES) |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED
Maybe you could use =sumproduct()
=sumproduct(--('[4DLWEDNESDAY.XLS]9501'!$I2:$U2=$aj$3)) - sumproduct(--('[4DLWEDNESDAY.XLS]9501'!$W2:$AA2=$aj$3)) (Untested) Tomkat743 wrote: =COUNTIF('[4DLWEDNESDAY.XLS]9501'!$I2:$U2,$AJ$3)-COUNTIF('[4DLWEDNESDAY.XLS]9501'!$W2:$AA2,$AJ$3) I WOULD USE AN ARRAY FORMULA OF =COUNT(IF(( BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND WILL NOT TAKE AN ARRAY FORMULA. $AJ$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST BILLING CODES) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED
Find a way to get rid of the merged cells, they are not worth the problems
that they create. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tomkat743" wrote in message ... =COUNTIF('[4DLWEDNESDAY.XLS]9501'!$I2:$U2,$AJ$3)-COUNTIF('[4DLWEDNESDAY.XLS] 9501'!$W2:$AA2,$AJ$3) I WOULD USE AN ARRAY FORMULA OF =COUNT(IF(( BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND WILL NOT TAKE AN ARRAY FORMULA. $AJ$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST BILLING CODES) |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED
YOU ARE MY HERO!!!!!!!!!!!! THAT WAS PERFECT.... I WOULD LIKE TO KNOW WHY
BUT AT THIS POINT I'M JUST HAPPY I FOUND SOMETHING THAT WORKED "Dave Peterson" wrote: Maybe you could use =sumproduct() =sumproduct(--('[4DLWEDNESDAY.XLS]9501'!$I2:$U2=$aj$3)) - sumproduct(--('[4DLWEDNESDAY.XLS]9501'!$W2:$AA2=$aj$3)) (Untested) Tomkat743 wrote: =COUNTIF('[4DLWEDNESDAY.XLS]9501'!$I2:$U2,$AJ$3)-COUNTIF('[4DLWEDNESDAY.XLS]9501'!$W2:$AA2,$AJ$3) I WOULD USE AN ARRAY FORMULA OF =COUNT(IF(( BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND WILL NOT TAKE AN ARRAY FORMULA. $AJ$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST BILLING CODES) -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED
There are some functions that don't work with closed workbooks:
=indirect(), =countif() and =sumif() for example. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html And just like any array formula, you can't use the whole column. Tomkat743 wrote: YOU ARE MY HERO!!!!!!!!!!!! THAT WAS PERFECT.... I WOULD LIKE TO KNOW WHY BUT AT THIS POINT I'M JUST HAPPY I FOUND SOMETHING THAT WORKED "Dave Peterson" wrote: Maybe you could use =sumproduct() =sumproduct(--('[4DLWEDNESDAY.XLS]9501'!$I2:$U2=$aj$3)) - sumproduct(--('[4DLWEDNESDAY.XLS]9501'!$W2:$AA2=$aj$3)) (Untested) Tomkat743 wrote: =COUNTIF('[4DLWEDNESDAY.XLS]9501'!$I2:$U2,$AJ$3)-COUNTIF('[4DLWEDNESDAY.XLS]9501'!$W2:$AA2,$AJ$3) I WOULD USE AN ARRAY FORMULA OF =COUNT(IF(( BUT THE CELLS I'M TRYING TO ENTER THE FORMULA INTO ARE MERGED CELLS AND WILL NOT TAKE AN ARRAY FORMULA. $AJ$3 COULD ALSO BE TEXT IE: "2M" "3C" "2A" (NOT CELL REFERENCES JUST BILLING CODES) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif returns #VALUE! when linked workbook is closed | Excel Worksheet Functions | |||
FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED | Excel Discussion (Misc queries) | |||
SUMIF Returns a #VALUE error when external source is closed | Excel Worksheet Functions | |||
SUMIF Returns a #VALUE error when external source is closed | Excel Discussion (Misc queries) | |||
SUMIF Returns a #VALUE error when external source is closed | Excel Worksheet Functions |