Software
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!
by sgt_shultz
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.
by kspugh
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.
by dklippert
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
by kspugh
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!
by dklippert
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.
by kspugh
by dklippert
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
by kspugh
by kspugh
by kspugh
by dklippert
In the Name box (to the left of the formula bar), enter the range of input cells. Let's say your first entry would be in S2 and the last one in U300 - Enter S2:U300 and hit the Enter key.
The whole input range is now selected.
Go to Data>Data Validation and enter the Custom formula. If V2 is the total cell in the first row, enter =V2=0
The Validation gnome will automatically adjust it for all of the selected cells.
by kspugh
Poster rated this answer. Your answer is much appreciated!
by kspugh
This discussion is closed to new replies.
Sort by Trending