+ Reply to Thread
Results 1 to 5 of 5

FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED

  1. #1
    Tomkat743
    Guest

    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. #2
    Dave Peterson
    Guest

    Re: 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. #3
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    >

    =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. #4
    Tomkat743
    Guest

    Re: 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. #5
    Dave Peterson
    Guest

    Re: 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 here:
    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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1