Reference Cell in custom format????

[lil_ern63]

I am trying to reference another cell in the custom format area. how it is seet up currently, I have a column (A) that will have the quantity, column (B) has the unit of the quantity, and column (C) has the unit price, followed by column (D) that will have the total cost of the object (i.e. column A * C = D)

I am trying to have column C set up so all I have to do is type the unit price but the custom format of the cell will change the value to include the label that is in the column next to it (B).......currently for examples sake, I am trying to set the unit price to 50, and the unit of the quantity will be tons. ideally the result after simply imputting 50 into the cell it will display $50/ton.

I could get it to display this simply for this case, by changing the custom format of the cell, but since the units will change from ton to sf. or lf....etc I am looking for a more permanent solution.......Please help!

[Dave Peterson]

I don't think you'll get your custom formatting to work that way.

But you could use an event macro that looks for changes in column B and formats
column D accordingly:

If you want to try, right click on the worksheet tab that should have this
behavior. Select view code and paste this into the code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range
Dim myStr As String

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Target, Me.Range("b:b"), Me.UsedRange)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub
Else
For Each myCell In myRng.Cells
If myCell.Value = "" Then
myStr = "General"
Else
myStr = "$0.00""/" & myCell.Value & """"
End If
myCell.Offset(0, 2).NumberFormat = myStr
Next myCell
End If

End Sub

And then back to excel to test it out.

lil_ern63 wrote:
>
> I am trying to reference another cell in the custom format area. how it
> is seet up currently, I have a column (A) that will have the quantity,
> column (B) has the unit of the quantity, and column (C) has the unit
> price, followed by column (D) that will have the total cost of the
> object (i.e. column A * C = D)
>
> I am trying to have column C set up so all I have to do is type the
> unit price but the custom format of the cell will change the value to
> include the label that is in the column next to it (B).......currently
> for examples sake, I am trying to set the unit price to 50, and the
> unit of the quantity will be tons. ideally the result after simply
> imputting 50 into the cell it will display $50/ton.
>
> I could get it to display this simply for this case, by changing the
> custom format of the cell, but since the units will change from ton to
> sf. or lf....etc I am looking for a more permanent
> solution.......Please help!
>
> --
> lil_ern63
> ------------------------------------------------------------------------
> lil_ern63's Profile: http://www.excelforum.com/member.php...o&userid=23219
> View this thread: http://www.excelforum.com/showthread...hreadid=400974

--

Dave Peterson

[lil_ern63]

I appreciate the comment, but I am still unable to get output of the cell to work, once again thank you for the try however.

[Dave Peterson]

What did you try?

lil_ern63 wrote:
>
> I appreciate the comment, but I am still unable to get output of the
> cell to work, once again thank you for the try however.
>
> --
> lil_ern63
> ------------------------------------------------------------------------
> lil_ern63's Profile: http://www.excelforum.com/member.php...o&userid=23219
> View this thread: http://www.excelforum.com/showthread...hreadid=400974

--

Dave Peterson