Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    create dynamic formulas based on lookup table (xl97/xl2000)

    Hi all, I attached a sample worksheet for what I need to do. It's quite hard to explain in words but here goes...

    I have a table of categories and monthly data. Each category has 5 sub-categories. These sub-categories are the same for each major category (e.g., TypeA has a,b,c and TypeB also has a,b,c).

    I also have a table that is used to contain the total of each sub-category. The tricky part is that the user can select which sub-category to use for a given major category (e.g., select 'b' for TypeA and 'c' for TypeB). I used Data/Validation for each cell to produce the drop down list of choices available for each category.

    Confused yet? Please look at the attached .xls file.

    I need to figure out some code that will dynamically create the formula for the cells based on what the user selected. If this can be done w/o code that's fine too (using a regular Excel formula - I'm more a VBA whiz not a formula whiz <img src=/S/blush.gif border=0 alt=blush width=15 height=15>)

    I'd appreciate any cool ideas you can come up with.
    Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create dynamic formulas based on lookup table (xl97/xl2000)

    Hi Deb, no code with this one, but the attached uses VLookup to find the values. I inserted the name 'MasterList' in the attached.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create dynamic formulas based on lookup table (xl97/xl2000)

    Forgot to ask if there is a reason why the caluclation was set to manual, I left it at manual. After changing the user selection, you will have to hit F9 to update the values unless you plan on using one of the worksheet events to update the values.

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create dynamic formulas based on lookup table (xl97/xl2000)

    WOW, that was fast work!! No reason that the sample was set to Manual calc, I didn't check that before I wrote it. It is set to Auto in the 'real' model.

    I knew there was a non-VBA way to do this but I'm not too swift with the fancier formulas.

    Deb <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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