formula for monthly data (9.0/2000)

[garcich1]

In a workbook I have worksheet 1 with columns containing the alloted budget (in hours) per task for the duration of the program, the % complete of that budget through weekending 3/12/04 (we status weekly), % complete through weekending 3/19/04, cummulative hours spent from the begining of the program (Aug.) to 3/12 or 3/19 (depending if the person's updated status this week yet or not), and the current hours spend for weekending 3/19 . On worksheet 2, I have columns containing the % complete history for wknd 3/12, 3/5, and 2/27 (we just started this wkly statusing deal). A manager wants to see the hours per task spent cummulativly for the month, not just entire program thus far. I'm trying to build a formula that takes ((ws2 column 3/5 (% completes) - ws2 column 2/27 (% completes) )and multiply that difference by ws1 column alloted budet) to get the March cummulative thru weekending 3/5. I also want it to return nothing for tasks with blank % completes instead of #value. Then for weekending 3/12, I have to again (((subtract % complete 3/12 from % complete 3/5 )and multiply that difference by the total budget )and add it to the March cum. thru 3/5 value) I came up with. However when I do the formula, some tasks it seems to work, and others don't look that great... can anyone help me? And I'm having trouble returning a blank instead of #value.

[HansV]

The seemingly empty cells, such as Sheet2!A7:C7, are not all really empty. This can happen if the data have been imported from another application. You can use the following macro to clear the empty-seeming cells:

Sub ClearEmptyCells()
Dim aCell As Range
On Error GoTo TheEnd
Application.ScreenUpdating = False
For Each aCell In Selection
If Trim$(aCell.Value) = "" Then aCell.ClearContents
Next
TheEnd:
Application.ScreenUpdating = True
End Sub

Select the range (A1:C213) before running this macro.

Put the following formula in F3:

=IF(ISBLANK(B3),"",(B3-C3)*Sheet1!A3)

and this one in G3:

=IF(ISBLANK(A3),"",(A3-C3)*Sheet1!A3)

Then select F3:G3 and fill down to row 213.

[sdckapr]

A non macro approach to clearing the cells is to highlight the region A3:C213
Edit - goto - <special>
Choose "Constants" and then uncheck all the checks but "Text"
<ok>
[All the "text" in selection is selected
Press <delete>

Steve