+ Reply to Thread
Results 1 to 9 of 9

User Input / Processing Sheet

  1. #1
    Registered User
    Join Date
    07-11-2008
    Location
    Ca
    Posts
    14

    User Input / Processing Sheet

    Ok advanced question here:

    I've got multiple sheets. They all have the same row / column layout and do basically the same thing.

    The reason I have different sheets is they represent different product categories. Becuase of this the calculations are slightly different in the same cells across the different sheets.

    What I'd like to do is have one sheet has all the user inputs, layout, comments, etc and have the calculations elsewhere.

    So basically when a user selects a product category different elements (formulas, etc) on the sheet change to fit that product category.

    Is there a way to do this with out using LOOKUP, LINKING, or simular on every cell to every different product category.

    Ideally something on the sheet that when you select a category from the dropdown it replaces an etire predefinded area of the same worksheet with a similar sized/formated section (formulas,formating, and all) from elsewhere in the same sheet or on a different sheet in the same workbook.

    Whats the cleanest easiet way to do this. I really wanted to avoid hundreds of nested LOOKUPS and/or LINKS.

    Thanks much!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You could use CHOOSE to select the formula based on the data type.
    ... when you select a category from the dropdown it replaces an etire predefinded area of the same worksheet ...
    That would require VBA.

  3. #3
    Registered User
    Join Date
    07-11-2008
    Location
    Ca
    Posts
    14
    Quote Originally Posted by shg
    You could use CHOOSE to select the formula based on the data type.

    That would require VBA.
    I've only dabbled in VBA. How difficult would it be to accomplish? Generally speaking what would be involved?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Generally, after the data is entered, it would be moved to the approriate sheet based on data type, and the formulas copied down from the row above.

  5. #5
    Registered User
    Join Date
    07-11-2008
    Location
    Ca
    Posts
    14
    Quote Originally Posted by shg
    Generally, after the data is entered, it would be moved to the appropriate sheet based on data type, and the formulas copied down from the row above.
    This sounds great. Just what I want. Anything specific I should check into or just read up on a general VBA book.

    Shouldn't be too difficult with fairly strong Excel skills right???

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    A book would be a great place to start. And reading the threads in the Programming forum.

  7. #7
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    Another option would be to use a dependent Named Range.

    If the dropdown in linked to cell A1, for example, one could use a named range similar to:


    Name: DependentRange
    RefersTo: =CHOOSE(MATCH(Sheet1!$A$1,{"Hardware","Software","Food","Clothing"},0),Sheet2!$B$1:$C$8,Sheet2!$E$1:$F$8,Sheet3!$G$1:$H$8,Sheet1!$E$1:$F$8)
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    That was my suggestion in post #2 ...

  9. #9
    Registered User
    Join Date
    07-11-2008
    Location
    Ca
    Posts
    14
    Quote Originally Posted by mikerickson
    Another option would be to use a dependent Named Range.

    If the dropdown in linked to cell A1, for example, one could use a named range similar to:


    Name: DependentRange
    RefersTo: =CHOOSE(MATCH(Sheet1!$A$1,{"Hardware","Software","Food","Clothing"},0),Sheet2!$B$1:$C$8,Sheet2!$E$1:$F$8,Sheet3!$G$1:$H$8,Sheet1!$E$1:$F$8)
    I'll have to checkout the choose formula tomorrow. This sounds more promising then embedded if, then, else formulas I've been using and quicker to implement while I learn VBA.

    Thank you both.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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