Hello Gents,
I have a large number of workbooks that use the Define Name facility to create context sensitive dropdown menus. All of these workbooks have been created in 2010 but when they are opening in 365 guess what?? the Define Name group of cells have vanished but the name still exists, so first the existing entry has to be deleted and a new Define Name has to be created. Is this a know problem and is there a work-around less grievous than the one I have described?
Cheers, David.
I don't observe that behaviour with a test workbook.
Would you upload your workbook.
What version of Microsoft Office are you using?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hello Richard,
I am using 2010 and my colleague is now using 365. The problem appeared the first time we tried to use the spreadsheet which I have attached as requested.
Kind regards,
David.
Sorry Richard,
the sheet is 'Categories & Coding and it applied to the thick outlined boxes on the left.
Regards,
David
Hi,
I don't understand the question.
I've opened the file with both 2010 and 2013 in the Office environment and don't see any difference.
What is it that you can't see. i..e what do you mean by the define name group of cells.
Hi Richard,
I use the 'Define Name' facility to create context sensitive drop-down menus and having done so in 2010 when the files are opened in 365 although the names that have been assigned to a group of cells (the Define Name function) appear, the group of cells that are supposed to be there for the drop-down menu choice are not.
In the spreadsheet I sent you, if you select from the menu in cell H6 the choices available from the drop-down menu in cell I6 should change. It works fine in 2010 but the drop-down menu in I6 disappears when opened in 365?
Thanks for you interest,
David.
Hi,
OK I see what you mean but I don't experience the same problem. In My Office Professional Plus 2013 version 15.0.4753.1003 I do get the right drop down changes in column I. What version do you have?
One thing that would be worth trying is the suggestion at Debra's http://www.contextures.com/xlDataVal02.html excellent site.
Scroll down the page to the section 'Using Dynamic Lists'. This shows an alternative to using the INDIRECT function. Try this with one of your selections and see if that makes any difference.
Hello Richard,
Good question, it's my colleague's machine that is running Office 365 but I don't know which version of Excel is being used, it might be 2016 not 2013. Thanks for link to contexture, it may be a solution for the future but my problem is having to re-work the various workbooks that we already have.
Many thanks for your help.
Kind regards,
David.
I get the drop-downs in E, H and I on this machine running the new Excel 2016, so it may be something related to running Office online.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
it works in the excel web app
Josie
if at first you don't succeed try doing it the way your wife told you to
Hi David,Originally Posted by David O'Riley
It would be useful to know if removing the Indirect and using OFFSET or indeed any other approach makes a difference. We know Indirect doesn't work with closed workbooks so there may be some similar behaviour with your two set ups. Are all the Excel settings the same?
The only other difference we have identified is in the machine. The original files were created on an HP running Vista and Office 2010, they open okay on another HP machine running Windows 10 and Office 2010, the problem only seems to manifest itself when opening the files on a Surface Pro running Windows 10 and Office 365.
An interesting idea about using OFFSET Richard, we will conduct an experiment when we have time and see what happens.
Thanks again,
David.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks