combine cell value# with written formulas and print in anoth

[CAPT.PT]

I am working on a Fire Dept. payroll...I have serveral worksheets and have to have a written statement(formulas) as to how I arrived at a certain figure. Dah--they don't trust computers at city hall!
Therefore I have to have a column for "how did you arrive at this figure"
so, I need a statement to appear in a cell the might end up saying:
11.2*23.50*4
that seems easy, but if the man didn't get any overtime then there is nothing in the cell. If he did, the total overtime figure is already calculated from another worksheet and put in lets say cell S6. so I need to have something like:
If S6>0,then,11.2*23.50*whatever figure is in cell P6.
cell P6 is the number of days he got overtime
and ofcourse will constanly be differant.
Any suggestions?
This message was edited by CAPT.PT on 2002-03-05 02:40

 

[Ian Mac]

I'd suggest not doing it like you ask for.

I'd right a seperate procedure/policy for this out lining what the formulas do, using generic formulas i.e.
Section 21: that + that + that if not that, is the hours worked if such and such.

I'd use word (I do use word!) for thsi kind of thing.

any company should accept this as it would need to be fully version controlled and therefore comply with ISO 9000:2000.

If your people don't accept a worldwide Quality system then...........

If you need any pointers E-mail me, I do this sort of thing for a living.

 

[Mudface]

This might not be exactly what you're asking for, but if your data is on the same sheet, then clicking into a result cell, then selecting Tools-Auditing-Show Precedents, will show the cells that went into making your result. You could make a macro to run from a command button on the sheet and do this with one click e.g.

Private Sub CommandButton1_Click()

Selection.ShowPrecedents

End Sub

Hope that helps.

 

[CAPT.PT]

LOGICAL DAVE WHERE ARE YOU?
Guys, I know, you what your talking about...I don't....I have to keep things simple...I have a formula that writes most of what I want it to say, I only have to know how to print....what, lets say q4..which is the number of times a man worked cell says....I don't even need it to do the math...just write the number thats in that cell. So that it will print in a certain cell....11.2*23.50*4 4 is the number I need to print( of course it could be any number up to 24).
I am sorry...when you BRAINS say, all you have to do is What* this*that=whatever....has no effect....I need it like 10hr.s overtime*hr. rate*days worked = the total!
10*23.50*4=940 then I can understand!
I know, Dah, what is he doing here...I run into burning buildings..when you guys are running out....PLEASE, JUST MAKE IT SIMPLE!
I THANK YOU VERY MUCH! CAPT. PT

 

[Paul B]

This will list formulas , cell address, and valves in a new worksheet, send a copy with your report and "they" can see the formulas and valves.
I think I got it off this board but don’t know who to give the credit to.

Sub ListFormulas()

Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer

' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas or the sheet is protected."
Exit Sub
End If

' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

' Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell
' Adjust column widths
FormulaSheet.Columns("A:C").AutoFit
Application.StatusBar = False
End Sub