***Official*** Excel Help Corner (3 Viewers)

[Pigskin Fanatic]

i'm stumped. google search says no one has the answer or it's not possible. ExcelGuys are smarter than google, right?

i have a cell in a worksheet that has data validation so it behaves as a simple drop down selection box. The worksheet is protected, but the cells in question are not locked, so users can type in or copy and format as they please in these cells. When i copy the cell down to other lines, all values and formats follow, but not the data validation. If I unprotect the cell and do the same, the data validation does copy down correctly.

is there a setting i'm missing to allow the data validation to also copy down to the new cells while the worksheet is protected?

(culdeus, a few months late, but that stupid function is called "flash fill")

I'm about a 4/10 when it comes to Excel, but I pasted your post into ChatGPT and got the following back. Does it help? ... I have no idea, as it's all foreign to me.

It seems like you're encountering a specific behavior of Excel where copying cells with data validation to other cells does not carry over the data validation rules when the worksheet is protected, even if the cells themselves are not locked. This can be somewhat limiting because it prevents the data validation from being applied to new cells via copying, which is a common task in Excel.

Unfortunately, there's no direct setting in Excel that allows data validation to be copied down to new cells while the worksheet is protected. Data validation rules are generally maintained when copying cells in an unprotected sheet, but protection constraints can interfere with this behavior.

Here are a few approaches you might consider to work around this issue:

1. Use a Macro to Copy Data Validation: If you're comfortable with VBA, you can write a macro that temporarily unprotects the sheet, copies the data validation, and then re-protects the sheet. This approach requires careful consideration of security and protection settings. Ensure macros are allowed to run by the users.
2. Pre-apply Data Validation to a Larger Range: Before protecting the sheet, apply the data validation to all the cells in the column or range where you expect data to be entered or copied. This way, even if the sheet is protected, the cells will already have the necessary validation applied.
3. Adjust Protection Settings: When you protect the worksheet, ensure that you check the option that allows users to edit objects or scenarios, depending on what Excel version you're using. Sometimes, tweaking these settings can allow for more flexibility while still keeping the sheet protected.
4. Manual Application of Data Validation for Users: Educate users on how to apply data validation themselves if they add new rows or cells that need it. This is more of a workaround than a solution and might not be feasible depending on the users' Excel proficiency.
5. Unprotect and Reprotect Through User Actions: Consider creating a simple interface (such as a button with a macro) that users can click to unprotect the sheet, perform their copying or data entry, and then reprotect the sheet. This would automate the steps and reduce the risk of leaving the sheet unprotected accidentally.

yes, this is it. not the answer i was hoping for, but at least i know it's simply not possible without the workaround answers here. Thanks @Mister CIA , i'll use one of those suggested answer. apprecate the list.

 

[massraider]

What stupid setting is it that it tries to guess what you want to fill down below a cell?

Like when you hold the cursor over the corner of the cell, till the cross shows, and then drag it down?

 

[culdeus]

What stupid setting is it that it tries to guess what you want to fill down below a cell?

Like when you hold the cursor over the corner of the cell, till the cross shows, and then drag it down?

No. It seems to want to basically use AI to be like oh I see you had Jan and Feb now in the next cell you want mar? I could screenshot this.

 

[dino259]

I am using a formula to determine the position of a certain character and using that as a number of characters in the formula MID. I would like to have this value max out at a value of 23. This is based on the size of the comparison field. Any suggestions to update my formula to use the calculated number but maxing it at 23?

=IFERROR(MID(G6,7,(SEARCH("4",G6)-9)),MID(G6,7,15)) sometimes the data in column G does not have a trailing number starting with the number 4.

 

[titusbramble]

Surely if (huge formula<24, huge formula, 23)?