Report

Drop down lists [Solved]

Ask a question christossol 6Posts Friday February 17, 2017Registration date April 10, 2017 Last seen - Last answered on Mar 31, 2017 at 10:50 AM by Mazzaropi
Hi All,

i have a drop down list in excel and the data for the list in a sheet in the same workbook.

If I copy the spreadsheet with the list to another workbook I have to re-create the list.

if I edit (insert rows for examble) my datasheet again the drop down list goes wrong....
How can i keep it fixed?

Helpful
+0
plus moins
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.office.com/en-US/article/Move-or-copy-worksheets-or-worksheet-data-47207967-BBB2-4E95-9B5C-3C174AA69328

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
christossol 6Posts Friday February 17, 2017Registration date April 10, 2017 Last seen - Mar 31, 2017 at 10:13 AM
Dear Mazzaropi

my apologies for not responding earlier.....

It worked perfectly....thank you very much
Reply
Leave a comment
Helpful
+0
plus moins
christossol, Good morning.

Glad to have helped you.

Have a great weekend.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!