combine cell value# with written formulas and print in anoth

CAPT.PT

New Member
Joined
Mar 2, 2002
Messages
32
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top