Is there a way to get two different number formats for same cell?

[aedctalk]

I ask because i'm pulling two different sets of data into the same cells.

I have a dropdown that has two sheet names in M120

then i reference it in the cell below using

=IF(ISERR(INDIRECT($M$120&"!AE149")),,INDIRECT($M$120&"!AE149"))

Thus i'm pulling attendance from one sheet that i just want 6,100 format
and percapita from another sheet that i want two decimals 3.50 both appear in same cell lets say A1 .. depending upon whats selected in M120


Is there any way to change the format depending on what value is in M120 .. or by any other method?

Thanks so much :P

 

[ChrisOswald]

Have you taken a look at the TEXT() function?

(I removed the iserr() bit from below to make the formula a little more obvious, plus if the values from a dropdown you shouldn't need to worry too much about a bad value)

=IF($M120 = "Sheet3",TEXT(INDIRECT($M$120&"!AE149"),"#,###"),TEXT(INDIRECT($M$120&"!AE149"),"#.00"))

 

[Fazza]

custom format. this gives the idea
[<100]0.00;#,##0

HTH

 

[RoryA]

If you are using 2007 or later then you can apply number formats in conditional formatting, so you can base them on the values of a different cell to the one you are formatting.

 

[mikerickson]

Putting this in the sheet's code module is a general solution.

Private Sub Worksheet_Calculate()
    On Error Goto ErrorOut

    With Sheet1.Range("M120")
    
        Select Case LCase(CStr(.Value))
            Case "decimal"
                .Dependents.NumberFormat = "0.00"
            Case "thousand"
                .Dependents.NumberFormat = "#,##0"
            Case Else
                .Dependents.NumberFormat = "general"
        End Select
        
    End With

ErrorOut:
On Error Goto 0
End Sub

 

[aedctalk]

Thank you everyone for the help!

All are great!

Problem Solved. Thanks again!