Count if not a formula? (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

Create these UDFs in a module:

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

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

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

and copy it down the column.

Steve

Reply | Quote

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

Reply | Quote

… 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

Reply | Quote

… 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

Reply | Quote

… 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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

How about this:

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

Use:

=CountFormulas(A7:P7)

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

How about this:

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

Use:

=CountFormulas(A7:P7)

Steve

Reply | Quote

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

Reply | Quote