Question

Locked

Dual dropdown arrows in a single cell in Excel 2007?

By toni.bowers_b ·
Tags: Off Topic
This question was asked by a TechRepublic member: This morning I took a call from a coworker who wanted help cleaning up data validation rules in an Excel 2007 spreadsheet she inherited from someone else. I was shocked to find that one of the columns had not one but two arrows for dropdown lists in each cell. The cell location indicator showed D5 as I clicked first one one and then on another dropdown, selecting two different values from lists that were similar but not identical. I used =D5 elsewhere and it returned only one of the values. When I tried to insert a column beside the weird column, I got an error about Excel being unable to move cells beyond the edge of the spreadsheet. (What?) Outcome: The user is starting a new sheet from scratch. Meanwhile I googled and binged and couldn't find a single shred of evidence of this behavior, let alone an explanation of how it happened or how to fix it. Any ideas?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

If you were able to share the file with us...

by TobiF In reply to Dual dropdown arrows in a ...

Then I'm sure we'd find out quicker.
I'd:
- Check for macros (both in general and in the corresponding "event handler page",
- Activate the reverse pointer and try to select drawing objects / form elements,
- Check if the whole worksheet is saved in a strange format,
- Delete the standard validation rules to see if anything is left,
- Try to save it the file in a previous format

Collapse -

Guessing

by dogknees In reply to Dual dropdown arrows in a ...

The cell contains Data Validation and an ActiveX or Controls Combo-box. Hence two arrows.

Inserting can cause the error if you have objects (like Controls or ActiveX (ring a bell)) that would be moved past the edge of the sheet. This is notriously flaky and is caused by a combination of column widths, hidden columns/rows, frozen panes, and Bill Only Knows what else.

Try removing the Data Validation from the cell, then if one arrow remains, try hitting "Design Mode" in Developer/Controls or Home/Editing/Find & Select/Objects, then Home/Editing/Find & Select/Selection Pane to display a list.

Regards

Collapse -

Thanks

by wordworker In reply to Dual dropdown arrows in a ...

@TobiF, thanks for the suggestions.

>If you were able to share the file with us...
Not necessary.
> Check for macros (both in general and in the corresponding "event handler page",
DONE ??? no macros
>- Activate the reverse pointer and try to select drawing objects / form elements,
Reverse pointer? Nonsensical. Perhaps you meant the Select Objects pointer.
>Check if the whole worksheet is saved in a strange format,
DONE ??? it wasn???t.
> Delete the standard validation rules to see if anything is left
That helped.
> Try to save it the file in a previous format
Irrelevant.

@dogknees. Thanks 10^6.

> Try removing the Data Validation from the cell, then if one arrow remains, try hitting "Design Mode" in Developer/Controls or Home/Editing/Find & Select/Objects, then Home/Editing/Find & Select/Selection Pane to display a list.
WINNER!

When I cleared all existing Data Validation rules, half of the dropdown controls disappeared. In the Selection Pane, there were around 900 objects and I was able to hide them. We are still scratching our heads over how the person who created this spreadsheet figured out how to create those controls in the first place. It???s no wonder she wasn???t able to run any reports against the data. The drop down shapes looked like they were functional but they weren???t populating anything.

Collapse -

Request for Clarification

by TobiF In reply to Thanks

Re: "around 900 objects"
You can do a lot of fun stuff with macros... :)

Back to After Hours Forum
5 total posts (Page 1 of 1)  

Off-Topic Forums