Force entry in one cell based on value of another cell

[Kebbon]

How might I force a user to enter a future effective date if they make an
entry in another cell for future price?
Thanks,
Kebbon

 

[Harlan Grove]

How might I force a user to enter a future effective date if they make an
entry in another cell for future price?

The more annoying, less effective way involves using event handlers, several
of them in this case: Change, Calculate, Deactivate and SelectionChange.
Each of these would make the required entry (date) the active cell if the
corresponding cell (price) contained anything.

The other way is to use additional terms in formulas. If both date and price
would be numbers, then change every existing formula that normally returns
numeric values to

=(original_formula)/(COUNTA(date_entry)=COUNTA(price_entry))

to make all these cells evaluate to #DIV/0! if one or the other BUT NOT BOTH
contained an entry; and change every formula that normally returns text
values to

=(original_formula)&LEFT("",(COUNTA(date_entry)=COUNTA(price_entry))-1)

to make all these cells evaluate to #VALUE! if one or the other BUT NOT BOTH
contained an entry.

If the macro-based solution is sufficiently annoying, your users will
disable macros, which is why macro-based enforcement is often if not usually
hopeless. OTOH, ensuring garbage out when fed garbage in has, at least in my
experience, proven to do a much better job of motivating users to make
complete and valid entries.

 

[Gord Dibben]

Difficult to "force" a user to enter data without using some code that prevents
closing/saving the workbook if user has failed to enter the data.

Can become quite involved with contingency plans if user opens the workbook with
macros disabled.

If the future effective date is based upon some specific data entered in the
other cell you could place a formula in the second cell that returns a date.

Or you could use Conditional Formatting.

Color the cell red or somesuch.

The cell will remain red until the date is entered.

Not a "force", just a visual reminder.


Gord Dibben MS Excel MVP

 

[kebbon]

Thanks to Gord and Harlan for their guidance. I think I will probably
go with conditional formatting for the visual cue.
Cheers,
Kebbon