Data validation menu lost on sheet copy

[Steve Payne]

I have created some in-cell dropdown menus that validate to a list in the
same sheet. I have copied the worksheet within the same workbook. The
drop-down menus don't work on the copied sheets. The list is still there,
and the data still needs to be validated in the cell, but the dropdown menu
doesn't work.

I have tried to re-generate the list in the cells, to no avail.

Thanks.

 

[Jim Gordon MVP]

Hi,

I tried to reproduce the problem in Excel 2004 by using Edit > Move or
Copy Sheet.

The problem did not reproduce in Excel 2004 with current updates installed.

What version of Excel are you using?

-Jim

 

[Steve Payne]

Hi,

I tried to reproduce the problem in Excel 2004 by using Edit > Move or
Copy Sheet.

The problem did not reproduce in Excel 2004 with current updates installed.

What version of Excel are you using?

-Jim

Excel 2004, 11.2.3 (060209)
Thanks for the help.


Wait, seems if I do it your way, via the edit menu, it works. I had been
doing it by right-clicking on the sheet tab, and selecting move-and-copy
from there. Why would that make a difference?

 

[Bob Greenblatt]

I have created some in-cell dropdown menus that validate to a list in the
same sheet. I have copied the worksheet within the same workbook. The
drop-down menus don't work on the copied sheets. The list is still there,
and the data still needs to be validated in the cell, but the dropdown menu
doesn't work.

I have tried to re-generate the list in the cells, to no avail.

Thanks.

Well, I tried it both ways and it seems to work fine. Can you describe more
precisely what & where the list is, and the cells with validation. And, then
describe how you copied it to make it fail, and exactly what fails.

 

[Steve Payne]

Well, I tried it both ways and it seems to work fine. Can you describe more
precisely what & where the list is, and the cells with validation. And, then
describe how you copied it to make it fail, and exactly what fails.

Its a pretty simple list, really. Just 8 or 9 items a few cells away from
the validation cell. I'm not doing any complex formulas or anything. On
the copied worksheets, the data is still forced to validate, I just have to
enter it in manually. The only thing that doesn't work is the drop-down
menu.

I created the first worksheet, and it worked fine. When I right-clicked on
the tab at the bottom of the sheet (with the sheet name) and selected "move
or copy sheet", and made the copy, the drop-down didn't work on the copied
sheet.

Weird.

 

[Bob Greenblatt]

Its a pretty simple list, really. Just 8 or 9 items a few cells away from
the validation cell. I'm not doing any complex formulas or anything. On
the copied worksheets, the data is still forced to validate, I just have to
enter it in manually. The only thing that doesn't work is the drop-down
menu.

I created the first worksheet, and it worked fine. When I right-clicked on
the tab at the bottom of the sheet (with the sheet name) and selected "move
or copy sheet", and made the copy, the drop-down didn't work on the copied
sheet.

Weird.

That is weird. Does the dropdown itself appear? And nothing happens when you
click on it right? If you chose data validation for that cell, what is in
the dialog? Does a reference to the list appear?

 

[Steve Payne]

That is weird. Does the dropdown itself appear? And nothing happens when you
click on it right? If you chose data validation for that cell, what is in
the dialog? Does a reference to the list appear?

The dropdown itself does not appear. I can only type in a string manually.
The only error I get is an item not in list error just like I would if I
typed in something that wasn't in the list under normal circumstances.

 

[Bob Greenblatt]

The dropdown itself does not appear. I can only type in a string manually.
The only error I get is an item not in list error just like I would if I
typed in something that wasn't in the list under normal circumstances.

OK, the drop down does not appear but the cell has validation? Send me a
sample and I'll take a look.

 

[Bob Greenblatt]

Ok, I looked at the sheet.

First, you should not put attachments on the news group. You should have
emailed me the workbook.

There was a problem in older version of Excel where the drop down list did
not show when window panes were frozen. This still appears to be a problem
In Excel 2004 if the dropdown list refers to cells in another pane. I could
not duplicate the problem exactly on a new sheet.

My guess is that you copied the sheets with frozen panes. There seems to be
some minor corruption in the sheet as the behavior is the same in Excel
2003.

You got me stumped. I suggest you go back to the last copy that had the drop
downs appearing and then save it as a new workbook without the frozen panes
and continue from there.

I'll report this, but don't hold you breath about a fix.

 

[Steve Payne]

Ok, I looked at the sheet.

First, you should not put attachments on the news group. You should have
emailed me the workbook.

There was a problem in older version of Excel where the drop down list did
not show when window panes were frozen. This still appears to be a problem
In Excel 2004 if the dropdown list refers to cells in another pane. I could
not duplicate the problem exactly on a new sheet.

My guess is that you copied the sheets with frozen panes. There seems to be
some minor corruption in the sheet as the behavior is the same in Excel
2003.

You got me stumped. I suggest you go back to the last copy that had the drop
downs appearing and then save it as a new workbook without the frozen panes
and continue from there.

I'll report this, but don't hold you breath about a fix.

Ok, thanks. Sorry about the protocol violation, I know better for next time.
Its not going to keep me from doing anything at this point, so thanks for
your attention to this.

Steve