chatguy
How is column J populated? Keyboard entry by the user?, Formula?, vba code? Something else?
Could somebody be pasting a series of values into the column at once?
Hi Peter,
It's usually done by hand (keyboard entry by user). It basically consists of the letters P & F (pass or fail). Basically column B says how many P/F's are expected, but if column B=5 on one row, it could be possible that there are 4 P's in column J (since they didn't get around to checking the last/5th item yet).
In this case, it would be nice to alert them with a popup (just one) as soon as the incomplete field is entered. It could also be possible that someone enters 6 P's instead of 5, which makes things even more confusing.
...actually, I oversimplified the case above, there are really more letters than P and F (total of 7) and conditional formatting is used depending on the contents of the field (ex: one color if one part fails, if multiple fails, if all fails, if all passes, etc) so using conditional formatting in this case would be pretty confusing to add another color. Since there are columns in use all around it, coloring a nearby column would be pretty tough as well, where it's become a whole colorful sheet through years of changes, etc. Since this has become a bigger problem with the spreadsheet, unfortunately I'd need to resort to popup dialogs to notify the user.
In the worst case scenario, if a macro cannot be done, I've tried a modification of the formula you've provided and seems to work well (btw, thank you so much!!)...I'd just need to scroll all the way to the right to see the results after I notice that something looks off. So if the numbers don't match up, at least I can now find the problem quickly, if I do that. So although it works, it would be nice if it caught the user's attention as soon as the error is made.
The one other issue I've noticed when using a formula instead of a global macro, is that the filesize went up by another 3.5MB (pretty big!) due to its repetition all throughout the workbook (this formula is currently repeated on about 30 pages!). So I was hoping that a macro that constantly checks each page as soon as a change it made would bring down the filesize as well--although I do understand that it would probably use more CPU each time a change is made. Hard drive space is probably more valuable than CPU usage, however, which would favor a macro, if this is possible(?).
Thanks
CG