format cell
 

I want to put a number, hidden, in a cell so taht all the user has to do is
enter an x rather than the number. THe hidden number becomes part of my
eventual sum in another column. I don't want to just enter the number for
accuracy reasons, I only want the "X" in the cells. Any ideas?

format cell
 

Exactly what you are asking for can't be done in Excel - there is
absolutely no provision in Excel that would allow two values to be
stored in the same cell - visible or not.

What you can do is have a column in which you can have the user enter
an x and then in another column have a formula that checks the first
cell for an entered x and if it finds one, evaluates to a number that
will become part of your eventual sum.

Make Col B the column in which an x is to be entered. If the column
which will be summed is Col C, the formula for Col C cells -
specifically cell C3 - would be:

=IF(B3="x",64,0)

assuming that the number you want to be eventually summed if cell B3
has an x is 64.

Note however, that while this is what you asked for - IF your user
enters X instead of x, this breaks. To take care of that, use

=IF(UPPER(B3)="X",64,0)

so the formula would work if the user enters x or X. But what if the
user types c or Z - keys close to the X key. You could set it up so
that ANY entry in B3 would include 64 in your evenual sum with the
following version

=IF(ISBLANK(B3),0,64)

note that since the ISBLANK function will return True if B3 is blank
(empty) the order of the two values of the IF have been swapped. You
could keep them in the same order by using the NOT function

=IF(NOT(ISBLANK(B3)),64,0)

but that requres an extra function evaluation and doesn't seem to make
the formula any clearer.

Hope this helps... :Bgr


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119464