Feb 23 2023 10:21 AM
Hello my most valued asset, Excel Community,
I am forever grateful to all of you who have assisted me in the hurdles I've had to cross with Excel.
I am reaching out to you again with hopes that someone will be again be able to help me solve a dilema.
I have been asked to create a log for our branch offices that is required by the Federal Government when a monetary instrument is purchased in cash over $3000. Management is asking that the fields be required before the user advances to the next cell, making each cell in the log mandatory for the line of entry.
I thought Data Validation was the answer, however, it doesn't seem to be. I also tried Googling this and the answers are VBA coding but seems to only cover the worksheet prompting an error message upon the closing of the worksheet.
I am still thinking Data Validation is the way to go here, however, I'm not too sure how to enter the arguments.
I've attached a file with notes for explanation and understanding.
I hope one of you most valued experts can again assist.
Feb 23 2023 10:42 PM
@Michael1105 See if the attached files does what you need. Note that this rather simplistic solution doesn't allow cells in columns G and H to be merged. If you must have them, you need to change it a bit.
Furthermore, you can't have two DV rules in the same cell, so columns J and O can be entered from the lists, even when the previous cells are not filled. But you can't continue on the next row until all cells on the previous row are filled in. Note that I also added an entry number in column A.
The DV rule applied to the green cells is like this:
=AND(COUNTA($B14:$O14)=14,COUNTA($A15:A15)=COLUMN()-1)
meaning that all 14 cells of the row above and all cells to the left of the current cell on the same row must be filled.
Feb 24 2023 06:01 AM
Feb 24 2023 06:10 AM
@Michael1105 But did you enter something in M15 and N15? I deliberately left them blank so that you would not be allowed to enter anything on row 16.
Feb 24 2023 06:41 AM
Feb 24 2023 07:04 AM
Solution@Michael1105 you have lost me. With the file I attached earlier, clear everything from B15 to O15 to get a clean starting point.
Enter something in M15. You can't. Enter a date in B15 - OK. Next cell you can enter something is C15. Try any other cell on row 15 and you can't. The only entries you can do are from the drop downs in J and O as explained earlier. And if not all cells in B15:O15 are filled, you can't enter anything in row 16.
If that's not working for you, I can't help you any further.
Feb 24 2023 07:22 AM
Feb 24 2023 07:26 AM
@Michael1105 You're welcome. Glad we got this sorted.