Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Question Unanswered: Need to have a if statement return the cells formatting

    I need for an IF Statement to return the called upon cell's formatting. For example, IF(A1=B1,C1,""). In the example C1 is rounded to the thousands. However, when value of C1 is returned by the statement it is in general format and has lost rounding.
    I am not very familiar with VBA and I am sure there is a way to do this using VBA but I am wondering if there is a way to do it just in the spread sheet. If it can't be done in the spreadsheet can someone get me started with it using VBA. Any help would be greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by jtull79
    I need for an IF Statement to return the called upon cell's formatting. For example, IF(A1=B1,C1,""). In the example C1 is rounded to the thousands. However, when value of C1 is returned by the statement it is in general format and has lost rounding.
    I am not very familiar with VBA and I am sure there is a way to do this using VBA but I am wondering if there is a way to do it just in the spread sheet. If it can't be done in the spreadsheet can someone get me started with it using VBA. Any help would be greatly appreciated.
    Hi

    I do not see why the IF does not return the rounded value. How is the value of C1 calculated ?

    However, not withstanding the above, you could try this
    =IF(A1=B1,ROUND(C1,-3),"")

    MTB

  3. #3
    Join Date
    Jan 2009
    Posts
    5
    Hi MTB,

    Thanks for the reply. C1 would be equal to 123456789 formatted as a number with no decimals and uses the 1000 separator. It appears as123,456,789. When the IF statement returns C1 it is returned as 123456789. I tried your soultion of =IF(A1=B1,ROUND(C1,-3),"") but it doesn't come back with the 1000 separator.

    The example that was given in my post is a simplified verison of what I am actuall doing. This is for a financal report. It uses a drop down box for the user to pick the scaling (0, 1, 1000, 1000000). Depending on what the select, the formatting would be different. The report has an auto format feature but I have to get the formatted value in the cell that causes the rest of the report to format in that scale. I hope that make sense. May not be pertainet to the issue but wanted to give a little back ground.

    If you can think of a potenitally easier way to do that, I am definitely open for suggestion. Thanks again for your time.

    JTull

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I am now very confused (not difficult!).

    I believe that

    a) If cell C1 contains a number with the CELL formated to display 123456789 as 123,456,789 then the cell with =IF(A1=B1,C1,"") in it will need the same formatting applying to it.

    b) If cell C1 has a formatted string in it (ie. the cell contains/returns a string literal "123,456,789") then the formula cell will return the string literal as is.

    Not sure what else I can say without more info.


    MTB

  5. #5
    Join Date
    Jan 2009
    Posts
    5
    Hi MTB,

    I have included a sample worksheet that has pretty much everything that I am trying to do. Maybe this will help you understand the issue. I think the my trying to simplify the problem for explanation purposes may have made things unclear.

    I appreciate all of your attention to this. If you need any further info, just let me know. Thanks.
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Unfortunatly IT policy will not permit down loads (which, as you can imagine, is a a little frustating), so am a bit stuck. If I have the time at the weekend I will down load at home and have a look. Unless someone else has the answer first ??


    MTB

  7. #7
    Join Date
    Jan 2009
    Posts
    5

    Solved with VBA

    MTB,

    I appreciate all of the help but I was able to solve the problem using VBA Code I found around the internet. It works quite well and is completely automated. See code below if anyone is interested. Again, I do appreciate the time MTB!

    JTull



    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Intersect(Target, Range("Scale_Factor")) Is Nothing Then
       Exit Sub
      Else
      'The cell you are monitoring has changed!
      'Do whatever you need to do...
       Call ScaleNumbers
      End If
    End Sub
    Sub DidCellsChange()
       Dim KeyCells As String
       'Define which cells should trigger the KeyCellsChanged macro.
       KeyCells = "Scale_Factor"
    
       'If the Activecell is one of the key cells, call the
       'KeyCellsChanged macro.
       If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
       Is Nothing Then ScaleNumbers
    
    End Sub
    Sub ScaleNumbers()
        Select Case Range("Scale_Factor").Value
            Case Is = "1"
                Range("G51").NumberFormat = "#,##0"
            Case Is = "1000"
                Range("G51").NumberFormat = "#,##0,"
            Case Is = "1000000"
                Range("G51").NumberFormat = "#,##0,,"
            Case Else
                Range("G51").NumberFormat = "#,##0.00"
        End Select
    End Sub

Posting Permissions

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