Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 9th 09, 09:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 2
Default 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.

  #2   Report Post  
Old October 10th 09, 01:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default 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

To learn more about UDFs, see:

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to stop cells with no data entered, from showing the formulas Dave Excel Discussion (Misc queries) 4 December 4th 08 01:36 PM
Excel 2007 is not recalculating formulas when new data is entered FlowerTechie Excel Discussion (Misc queries) 3 January 12th 08 05:10 AM
Auto calculate formulas when new data is entered Cam1234 Excel Discussion (Misc queries) 6 February 15th 07 05:30 PM
automtically change ranges in formulas when new data is entered JRoyer95 Excel Worksheet Functions 5 January 6th 06 06:14 PM
Can Excel recognize when data is entered and apply formulas? cwool4512 Excel Worksheet Functions 2 July 7th 05 07:58 PM


All times are GMT +1. The time now is 12:27 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017