+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : How can I simplify this toolkit

  1. 10-16-2011, 01:03 PM #1
    Mulski
    Mulski is offline
    Registered User
    Join Date
    10-15-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    How can I simplify this toolkit

    Hi All

    First post so please be gentle. I am trying to put together a guide for a community that I'm involved with and in simple terms I am trying to display a title based on the selection of two drop down boxes.

    At the minute I'm using an IF - AND statement, but this is getting really cumbersome and and would love to understand a simpler way of doing it. Attached is a work in Progress example.

    The key area is am tyring to fix is Cell D9 on the Coding Toolkit tab. All of the other stuff seems to work okay.

    Please note I am not an advanced Excel user and am self taught, so please don't assume I will understand complex formulas without a step through explanation. What you see in the spreadsheet is about the limit of my understanding.

    DRAFT Toolkit v0.01.xlsx

    Many thanks in advance for your help.

    Cheers
    Reply With QuoteRegister To Reply
  2. 10-16-2011, 03:30 PM #2
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: How can I simplify this toolkit

    Hi & Welcome!

    The first problem needed to overcome is the data mismatches between the search columns A & B on 'Coding Detail', and the named ranges on 'System Commands'. It is far easier to find "3G MMI" in a list that contains "3G MMI" than it is in a list that only contains "MMI_3G". Which list would you prefer to change?

    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.
    Reply With QuoteRegister To Reply
  3. 10-17-2011, 03:26 PM #3
    Mulski
    Mulski is offline
    Registered User
    Join Date
    10-15-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How can I simplify this toolkit

    Hi,

    Many thanks for the response and apologies for the delay in getting back to you. I've been out of the office all day.

    Oops, sorry hadn't even noticed the different entries - 3G MMI v MMI 3G .

    The correct value should be as per the 'System Commands', i.e. 'MMI 3G'

    Thanks again for your help.

    Cheers
    Reply With QuoteRegister To Reply
  4. 10-17-2011, 05:50 PM #4
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: How can I simplify this toolkit

    Have a look at this.

    Made use of array formulas using Ctrl-Shift-Enter

    Cheers,
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 10-18-2011, 01:27 PM #5
    Mulski
    Mulski is offline
    Registered User
    Join Date
    10-15-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How can I simplify this toolkit

    Quote Originally Posted by ConneXionLost View Post
    Have a look at this.

    Made use of array formulas using Ctrl-Shift-Enter

    Cheers,
    Many thanks for this and certainly a lot cleaner that the huge number of IF-AND statement I had, although I'll need to spend some time trying to get my head round the fomula. Thanks for the link that explains it.

    The only aspect that I have a challenge with re this approach is by placing all of the System Items (System Commands tab) in a single column means that the System Select (Cell D7 on the coding toolkit) now lists all of the systems apposed to the previous way which filtered them dependent upon the selection in D5 (same tab).

    i.e. D5 is the MMI Type, and dependent upon which one of these you select, you should be presented with only the ones applicable to that MMI Type in D7 apposed to the entire list - hope this makes sense.

    Really appreciated your help with this.

    Cheers
    Last edited by Mulski; 10-18-2011 at 03:00 PM.
    Reply With QuoteRegister To Reply
  6. 10-18-2011, 04:42 PM #6
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: How can I simplify this toolkit

    My apologies for changing the System Items to a single list. I had assumed from your note "Data Not Added Yet" that the final lists would be far more similar.
    Reply With QuoteRegister To Reply
  7. 10-18-2011, 04:47 PM #7
    Mulski
    Mulski is offline
    Registered User
    Join Date
    10-15-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How can I simplify this toolkit

    Quote Originally Posted by ConneXionLost View Post
    My apologies for changing the System Items to a single list. I had assumed from your note "Data Not Added Yet" that the final lists would be far more similar.
    No problem at all, I probably wasn't clear enough in my first post. Are you able to advise on how to restructure it.

    Worked out the CSE forumula though, so learning has definately taken place - thanks.

    Cheers
    Reply With QuoteRegister To Reply
  8. 10-18-2011, 06:54 PM #8
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: How can I simplify this toolkit

    I was going to recommend using dynamic named ranges, but the two linked combo boxes (second referring to the first), adds a layer of volatility that Excel doesn't appear to like.

    The named range MMI_TYPE is still dynamic so perhaps you might use it in the future. Add some text to cell "System Commands'!A5 then use the combo box in 'Coding Toolkit'!D5 to see the benefit of the dynamic named range.

    Other than ensuring your list data matches you table data, there's not much more I might suggest.

    Cheers,
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 10-20-2011, 04:11 PM #9
    Mulski
    Mulski is offline
    Registered User
    Join Date
    10-15-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How can I simplify this toolkit

    Quote Originally Posted by ConneXionLost View Post
    I was going to recommend using dynamic named ranges, but the two linked combo boxes (second referring to the first), adds a layer of volatility that Excel doesn't appear to like.

    The named range MMI_TYPE is still dynamic so perhaps you might use it in the future. Add some text to cell "System Commands'!A5 then use the combo box in 'Coding Toolkit'!D5 to see the benefit of the dynamic named range.

    Other than ensuring your list data matches you table data, there's not much more I might suggest.

    Cheers,
    Wow, thank you very much. Looking at the coding I know I would never have been able to work it out. Really appreciate the great support.

    Cheers
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1