Need to have a if statement return the cells formatting

jtull79 · #1 (**permalink**) 01-27-09, 18:14

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.

MikeTheBike · #2 (**permalink**) 01-28-09, 08:28

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

jtull79 · #3 (**permalink**) 01-28-09, 18:33

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

MikeTheBike · #4 (**permalink**) 01-29-09, 08:12

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

jtull79 · #5 (**permalink**) 01-29-09, 11:26

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.

MikeTheBike · #6 (**permalink**) 01-30-09, 08:06

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

jtull79 · #7 (**permalink**) 01-31-09, 18:52

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

Internet Services

Web Development

Digital Marketing

Consumer Tech