Remember Me? October 9th 09, 09:31 PM posted to microsoft.public.excel.worksheet.functions
 Brand external usenet poster First recorded activity by ExcelBanter: Oct 2009 Posts: 2 Counting # of Formulas in a column with formulas and entered data

My spread sheet consists of approximately 550 rows and growing. Calculations
are calculated as data is entered by hand from left to right. For instance,
D5:M550 consists of of entered data, formulas that have been calculated, and
formulas that are equivalent to 0 due to not being calculated. I am looking
to be able to count the formulas that have automatically calculated themselfs
in a single column with out counting the values that have replace/deleted the
formulas as the values were entered by hand. This spread sheet calculates
due dates for documents as a previous document has been completed.

Formula= "=IF(D261/1/2000,D26+14,0)" Calculated date is in cell E26 is
1/14/2009 if the day which at some point was entered by hand in cell D26 is
1/1/2000, for instance 1/1/2009. Cell E27 is a hand entered date due to the

document being completed. I need a formula that will count all of the
"calculated dates" in Column E, and not the calculated dates, plus the
entered dates. If more clarification is needed, please let me know. October 10th 09, 01:01 PM posted to microsoft.public.excel.worksheet.functions
 Gary''s Student external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 11,058 Counting # of Formulas in a column with formulas and entered data

This small UDF will count all the formulas in a range:

Function countf(r As Range) As Long
Dim rr As Range
countf = 0
For Each rr In r
If rr.HasFormula Then
countf = countf + 1
End If
Next
End Function

UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function =countf(H1:H100)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx

--
Gary''s Student - gsnu200907

"Brand" wrote:

My spread sheet consists of approximately 550 rows and growing. Calculations
are calculated as data is entered by hand from left to right. For instance,
D5:M550 consists of of entered data, formulas that have been calculated, and
formulas that are equivalent to 0 due to not being calculated. I am looking
to be able to count the formulas that have automatically calculated themselfs
in a single column with out counting the values that have replace/deleted the
formulas as the values were entered by hand. This spread sheet calculates
due dates for documents as a previous document has been completed.

Formula= "=IF(D261/1/2000,D26+14,0)" Calculated date is in cell E26 is
1/14/2009 if the day which at some point was entered by hand in cell D26 is
1/1/2000, for instance 1/1/2009. Cell E27 is a hand entered date due to the

document being completed. I need a formula that will count all of the
"calculated dates" in Column E, and not the calculated dates, plus the
entered dates. If more clarification is needed, please let me know.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Dave Excel Discussion (Misc queries) 4 December 4th 08 01:36 PM FlowerTechie Excel Discussion (Misc queries) 3 January 12th 08 05:10 AM Cam1234 Excel Discussion (Misc queries) 6 February 15th 07 05:30 PM JRoyer95 Excel Worksheet Functions 5 January 6th 06 06:14 PM cwool4512 Excel Worksheet Functions 2 July 7th 05 07:58 PM

All times are GMT +1. The time now is 03:08 PM. Copyright ©2004-2019 ExcelBanter.