Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: combine cell value# with written formulas and print in anoth

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    "Have a good time......all the time"
    Ian Mac

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •