Drop down lists
Solved/Closed
christossol
Posts
15
Registration date
Friday February 17, 2017
Status
Member
Last seen
March 23, 2022
-
Updated by christossol on 16/03/17 at 07:35 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Mar 31, 2017 at 10:50 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Mar 31, 2017 at 10:50 AM
Related:
- Drop down lists
- Excel drop down list 2 columns - Guide
- Google lists - Guide
- Virtualbox drag and drop - Guide
- What is drop box - Guide
- How to create drop down list in excel - Guide
2 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 16, 2017 at 07:29 PM
Mar 16, 2017 at 07:29 PM
christossol, Good evening.
"...If I copy the spreadsheet with the list to another workbook I have to re-create the list..."
This depends on how you copy the data to the other file.
Instead of copying and pasting use the copy of the worksheet by another method:
Follow this link from the official Microsoft website and choose:
Move or copy worksheets to a different workbook
https://support.microsoft.com/en-us/office/move-or-copy-worksheets-or-worksheet-data-47207967-bbb2-4e95-9b5c-3c174aa69328?ui=en-us&rs=en-us&ad=us
Remember to maintain both workbooks open while this operation.
It works completely.
"...if I edit (insert rows for examble) my datasheet again the drop down list goes wrong..."
This is because when you created the named range it gets with the limits locked.
So when you increase the rows the named range does not change automatically.
Suppose you created the named range in column A of sheet2 of the workbook:Test1.xlsx
Use this formula:
=OFFSET('Sheet2'!$A$1,0,0,COUNTA('Sheet2'!$A:$A),1)
This formula allows you to add new rows without having to change the named range. It always works.
When you copy this tab from the Test1.xlsx workbook to another workbook, for example:Test2.xlsx, the named ranges will go together without any problem.
Please tell us if all of these instructions have resolved your question.
I hope I have helped.
--
Belo Horizonte, Brasil.
Marcílio Lobão
"...If I copy the spreadsheet with the list to another workbook I have to re-create the list..."
This depends on how you copy the data to the other file.
Instead of copying and pasting use the copy of the worksheet by another method:
Follow this link from the official Microsoft website and choose:
Move or copy worksheets to a different workbook
https://support.microsoft.com/en-us/office/move-or-copy-worksheets-or-worksheet-data-47207967-bbb2-4e95-9b5c-3c174aa69328?ui=en-us&rs=en-us&ad=us
Remember to maintain both workbooks open while this operation.
It works completely.
"...if I edit (insert rows for examble) my datasheet again the drop down list goes wrong..."
This is because when you created the named range it gets with the limits locked.
So when you increase the rows the named range does not change automatically.
Suppose you created the named range in column A of sheet2 of the workbook:Test1.xlsx
Use this formula:
=OFFSET('Sheet2'!$A$1,0,0,COUNTA('Sheet2'!$A:$A),1)
This formula allows you to add new rows without having to change the named range. It always works.
When you copy this tab from the Test1.xlsx workbook to another workbook, for example:Test2.xlsx, the named ranges will go together without any problem.
Please tell us if all of these instructions have resolved your question.
I hope I have helped.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 31, 2017 at 10:50 AM
Mar 31, 2017 at 10:50 AM
christossol, Good morning.
Glad to have helped you.
Have a great weekend.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Glad to have helped you.
Have a great weekend.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Mar 31, 2017 at 10:13 AM
my apologies for not responding earlier.....
It worked perfectly....thank you very much