SOLVED

how to edit inherited name manager menu in spreadsheet?

%3CLINGO-SUB%20id%3D%22lingo-sub-2270531%22%20slang%3D%22en-US%22%3Ehow%20to%20edit%20inherited%20name%20manager%20menu%20in%20spreadsheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270531%22%20slang%3D%22en-US%22%3E%3CP%3EPC%20Windows%2010%20Ed%20%2F%20MS%20Office%20365%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20passed%20down%20sheet%20with%20drop%20down%20menu%20options.%20Column%20I%20is%20solicitor%20team%20member%20names%20which%20is%20extremely%20out%20of%20date.%20I%20have%20not%20used%20Name%20Mgr%20for%20dropdowns%20before%20and%20can%20not%20find%20a%20way%20to%20edit%20this%20list.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2270531%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

PC Windows 10 Ed / MS Office 365

 

Have passed down sheet with drop down menu options. Column I is solicitor team member names which is extremely out of date. I have not used Name Mgr for dropdowns before and can not find a way to edit this list. 

 

Any suggestions appreciated.

5 Replies
best response confirmed by JoAStro (Occasional Contributor)
Solution

@JoAStro 

 

Whoever created that workbook was too clever by half. The data validation list that populates that drop down was in a separate hidden sheet. Moreover, the sheet is password protected. I've unhidden the sheets--there were two hidden--but don't know the password(s). Once you've gotten the password(s), you will be able to edit the list. Short of that, at least you can see where the list is....and maybe start over with another sheet.

No chance of getting password - created by someone no longer employed. Who knows where the hidden sheets are in all her files.
I've always had my data options on the same sheet in hidden/protected cells. Guess I'll be starting over. Thank you for the prompt response.

@JoAStro There are two hidden sheets in your workbook. Both are protected though. Have unhidden them for you. Sheet2 hold the lists for the dropdowns. Sheet3 mostly has errors. Both are protected and require a password to change them.

Created a Sheet4 for you containing the dropdown listings that you can edit, but you'd have to link them again yourself. See attached.

 

Edit: Didn't see the previous posts before I posted mine.

Thanks for adding sheet 4. I will work on linking my updates.

So appreciate the help.
A suggestion for you. I think the idea of having a separate sheet for lists and tables used for background reference is fine; I usually do that, and I'll call that sheet something like "Business Tables" or "Background Tables" to designate its purpose. But unless it needs to be hidden from other users, I'd keep it visible and just put a strong warning on it not to mess with it. Or Password protect it, but make sure the password is accessible and mainly used to keep it from accidental change. Obviously there are situations where utmost security is needed; this one you're dealing with doesn't seem like one of those situations; instead--and why I said the original user was "too clever by half"--is that it's protection that ends up shooting somebody in the foot, actually interfering with the functionality it was meant to provide.