Excel lists mysteriously disappear

andreea

New Member
Joined
Apr 1, 2005
Messages
7
Hi, guys!

I made an excel file and I created some predefined lists usind Data>Validation>List and a named range. I made a list (Ctrl+L) containing the named range so it will automatically extend each time the user enters a value in the list.

The method was working perfectly BUT after 2 months of using this file, a guy tells me "the blue rectangles" has disappeared. My question is: How could they do that? There were more than 20 lists in that file and now there is no one left... and the guy sais "I didn't do anything". I'm a little bit confused so, if you have any idea... please help :)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
andreea,

Do you mean that the dynamic range name you created has been deleted? Do you mean the contents of the dynamic range have been deleted? Was the sheet containing the lists protected? With a password?

Dufus
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
If you click in one of the list ranges, does the "blue rectangle" re-appear?

If so, then go to Data|List and select Hide Border of Inactive Lists.
 

andreea

New Member
Joined
Apr 1, 2005
Messages
7
Thank you for the prompt replies, guys!
Dufus, the named range is still there, only the list has disappered.
NBVC, thank you, but it's not that, i've checke it... I mean the list is not there, like it has never been created, I don't mean the borders :(
Do you have any other ideas?
Thank you again!
Andreea
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

Perhaps it was saved in a lower version of Excel and then re-saved to 2003?

not sure, but looks like you might have to re-list each range via the Ctrl-L or Data|List|Create List.
 

andreea

New Member
Joined
Apr 1, 2005
Messages
7
Nope... every computer in this company has Excel 2003 and... Yup... this looks like I will have to re-list them again but, still, I'm not sure what could have caused this problem and if it can happen again.
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176

ADVERTISEMENT

andreea,

Someone must have deleted the contents of the List if you're trying to say the contents of the List are gone. Are the headers missing too? I'm still unclear on whether the definition of the Data List remains and if the sheet was protected in any way. By the way, a Data List will automatically expand without creating a dynamic range. So, I'm curious why you created a dynamic range name and assigned it to the Data List?

Dufus
 

andreea

New Member
Joined
Apr 1, 2005
Messages
7
Hello again Dufus,

I am trying to say that the contents of the lists are there. The named ranges are there, too. Only the Data List definition is missing. So, basically, I now have a the named ranges and the values inside these ranges.
Unfortunatelly the sheet had no protection.
I mixed the Data List and the named range because I needed the named range to expand automatically. I was using those ranges to create validation lists for other cells.

Bye!
Andreea
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
andreea,

Thanks for clearing that up for me. I'm using XL2K at home so I can't test it right now but, I thought when I was playing with this earlier that I did something simple like Data Validation it undid the List. I'm guessing it's possible to undo a List pretty easily. As for recreating them, maybe a macro would be advisable. You could even trigger it on open of the workbook just to be sure all your lists are correct before the user begins.

Dufus
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,540
Latest member
Nereus A

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top