+ Reply to Thread
Results 1 to 2 of 2

Help with dynamically creating spreadsheets and dropdowns

  1. #1
    Registered User
    Join Date
    Ottawa, Canada
    MS-Off Ver
    Excel 2010

    Post Help with dynamically creating spreadsheets and dropdowns

    Hi, I have a few questions about making some macros to generate a bunch of sheets. Help with any (or all ) of my questions would be really appreciated.

    the columns in my sheet are as follows

    Name | boolean1 | boolean2 | boolean3 | number remaining | list of numbers1 | list of numbers2

    The name, booleans and number remaining are related, for every name i will have a value for each of the booleans in the same row and for the number remaining. The lists of numbers are unrealted to the other columns and can have a different number of rows in them.

    What I want to do with those are create a new sheet for every row in the name/boolean/remaining columns. I don't know before hand how many rows there will be as the sheet is populated from java code running on a server before giving the populated sheet to the user. Each sheet would take the same name as the value in the name column (which are unique).

    Depending on the values in the boolean columns different columns need to be added so those sheets. For example if boolean1 is true the new sheet will require column3&4 and if boolean2 is true ill need to add columns 5&6 etc.

    To make it even more complicated some of those dynamically added columns need to be filled with drop down boxes from the list of numbers1 and list of numbers2 columns on the original sheet. And to make it slightly more complicated i would like the drop downs to only show values from those lists which have not already been selected in any of the sheets that have been created, so the selected values are unique for the entire workbook.

    And one final question is there any way to limit the number of rows in the new sheets that are made. For instance in the row with name = AFakeName if the value in the number remaining column is 5 i would only like the user to be able to fill out 5 rows in the AFakeName sheet. Either not allow them to fill out more rows or display a warning if they have filled out more than 5 rows or something like that.

    I know there are a lot of questions here but again if anyone can help with just part of it I would really appreciate it. While I am a computer programmer i never use excel and have never programmed in VB so I'm at a bit of a loss.

    Thanks in advance,
    Last edited by johnkhawley; 11-05-2010 at 01:21 PM. Reason: Title change

  2. #2
    Valued Forum Contributor blane245's Avatar
    Join Date
    Melbourne, FL
    MS-Off Ver
    Excel 2010

    Re: Help with a few macro questions

    Welcome to the forum!

    Since you are a programmer, what you need to work on is learning the Excel object library so you can do a lot of this yourself. If you look at Stickies on the Excel General forum, you will see a lot of web resources that are available to help in that area.

    Based on the description of your problem, this is all quite doable in Excel VBA. It would help us a lot if you could attach a workbook that contains the some sample data and the resulting output that you are looking for. As they say, a picture is worth a thousand words. If the data is sensitive, continue to use the fake names and values as you have been doing.
    Click my star if my answer helped you. Mark the thread as [SOLVED] if it has been.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1