***Official*** Excel Help Corner (1 Viewer)

[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)?

 

[Jayrok]

I have a folder with 3 excel files. This folder will be different for various users that they select from a dialog box (Application.FileDialog(msoFileDialogFolderPicker), etc. But the path will be established for the correct files to work with. I have code that opens each file and gathers various data from specific worksheets to build another excel file template. This works and has worked for years.

Now one of the files has special characters [p5] before the normal filename. [p5] Some Named Report.xls

What I'd like to do is insert a small piece of code that will find this file and rename it to just p5 Filename. Just want to remove the brackets ----> p5 Some Named Report.xls

Something like IF filename contains "[p5]" then Name filename to "p5"

Removing the brackets will return the whole function back to green pastures. After the file is renamed, then the code will resume to open the worksheets, and so forth through its routines.

Or would this need to be its own subroutine that is called. I'm hoping it can be done as part of the existing code just before looping through the worksheets for the data.

Thanks!

 

[titusbramble]

That's not really an Excel question but I guess you could do something in VBA to open then save a copy named what you want

 

[Jayrok]

That's not really an Excel question but I guess you could do something in VBA to open then save a copy named what you want

Yes my code is VBA in an excel workbook. I'm trying to get help with some VBA code that will rename an excel file in a remote folder and leave it in the same folder just without the prefix [p5]... just remove the brackets. Thanks

 

[Nick Vermeil]

Hey guys. Need help with a formula. I am trying to create a sheet that updates the cost of gold items based on the daily gold price. That I can do pretty easily, but I would like another column that calculates the cost based on a "last delivery date" which varies based on the item. So that delivery date would be in one column, but the calculation would be in a different column. I have found some formulas on line, but I have to enter the date in the formula foreach item.

Thoughts?

 

[bryhamm]

Assuming you have the most recent version of excel, what you want is to use the XLOOKUP formula

 

[tri-man 47]

Assuming you have the most recent version of excel, what you want is to use the XLOOKUP formula

And will it help in the cost calculation to utilize Excel's logic of having numbers underpinning each date? I.e., subtracting the last delivery date from the current date will yield a number ...the number of days, which again, might be part of your cost calculation.