Questions

Dual dropdown arrows in a single cell in Excel 2007?

+
0 Votes
Locked

Dual dropdown arrows in a single cell in Excel 2007?

toni.bowers_b
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?
  • +
    0 Votes
    TobiF

    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

    +
    1 Votes
    dogknees

    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

    +
    0 Votes
    wordworker

    @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.

    +
    0 Votes
    TobiF

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

  • +
    0 Votes
    TobiF

    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

    +
    1 Votes
    dogknees

    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

    +
    0 Votes
    wordworker

    @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.

    +
    0 Votes
    TobiF

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