Cell Number Format includeing other Cell Value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

daniel_of_vienna - 06 Apr 2008 19:33 GMT

Hello,

I needed to create some conditional number format - to say so...
My goal:

Cell A1 content is "m2"
as I Enter in cell A2: "10" -> the output to cell A2 should be "10
m2".

Cell A1: "Dollars" -> A2: 200 -> shown "200 Dollars"...
and so on....

Is that possible in Excel? (don't know VBA - yet :DD)

Any help is appreciated!
BR,
Daniel

Reply to this Message

Dave Peterson - 06 Apr 2008 19:50 GMT

How about just use a third cell:

=a2 & " " & a1

> Hello,
>
[quoted text clipped - 13 lines]
> BR,
> Daniel

Signature

Dave Peterson

Reply to this Message

daniel_of_vienna - 06 Apr 2008 20:40 GMT

> How about just use a third cell:
>
[quoted text clipped - 23 lines]
>
> - Zitierten Text anzeigen -

Dear Dave,

thank you for answering that quickly. I see, using a 3rd cell is my
only option. :(((
BR,
Daniel

Reply to this Message

Gord Dibben - 06 Apr 2008 20:05 GMT

Rather than use 2 cells you could just custom format one cell.

Enter 10 in A1 then Custom Format to

#" m²" Use Alt + 0178 to enter the superscript ²

Enter 200 in B1 and Custom Format to

#,##0 "dollars"

Any other method would leave the resulting value as Text which could not be
calculated.

Gord Dibben MS Excel MVP

>Hello,
>
[quoted text clipped - 13 lines]
>BR,
>Daniel

Reply to this Message

daniel_of_vienna - 06 Apr 2008 20:43 GMT

> Rather than use 2 cells you could just custom format one cell.
>
[quoted text clipped - 30 lines]
>
> - Zitierten Text anzeigen -

Dear Gord,

The "Unit" of the cell varies from line to line, so this manual
formating is not an option to me in this case.
I just wondered, why excel can't read out the value of a given cell
and attach this as a string to my number value. (Neither in VBA...?)
Thank you for the fast ansver as well.

BR,
Daniel

Reply to this Message

Gord Dibben - 06 Apr 2008 21:43 GMT

>The "Unit" of the cell varies from line to line, so this manual
>formating is not an option to me in this case.
>I just wondered, why excel can't read out the value of a given cell
>and attach this as a string to my number value. (Neither in VBA...?)
>Thank you for the fast ansver as well.

If you want to use VBA event code you could get what you want.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "B1:B10"
On Error GoTo stoppit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
With Target
.Value = .Value & .Offset(0, -1).Value
End With
Next
End If
stoppit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Adjust the range of B1:B10 to suit. Alt + q to return to the Excel window.

Enter your "units" in column A then enter numbers in column B

Gord

Reply to this Message

Dave Peterson - 06 Apr 2008 23:50 GMT

Just a couple of minor modifications...

I'd check the cells that are in the intersection and use "With Cell" instead of
"With Target". I'd also just ignore any error and continue processing the other
cells.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "B1:B10"

Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell
.Value = .Value & " " & .Offset(0, -1).Value
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True

End Sub

> >The "Unit" of the cell varies from line to line, so this manual
> >formating is not an option to me in this case.
[quoted text clipped - 28 lines]
>
> Gord

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 07 Apr 2008 00:03 GMT

Another option would be to use Gord's idea, but instead of changing the value,
change the numberformat (kind of what you (the OP) asked for originally:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Dim myStr As String
Dim iCtr As Long
Dim myFormatStr As String
Const WS_RANGE As String = "B1:B10"

Application.EnableEvents = False
'On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell
myStr = .Offset(0, -1).Value
myFormatStr = ""
For iCtr = 1 To Len(myStr)
myFormatStr = myFormatStr & "\" & Mid(myStr, iCtr, 1)
Next iCtr
.NumberFormat = "General" & " " & myFormatStr
End With
Next Cell
End If
On Error GoTo 0
Application.EnableEvents = True

End Sub

> Just a couple of minor modifications...
>
[quoted text clipped - 57 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 07 Apr 2008 00:55 GMT

Thanks Dave.......vigilant as ever.

I appreciate the subtle but important edits.

Gord

>Just a couple of minor modifications...
>
[quoted text clipped - 53 lines]
>>
>> Gord

Reply to this Message