1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Drop-down lists and hidden columns in Excel

Discussion in 'Business Applications' started by Tavel, Oct 28, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Tavel

    Tavel Thread Starter

    Joined:
    Oct 20, 2009
    Messages:
    9
    I have several excel sheets which accumulate data at regular periods. This accumulation involves the addition of new columns. Each column contains one or two drop down lists.

    When I report to other team members I send them .pdf's of the datasheets. In order to get the reports to fit on one piece of paper I hide the unnecessary columns.

    When I hide the columns all the dropdown lists get shunted along to the first visible column. This was never a problem until the format of the columns were changed (request from the customer). This change can not be implemented for all previous entries.

    I now have the problem that the bunched up dropdown lists appear in the first visible column obscuring the title for that column. How can I get around this problem without spending 15 minutes (temporarily) deleting these dropdown columns each time?

    I have thought about deleting them permanently except the sheet is also used by other people and this would cause unwanted problems.

    I hope someone can help me. Thankyou.

    I should have added: if you hide columns C through F in the test file then you will see all the dropdown lists shift into column G.
     

    Attached Files:

  2. Tavel

    Tavel Thread Starter

    Joined:
    Oct 20, 2009
    Messages:
    9
    bump
     
  3. Tavel

    Tavel Thread Starter

    Joined:
    Oct 20, 2009
    Messages:
    9
    bump
     
  4. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    I suggest you replace those dropdown boxes with data validation > list. This option would provide dropdown boxes also, and will resolve your problem.
     
  5. Tavel

    Tavel Thread Starter

    Joined:
    Oct 20, 2009
    Messages:
    9
    Do you mean a formula?

    Could you give me a brief explanation of what you mean, please.

    Thankyou.
     
  6. Tavel

    Tavel Thread Starter

    Joined:
    Oct 20, 2009
    Messages:
    9
    bump
     
  7. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    See attached.
     

    Attached Files:

  8. Tavel

    Tavel Thread Starter

    Joined:
    Oct 20, 2009
    Messages:
    9
    Turbodante,

    This looks super.:) I have tried copying and pasting, and have tried to see how you have made this.

    Could you point me in the direction of which commands and options I need to make such a list please.

    I really appreciate the help you are giving me. (y)
     
  9. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Not sure which version of excel you’re using, but you need to find the menu with data validation. In version pre-2007 I think it's

    Data > Validation > Settings

    From ‘Allow’, click ‘List’

    In ‘source’ , you can either type in your selection choices or select from a range of cells from the same sheet.
     
  10. Tavel

    Tavel Thread Starter

    Joined:
    Oct 20, 2009
    Messages:
    9
    Sorry for my late reply.

    This is super and it works like a dream.

    Thankyou very much.:D
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/872323

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice