assume the number I type is in the thousands

R

Raza

I have a spread sheet that is dealing with home prices. As you can guess,
I'm typing a lot of zeros.

Instead of typing 300,000 for a home price, is there anyway that I could
format a cell so I type 300 and the number pops up as $300,000?

Thanks.
 
T

Tom Ogilvy

Just be warned,
Setting fixed decimal to -3 will add 3 zeros to the end of any number you
type.

It can't be selectively applied.
 
R

Raza

Changing the fixed decimal to -3 worked but there are other value on the
spreadsheet that need to be less than 1,000 like when you subract $800 rent
from the mortage payment. Any other solutions?
 
T

Tom Ogilvy

that's probably a little too general. To qualify, it will be applied to any
number where you don't specify the decimal point.
 
R

Raza

The only problem with that method is that the number only appears in the
thousands and I need the value to be in the thousands to make calculation.s
 
A

Anne Troy

Only the macro solution I've suggested. Other than that, you COULD do
this...
Enter just the 300 part of all your values. When you're done typing them up,
type 1000 into a blank cell and copy it. Select all your values, and hit
Edit-->Paste Special-->Multiply, and this will multiply them all by 1000.
However, I think recording the macros would be the easiest solution for you.
************
Anne Troy
www.OfficeArticles.com
 
G

Gary''s Student

You could create a worksheet event change macro. The Macro could detect your
entry of data into a cell and automatically multiply by 1,000. It is much,
much easier to enter 300 into an un-used cell, like say D1, and use =1000*D1
where you want the full, big, number.
 
G

Gary''s Student

Here is some worksheet code that will auto-multiply by 1000 for entries in
cells A2 thru A10:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A2:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * 1000
Application.EnableEvents = True
End Sub
 
R

Raza

Your solution seems perfect. The only problem is that I don't know how to
add code to a worksheet! How do I do that?
 
D

Dave Peterson

Rightclick on the worksheet tab that should have this behavior and select "view
code".

Paste the code into that code window.

Then back to excel to test it out.
 
Top