Cell Number Format includeing other Cell Value

  • Thread starter Thread starter daniel_of_vienna
  • Start date Start date
D

daniel_of_vienna

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
 
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
 
How about just use a third cell:

=a2 & " " & a1












--

Dave Peterson- Zitierten Text ausblenden -

- Zitierten Text anzeigen -
Click to expand...

Dear Dave,

thank you for answering that quickly. I see, using a 3rd cell is my
only option. :(((
BR,
Daniel
 
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








- Zitierten Text anzeigen -
Click to expand...

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
 
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.
Click to expand...

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
 
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
 
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
 
Thanks Dave.......vigilant as ever.

I appreciate the subtle but important edits.


Gord
 
Back
Top