Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2010
    Location
    Toronto, ON, Canada
    Posts
    52
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Lost formatting in Excel

    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

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    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
    Attached Files Attached Files

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    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.

    AssignMacro.jpg Format.jpg

    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.

    Design.jpg


    Properties.jpg

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

    HTH,
    Maud
    Last edited by Maudibe; 2013-03-14 at 18:38.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    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

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    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

  6. #6
    Star Lounger
    Join Date
    Jan 2010
    Location
    Toronto, ON, Canada
    Posts
    52
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •