• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation not saving .xlsx file - Excel 2013.

D

Delta337

New Member
Hi Team,

I have a spreadsheet where the names of driver stowing and unstowing trains needs to be entered into a cell. 1 driver name per cell. For neatness I have requested that all supervisors entering the names use the following format...SURNAME, 1st Name. That is the surname of the driver is entered 1st in uppercase, then thier 1st name in proper text.

I can use data validation with the following formula set as custom =OR(EXACT(D229,$D$5:$D$208)) which works fine for all respective data validated cells, D229 being the first cell to have a name entered and $D$5:$D$208 being the location of the names list.

To make things easier for my colleagues, I have placed the list as part of the spreadsheet so that the auto fill function works when the first few characters of the names are entered either in upper, lower or proper case, the auto fill detects this, highlights and fills the remainder of the cell based on it's best guess. No problem there.

I tested this by deliberately using lower case entries. The spreadsheet highlights it's best guess from the list exactly as listed. Enter to accept. The cell then changes to upper cases and lower cases per the list and all is good. The formula works correctly. Save spreadsheet.

Issue:
Except...when the spreadsheet is reopened by myself or another colleague, the data validation seems to have disappeared. Clicking on any of the data validated cells (D209 for example) I note that the "Apply these changes to all other cells with the same settings" is unticked. Reticking this highlights the other validation cells appropriately and the formula works again. Save spreadsheet and close. reopen spreadsheet and repeat issue.

Trouble shooting:
Searching for a solution through various forums, I note that there were compatibility issues with excel saving .xls and .xlsx and .xlsm files. This spreadsheet was designed a few weeks ago using excel 2013. It has no macros running. The only other issue that was possibly raised was the spreadsheet not liking frozen cells. Freezing or unfreezing the cells is not making any difference.

Work arounds:
I would prefer to use formulas as opposed to macros. I cannot use a vlookup because this involves helper cells and I cannot use helper cells or I would have done so already. In my travels for alternitaves (because initially I thought that excel did not like the custom data validation formula) I came across the following... =(AND(EXACT(C5, UPPER(C5)),ISTEXT)). This I placed a into one of the data validation cells after wiping all of the previous data validation. I then entered Bloggs, Joe into the cell and was rewarded with BLOGGS, JOE. Is there any way to modify, add to, concentate, adjust etc. the formula so that I have the cell automatically change it to BLOGGS, Joe irrespective of whatever whacky combination of characters is input?

Any assistance would be greatly appreciated.

Attached is a short version of the spreadsheet.

Thank you.
 

Attachments

  • Train Stowing-Unstowing Log Sheet.xlsx
    75.9 KB · Views: 15
Hi:

I suggest you to use searchable drop-downs, In your method if I understand correctly you are pulling the names based on the surname. what if two people have same surname?

Thanks
 
Some observations...
You do have 3 sets of names in columns D, M and R. And the data validation is set on these 3 columns only referring to 1 list of names (in $D$5:$D$208).

Concerning
"Apply these changes to all other cells with the same settings" is unticked"
Click to expand...
. I believe this to be standard behavior. You can select 1 cell, make changes to the data validation and then apply it for all other cells where you have used the same data validation rule before. Otherwise you need to select them all up front. Doubt the issue comes from here. When you click any of the cells in the range, the formula in data validation is still there, no?

Alternative suggestion: try to explain to user to use ALT + ARROW DOWN to view a "drop down" list. Now this only works if you do not have any interrupts in the range. In D420 it will not work f.e. Since the previous rows are empty. But the way your sheet is set-up, it seems to get the job done. Better, follow Nebu's advice and use data validation with Lists.
 
Nebu said:
Hi:

I suggest you to use searchable drop-downs, In your method if I understand correctly you are pulling the names based on the surname. what if two people have same surname?

Thanks
Click to expand...


Hi Nebu and G.

Thanks for your response.

I have not heard of searchable drop downs so I will study them tomorrow. These are not the "options" provided by the list data validation list are they?

To answer Nebu first...there are a few names in the list of the same surname. The auto fill just waits until the comma is past and then starts looking for the next different character to take a guess at. Eg SOLINAS, Marc and SOLINAS, Kim.

You are correct in your assumption that highlighting 1 cell and ticking the "Apply these changes to all other cells with the same settings" works for all cells in all the name columns. The problem is it does not stay this way once re-opened and to get the sheet to work again, I need to go to the data validation tab, open the validation and re-tick the "Apply these changes to all other cells with the same settings" box each time I reopen the Spreadsheet. The whole table works okay without too much complication. It just does not seem to save the validation. I played around with a macro today to auto tick the "Apply these changes to all other cells with the same settings" on opening the spreadsheet. This does not appear to function so I am back to square 1.

Concerning G's question. Would it be better to have separate data validation per list on each column? There is already a list per column. It would not take a lot to data validate each name column based on its appropriate list. (I had to do it this way initially so that the auto fill would work).

