SOLVED

Cell Entry Required before advancing to next cell entry field

Brass Contributor

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.  

7 Replies

@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.

 

@Riny_van_Eekelen
I want to thank you very much for your prompt response to my post.
As far as the merged cells not being able to be merged, that isn't an issue, and I already have a work-around.
I downloaded the file and tried entering information on the second line. When entering the date DV came back with an error saying the entry doesn't match the data validation restrictions. I need for the date value to be entered and if that value is entered then entry to the next field is allowed, but if there is no entry to the next field then an error message appears.
I hope I'm explaining what is needed correctly.

@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.

I hadn't.
What I am trying to achieve is this (for example):
If a date is entered in b15, then Excel will allow for an entry to c15, and so on down the row.
If a date is entered in b15, but no entry is entered in c15, then an error message appears requiring entry to c15, (if an entry is entered in c15, but not in d15, then an error msg requiring entry to d15)...and then so on down the row.
I hope that makes sense.

The way the sheet is now, I am able to bypass entry to M15 and N15 but not able to enter in row 16 because these two cells aren't completed. That isn't going to accomplish my objective. I want to alert the user that when a date is entered then all other fields in the row are required forcing the user to make those entries.
We have a very basic log now and our branch staff aren't completing these logs in full as required by the government and the auditors. I am trying to make the log force the user into completing the necessary fields which will help us be in compliance with the two agencies.

best response confirmed by Michael1105 (Brass Contributor)
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.


I believe your solution is the best for what is needed.
Not clearing the row is what caused the confusion.
I truly appreciate you taking time out of your day to assist me.
Thank you and enjoy the rest of your day.

@Michael1105 You're welcome. Glad we got this sorted.

1 best response

Accepted Solutions
best response confirmed by Michael1105 (Brass Contributor)
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.

View solution in original post