Prevent a range of cells to be entered unless one particular cell is filled (same row, diff column)

[atisyam]

Hello

I was wondering if it is possible to prevent data to be entered unless data is entered for one particular cell. These cells are in multiple columns but the same row. Best if it uses any excel functions available except for VBA due to the worksheet formatting. :/

The example is Columns A to F will be locked unless column G is entered. Then columns A to F will auto unlocked for the user to fill in information. If the user tries to enter anything from column A to F, there will be a popup that column G must be entered first.

Is this possible? Hope to get any advice or solutions. Thank youu

A	B	C	D	E	F	G
						Enter here first

<tbody>
</tbody>

 

[Eric W]

Select columns A:F (or A1:F100 if your range is smaller). From the Data tab click Data Validation. On the dialog box, choose Custom from the Allow drop-down. In the formula box, put

=COUNTA($G1)>0

You can then click the Error Alert tab and enter your custom error message.

That should do it, but be aware that Data Validation can be defeated. Someone can enter the data in column M, then copy the cell and paste it into column A.

 

[atisyam]

Thanks Eric, worked as what I intended.


Noted bout the copy paste thing.

 

[Eric W]

Glad to help.

 

[atisyam]

Hi sorry Eric. Spoke too soon.


The above formula worked well as per intention. However, when I have already input in the data for column G and tried to enter data for columns A & F, the error pop up box still persist. I could choose other type or message box alert instead of error but I honestly would still prefer the error alert :/


FYI column G is a drop-down list where the user chooses the option from the list. Just in case if because of this, the formula might be different?


Hope for anyone that can help. Thank youu.

 

[Eric W]

I'm sorry, I can't replicate your problem. I even added a drop-down in G, and it still works as intended. The only thought I have is that did you add or delete rows/columns after entering the DV formula? Or cut/paste cells from your range, or to your range? If you add/delete rows/columns, Excel will "helpfully" adapt the formulas for you. Cutting/pasting will remove the DV from the associated cells.

If that kind of thing is to be expected in your sheet, you could add an event handler. This would monitor your sheet, and if someone tries to add/change data to your range, it would check for some in G before it allows it. This would be immune from Excel's machinations. This would make your sheet macro-enabled though. Let me know if that's of interest.

 

[atisyam]

I'm sorry, I can't replicate your problem. I even added a drop-down in G, and it still works as intended. The only thought I have is that did you add or delete rows/columns after entering the DV formula? Or cut/paste cells from your range, or to your range? If you add/delete rows/columns, Excel will "helpfully" adapt the formulas for you. Cutting/pasting will remove the DV from the associated cells.

If that kind of thing is to be expected in your sheet, you could add an event handler. This would monitor your sheet, and if someone tries to add/change data to your range, it would check for some in G before it allows it. This would be immune from Excel's machinations. This would make your sheet macro-enabled though. Let me know if that's of interest.

Hi Eric. Sorry for the late reply.

Thanks for your suggestions. You're right about the cut/paste cells. That is why it didn't work. Now it's working perfectly. Thanks for your help!