How to enter a value in a cell but display a different value in the same cell?

[39N95W]

Excel 2000
Windows ME

My boss would like me to try to perform some spreadsheet trickery for his
monthly expense report.

Here is what he wants:

Suppose I type the value 100 in cell F3 (this is a Mileage value).

Immediately after typing in 100 in cell F3, I would then want to display the
following value in F3: 100 / 34.00

This would be a combination of the value typed in by the user along with
that value multiplied by .34, separated by a slash. It is also required
that I be able to sum the value on the right side of the slash.

I know the obvious solution to this is to use an additional column to handle
the dollar value of the mileage reimbursement, but for some reason this is
NOT an option.

So, can it be done? What worksheet events are we talking about here?
Any help greatly appreciated.

-gk-

 

[Frank Kabel]

Hi
though this could be done using the worksheet_change event (see below
for some example code for column F) you'll get problems summing the
values on the right side of the slash. This would require a user
defined function (IMHO).
So you really should consider using a separate cell (just curious: why
is this not an option?):

put the following code in your worksheet_change event. Note: The result
in your cell is a TEXT value and not a numeric value

----
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Intersect(Target, Me.Range("F:F")) Is Nothing Then Exit Sub
on error goto errhandler
If Target.Value <> "" and isnumeric(target.value) Then
application.enableevents = false
target.value = target.value & " / " &
format(target.value*0.34,"0.00")
End If
errhandler:
application.enableevents = True
End Sub

 

[Bob Phillips]

Here is one way to input the data.

Right-click on the sheet tab, select View Code from the menu, and paste this
code.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
With Target
.Value = Format(.Value, "0") & " / " & Format(.Value * 0.34,
"0")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Adding it is trfickier though.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Here is the sort of UDF Frank talks about


Function SumRight(rng As Range)
Dim amt
Dim cell As Range
Dim iPos As Long

For Each cell In rng
If cell.Value <> "" Then
iPos = InStr(1, cell.Value, " / ")
If iPos > 0 Then
amt = amt + CDbl(Mid(cell.Value, iPos + 3, 5))
End If
End If
Next cell
SumRight = amt

End Function


Use like
=SumRight(A1:A10)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

Do you work in the same office as "Dilbert", must be fun.
Creative use of Excel.
http://www.unitedmedia.com/comics/dilbert/dnrc/html/newsletter25.html

Put the value in it's own cell and use a concatenation to show
what your boss wants where he wants it.

If you try to use an event macro and retain the value as a number
you will have to use absurd formatting, which will greatly increase
the size of your workbook and could possibly make force you to
restore from a backup. (you do have backups, don't you?), or to
remove lots of garbage formats when you exceed a limit..