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
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,
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
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
Have a look at this.
Made use of array formulas using Ctrl-Shift-Enter
Cheers,
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
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.Originally Posted by ConneXionLost
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.
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.
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
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
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,
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks