Excel cell formatting

  • Thread starter Thread starter Don Niall
  • Start date Start date
D

Don Niall

Hi,

Is it possible to formal a particular group(s) of cells
such that all numeric entries are always expressed as
negative e.g an entry of 150 will be reflected in the cell
as -150.

Thx,

Don-
 
Hi Don!

You can use:

Format > Cells > Custom Format
Type the string -#

But remember that this only "looks" negative. It's stored as a
positive.

Also, if you type in -150 you'll get --150 unless you use the format
string

-#;+#

Here, the positive entries get formatted with -150 and negative with
+150.

It'll confuse most users and I'd look for better approaches to secure
your objective.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Don

As Norman points out, it is not possible to "format" the cells to be negative,
only show negative.

You can change them after the fact by placing -1 in a cell. Copy>Paste
Special>Multiply to your selected range of cells.

You can change them as you enter tham if you use Event code in the worksheet.

You can use a helper column with =A1*-1(drag/copy down column) and when done
entering in column A you can Copy>paste Special>Values on helper column then
delete original column.

Gord Dibben Excel MVP
 
Gord, Norman ...
Many thanks for the assistance .....

Could you briefly describe what you meant by '....You can
change them as you enter them if you use Event code in the
worksheet ...'?
If that doesn't throw up a workable option I am inclined
to go with your last idea: insert 'data entry only' column
B into the worksheet; format relevant column cells in
format -#, introduce a formula into adjacent column A
cells with =B1*-1 etc. Now anything that is entered into
column B will be prefixed with a negative sign, while
formula in column A will ensure it is accomodated as a
negative amount in further calculations etc ....

Don-
-----Original Message-----
Don

As Norman points out, it is not possible to "format" the cells to be negative,
only show negative.

You can change them after the fact by placing -1 in a cell. Copy>Paste
Special>Multiply to your selected range of cells.

You can change them as you enter tham if you use Event code in the worksheet.

You can use a helper column with =A1*-1(drag/copy down column) and when done
entering in column A you can Copy>paste Special>Values on helper column then
delete original column.

Gord Dibben Excel MVP
Click to expand...
 
Hi Don!

Can we first decide what you want to do as neither Gord nor I believed
that you really wanted to format positives as negatives.

Do you just want to convert positive inputs to negatives?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
news:[email protected]...
 
Yes - exactly. For select cell entries I want them to be
reflected, and treated, as negative entries. So, assume
that the user will always input a positive number ....

Don
 
Hi Don!

Try:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column = 1 Then
For Each cell In Target.Cells
cell.Value = Abs(cell.Value) * -1
Next cell
End If
End Sub

This code goes in the Sheet Module for the data entry sheet and covers
entry in column A only.

But it will slow things down.

I'd be more inclined to allow user freedom of entry and then use a
formula on that cell:

=IF(-ABS(A1)=0,"",-ABS(A1))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman, thanks for the response .... I just need a
clarification on the formula you suggested at the very
end. The assumption here is that the results of the
formula interrogation are posted to a NEW cell? For
example, the formula results cannot be posted to the same
cell occupied by the original data .... I may be stating
the obvious here but just want to make sure there isn't a
way of (sort of) embedding formulas into data entry
cells ....?

Don-
 
Hi Don!

I suppose that you could regard the Worksheet_Change event handler as
a form of embedding a formula in a cell. When an entry is made in a
Target cell, the event handler kicks in.

The suggested formula approach involves the formula being placed in a
cell immediately to the right of the data entry cell.

=IF(-ABS(A1)=0,"",-ABS(A1))

Whilst A1 is empty the formula will evaluate to 0 and it will return a
blank. As soon as a numeric entry is made -ABS(A1) will ensure a
negative result. But these two returns are both returns to the
adjacent cell. A formula or function can only return a value. There's
nothing that you can put in an empty cell that can act upon a
subsequent entry into that cell. You can pre-format a data entry but
that only governs how the cell contents will look; it won't affect
what is stored. Similarly, you can use Data validation to control what
can be put in a cell but it won't change it when it is input.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top