# Thread: Count if not a formula? (Excel 2000)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Count if not a formula? (Excel 2000)

<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. ## 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
•