General discussion

Locked

create err message/locks cell till fixed

By kspugh ·
I have a 'difference' column on an Excel spreadsheet that subtracts Col M from Col S. If the entry is balanced and correct, the answer is Zero. If there is an amt other than zero, I want an error message telling the user to correct the entry, which must be done in order to move on.
Thanks in advance!

This conversation is currently closed to new comments.

15 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by sgt_shultz In reply to create err message/locks ...

don't have excel in front of me at the moment but i think part of what you desire is available thru format/cell. you might be able to program a custom display for when a numeric value is less than zero. it is already checking that so it can make those cells turn red and display a minus sign, see? i think i remember some neat stuff you could do in there...then you could cheat really bad somehow on the freezing thing maybe write close sheet macro that checks to make sure columns total zero otherwise loops back and user has to figure out problem which should be obvious depending on how many rows they are looking at, see?
as far as freezing the cell, i am thinking, thru hat, you would have take control of the data entry process. with a form. users wouldn't enter directly into cells anymore, your program would present form to get their entry and test entry and feedback to user and when it got good entry put it in cell, see? write some code maybe. first learn how to make form and display prompt. easy. then learn how to put value from form in test field, uh cell. ditto easy. then learn how to test value in field for your constraints. little more fun. if negative then wipe cell and redisplay input form 'oops, try again'. or whatever. there is lots of help in Excel and you can record macros for and examine that code.

Collapse -

by kspugh In reply to

Poster rated this answer. This spreadsheet is 32 columns wide, too much data for a form. If a macro is the answer, can someone tell me how that would work to check for balances in a certain column before closing? I am visualizing recording a macro, and can't see how that part would work out.

Collapse -

by DKlippert In reply to create err message/locks ...

You can use Format>Conditional Formatting to change the cells color if it is not equal to zero.
Or
You can use Data>Validation
Choose Custom under allow and have the formula:
(Assuming T is the "difference" column)
=T3=0
create your own Error message

Collapse -

by kspugh In reply to

Poster rated this answer. The users of this spreadsheet do not look at the difference column to notice an error message or actual amt. of difference. I have used both of the above methods. What I need is a pop up message in the middle of the screen that stops progress until the problem is fixed. . I thought what you suggested would work too, but it didn't. Thanks much to both of you who replied!

Collapse -

by DKlippert In reply to create err message/locks ...

See Mark Rowlinson's site:
http://tinyurl.com/5894m

Data validation, One of Excel's most underestimated tools...
In addition to this article there are many examples of using data validation contained in the Data Validation Examples workbook.

Collapse -

by kspugh In reply to

Poster rated this answer. This is my problem with Data Validation. My 'difference' column is a sum of 3 cells & the total from that formula actually determines whether the entries are in/out of balance. There is no actual data entry into the cell that needs to be evaluated. So how would I successfully use Data Validation in this instance? If I choose custom, what would the formula be? I have to sum 3 cells to start with and then somehow formulate a pop up message to stop input if the cell total is something other than zero. Thanks for your help

Collapse -

by DKlippert In reply to create err message/locks ...

Highlight the entry cells and then create a Data Validation formula:

Choose Custom under Allow and have the formula:
(Assuming T is the "difference" column)

=T3=0

The formula will automatically adjust for every cell you have selected

Collapse -

by DKlippert In reply to

Yes, there are two ='s

Collapse -

by kspugh In reply to

Poster rated this answer. It worked perfectly! No wonder you are THE GURU! Your answer is very acceptable but I have to ask one more question. How do I copy this formula down for 300 rows? Surely I don't have to do it row by row?

Collapse -

by kspugh In reply to create err message/locks ...

I answered my own final question about copying the formula down. I used paste special Validation. Thank you once again!

Back to Software Forum
15 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums