Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count if not a formula? (Excel 2000)

    Hi all, Again, I'm stuck ...

    I have a row of "Visit Names"
    Then a row of "# of days between visits"
    Then a row of Dates. This row starts out with projected dates (formulas). The users fill in the actual dates. Now I need a way to tell the actual dates from the projected dates. And place the last actual date in the last column. Below is a sample. I have also attached an example. (it would be nice to conditionally format the cell to distinquish from actual and projected--but that's fluff for now)

    Screening----Day1---Week2---Week4--Week6--Week8--Week12-----------Week16-----------Last Real
    10--------------14-------14---------14--------14-------- 28--------28------------------28
    7/7--------------7/23----8/6--------8/22------9/19-----10/17----10/24(formula)--11/14(formula)---Week 8

    Help.
    Thanks, cat

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count if not a formula? (Excel 2000)

    Create these UDFs in a module:

    <pre>Function IsFormula(rng As Range) As Boolean
    IsFormula = rng.Cells(1).HasFormula
    End Function

    Function LastEnteredDate(rngDates As Range, rngLabels As Range)
    Dim x As Integer

    For x = rngDates.Columns.Count To 1 Step -1
    If Not (rngDates.Cells(1, x).HasFormula) Then
    LastEnteredDate = rngLabels.Cells(1, x)
    Exit Function
    End If
    Next
    End Function</pre>


    Select the range A7:P100 (or whatever) and format conditional formatting:
    Formula is:
    =isformula(A7)
    <format> [format as desired, pattern orange]
    <ok><ok>

    In Q7 enter:
    =LastEnteredDate(A7:P7,$A$5:$P$5)

    and copy it down the column.

    Steve

  3. #3
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if not a formula? (Excel 2000)

    Steve ... thanks a bunch for responding so quickly! (I'm on a tight schedule, so I'll go try it right now and let you know ...)
    --Cindy

  4. #4
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if not a formula? (Excel 2000)

    ... I don't have the function "ISFORMULA". I have a lot of other "IS STUFF", though. Is there an add-in I should select to get this function?
    Thanks,
    --cat

  5. #5
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if not a formula? (Excel 2000)

    ... please ignore my last post. ISFORMULA is what I will be adding as VB code (sorry, for my ignorance, I'm self-taught with macros)
    --cat

  6. #6
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if not a formula? (Excel 2000)

    Thanks! This works great for viewing which dates are actual and which ones are still formulas. In order for the rest of my application to work I need to have the count of the formulas (or not formulas) in order to perform some other calculation.

    I tried using this formula in the last cell of the row
    =countif(A7:Q7,isformula)

    but it doesn't work. Please, do you have any other ideas.
    --Cindy

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count if not a formula? (Excel 2000)

    How about this:

    <pre>Function CountFormulas(rng As Range) As Long
    Dim rCell As Range
    CountFormulas = 0
    For Each rCell In rng
    If rCell.HasFormula Then _
    CountFormulas = CountFormulas + 1
    Next
    End Function</pre>


    Use:
    <pre>=CountFormulas(A7:P7)</pre>


    Steve

  8. #8
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if not a formula? (Excel 2000)

    You are the Best, Steve !!! Now, I can proceed!
    Thanks,
    Cindy

Posting Permissions

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