Lost formatting in Excel

Topic

New Reply

Hi All:

I’m not a really expert user in Excel and am stumped here.

My work gave me an Excell sheet with things I have to update and change every month.

Some of the columns had drop-downs where I had to choose “Y”, “N”, ro “N/A” to fill in the cell.

These drop-downs appear to have all lost the “Y” choice and don’t work with any of the other choices, which have spread out so the drop down is about 4 inches long now.

I’m panicked that I may have to start this sheet all over again, because I just don’t know how to fix it and it has to be fixed in order for me to do my work.

Any experts with suggestions for this? I would be eternally grateful to get it fixed with no other problems occuring
TIA,
~marilyn

"She was not quite what you would call refined. She was not quite what you would call unrefined.
She was the kind of person that keeps a parrot."
--Mark Twain

Reply | Quote

Replies

Hi Marilyn

Save the attached file.
Then open it.
Copy cell [A1] which has the dropdown you want.
Paste it using Ctrl-V to where your current dropdown isn’t working.

zeddy

Reply | Quote

Zeddy, Since Marilyn has expanded the drop down to 4″ I am wondering if we are looking at an Active X or form control rather data validation. Even if the source list for data validation was increased to a large amount of entries, the drop down would grow no larger that about 1.5 inches with a scroll bar. If this is an Active X or a form control, most likey the souce data has changed.

Marilyn, by right clicking on the control do you get a context menu that has has Assign Macro as one of its choices? If so, we are looking at a form control. My guess is that by clicking “Format control”, here is where you may have increased the number of dropdown lines to make it 4 inches long. You may have to re-establish the input range to view all your choices.

33411-AssignMacro 33412-Format

If it is an Active X control and if you right click on the control and get no context menu, enter design mode (developer tab 2007-2010)(Control tool bar 2003) then right click the control. Click on properties and next to the ListFillRange property and enter the range of the choices (Ex N7:N20) that you need for the source.

33414-Design

33413-Properties

If able (no confidential data), you may also post your workbook and we will happy to repair it for you.

HTH,
Maud

Reply | Quote

Hi Maudibe

I have seen ‘corrupted’ data validation dropdowns which become ‘very wide’.
So I believe Marilyn is actually seeing a ‘4-inch-wide’ data validation dropdown rather than a ‘4-inch-deep’ activeX dropdown.
And, based on Marilyn saying ‘I’m not really an expert’ I gave the simplest solution I could think of i.e. copy and paste a cell. with the required validation rule.
I think your reply might scare a beginner, but I know what you are talking about.

zeddy

Reply | Quote

so the drop down is about 4 inches long now.

Zeddy,
“4 inches long” just made me think verticle but I hear what you are saying bro. Hopefully you are right that it is data validation in which case, you did provide an easy solution. If not, however, Marilyn claimed not to be an expert, as opposed to, being a beginner. Also, that she would have to start the sheet all over again. I am anticipating she has a basic understanding and would fair well. I have tried to make my explanations simple with images and examples to guide along the way. I don’t think it is too daunting but her problem seems simple and there are plenty of eager members to help.

Back to the first person… Marilyn, fear not. We’ll get you back up and running in no time.

Maud

Reply | Quote

You guys are so great!

I muddled through on the one when I posted here, then took vacation and skipped one, so now I’m back to the problem. And I kind of have two problems, I guess – one being that I don’t understand most of what you said above, any of you (maybe I should change ‘not really expert’ to ‘only know how to do the few things I always use’) and that yes, the work is very confidential for the Federal Government so I can’t post the sheet.

What I’m going to do is to try and copy and paste all your instructions and ideas into an e-mail, and mail it to my work computer (I’m in the middle of moving so don’t even have access to a printer) and see if I can follow them when I take it step by step. I know some of you were kind enough to ‘draw pictures’ so I’m hoping I get it done today.

But if you hear back from me today, I wouldn’t be too surprised.
Thanks again and off to try it.

"She was not quite what you would call refined. She was not quite what you would call unrefined.
She was the kind of person that keeps a parrot."
--Mark Twain

Reply | Quote