Input cell information

[RB Camp]

I hope that I can explain this correctly. I have a cell “A†and cell “Bâ€.
If cell “B†is empty I want cell “A†to be able to have someone type a date
into it. If cell “B†already has a date, I want cell “A†to show an X in it
so they know that cell “A†is not available to be used. I need to do this
without having the formula located in cell “A†as it gets over written when
someone types in the date. Is there anyway to accomplish this?

 

[TomHinkle]

First off, keep an open mind..

Problem seems to be you want to use A, unless cell B has a value. Add
column C. In column C put a formula using col B if it exists, col A if not.

I KNOW you probably really want to do it in 2 columns.. There's probably a
way, BUT in thinking of maintaining it, if you can do without code, it's
better.

Also, try to avoid worksheets that are used for data entry AND reporting.
Make the data entry sheet simple, and utilitarian. Then make another sheet
that reports the data with all the fancy formatting.

 

[TomHinkle]

okay,
I understand the dilema.

First off, if the user will be way too confused with one extra column, I
think you probably have too much going on on that worksheet..
however.

I think you can give interface feedback to the user, BUT maybe not in the
form of an X.

In column C, (or A, for that matter) use conditional formatting... If col C
= col A, turn the cell (or just the border) a color, say green. If col C =
col B, turn it a different color (say Green)

If that's still not acceptable and you absolutely have to show the user a
big black X, you'll have to write code..

go to the worksheet's code module.. in the change event write code. the
parameter passed in [Target] represents what has changed. You'll have to do
some if's to make sure they only changed one cell and it's one in question

....

if Target.column > 0 and target.column < 4 then

' your code here

end if


This will work, BUT will 'fire' on every change to the sheet, and have to be
maintained.. (if you move the input columns, this macro will have to be
updated)

I'd go with the conditional formatting. Turn col A Bright red if there's no
value in B, and white otherwise

 

[RB Camp]

Thank you for your responce.

Unfortunately if I add the cell “C†it would get confusing for the person
entering the information as they would have to compare it with cell “A†and
there are many cells on this page already. If there is a way to put the
formula in cell “C†that says if Cell “B†is not empty then put an X in cell
“A†it would fix my problems. I can not figure out how to this part.