Unfortunately, we do a lot of data entry in our line of work on different sheets. My colleagues are not as savvy with the computer as I am and I like to simplify and automate things as much as I am able so all they need to do is enter the data as it comes and the formatting etc. handles itself. They cannot follow a simple instruction like SURNAME, Given name which is why I have restricted the data entry. Personally I find typing in names a whole lot quicker because of the auto fill, but I can suffer having to scroll through a plain drop down list if that is the only option to keep things neatly formatted the way that I want.

Not sure what the go with the interrupts in the range D420. There shouldn't be any gaps which is why SURNAME, 1st Name is the default. Ill look into this tomorrow as it explains why I was having issues with the auto fill today at the very bottom of the sheet when testing it.

I will keep playing with =(AND(EXACT(C5, UPPER(C5)),ISTEXT)). That looked promising without the need for drop down lists. There should be a way of having everything after the comma turn into Proper. Just not sure how to combine =(AND(EXACT(C5, UPPER(C5)),ISTEXT)) and =(AND(EXACT(C5, PROPER(C5)),ISTEXT)) together.

Thank you both again for your suggestions and help. It might be scroll through a drop down list after all.

Warm regards
 
Small correction to what I wrote. Nebu's suggestion was about using active x-controls (and not data validation lists), since that would allow what you are after. Cumbersome to set it up for all your rows, I think. Or there is a magic Ninja trick for this. Love to find out if there is.
Now I also understand what you did so that values auto-fill (I missed that before). So, though you stated clearly, the case setting is the only issue.
But that seems to work even without the data validation. just enter in lower case the first few letters, press simply enter, and the casing is set according to the one of the list you made.
Could it be your are digging a way, where the road ahead is already paved? Or am I missing a scenario where the above does not work?
 
Searchable dropdown can be done via data validation list, with some work in the set up.

Here's one such example, taking advantage of Cell() function.
Few notes:
1. You need to have iterative calculation turned on.
2. Each time you enter validation cell, you will need to clear content or type in some string.

Validations are in B2:B6 range.

Another method is to use data validation in conjunction with combobox. But this method will require use of VBA.

Original concept from here.
 

Attachments

  • Searchable_DataValidation_Sample.xlsb
    17.8 KB · Views: 70
Last edited:
Hi Team,

Apologies for the delay in reading your posts.(Night shift now so I have some time to play around a bit on projects).

Regarding Chihiro's suggestion first. I found the web link that you posted very interesting, especially the drop down list refreshing to reflect the cell box entry at the first part of the video. I may have to muck around with that as this brings up "limited" choices to choose from depending on what is typed in. It does however slow things down a bit to have to type and then grab the mouse and then operate the drop down handle to select a name. I'm wondering if there is a formula that brings up the entire drop down list, then eliminates choices as you type? The first part of the CELL () appears to be similar to the second part. I think that the second part of the CELL formula may be a bit redundant for me, but I'm going to muck around with it.

Regarding G's modification to his post. I have just re-modded the spreadsheet per his last suggestion. The simple drop down list (that auto filled from the previous choices) worked just fine. It also worked once saved and reopened. It has the advantage of highlighting and then automatically changing once "Enter" is pressed. Ill muck around with the cell background colouring so that the names underneath the highlighting come up a bit more clearly. This way the user can see what is highlighted and can decide if this is the correct choice.

The only disadvantage to the drop down list alone, is that I can enter an entire name including the comma in lower case, deliberately ignore the auto fill match and press enter. The drop down list will accept this as the cell itself is not case sensitive, only what I want in the cell. Unfortunately, once the lower case rogue name is entered into the list, this then becomes an option for the future. This is what I was trying to eliminate by having the EXACT function compare the contents to the list and spit it out if it is not exact. This then forces the user to be more specific or use the drop down box as these are the only choices for cell input.

It may be that I am over complicating things for myself and that I should use the paved road, treat things as they are and train the other users.

For now, I am going to just use the drop down list combined with auto fill. I am going to try and combine this with the Cell() function so that those who know what they are doing, (ie me) would be happy to enter things correctly and use the auto fill correctly. If I have to a full name + initial to get the correct name eg TAYLOR or SOLINAS, then so be it, but if the others are happy entering a few characters and then using the drop down box to bring up only the "limited" choices this is good too. They are to some extent hunt and peck typists so a few characters and then a drop down would still save them time.

As a final error trap, every once in a while I can set a conditional format to highlight cells that are not EXACT per the list and manually change them.

Thank you all for your input. I have learnt about a new function that looks to prove very handy.

Your time and thoughts very much appreciated.
 
Last edited:
Thanks Nebu.

Ill look into this possible solution next few days and report back. Your time is much appreciated.
 
You must log in or register to reply here.
Back
Top