+ Post New Thread
Results 1 to 14 of 14
How do you do....it? Thread, Excel in Technical; In Excel I've got two drop down lists at the moment with plans to add more soon. What I want ...
  1. #1

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,157
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181

    Excel

    In Excel I've got two drop down lists at the moment with plans to add more soon. What I want to do is make it so that when two options are selected from the list an error is displayed; the two lists show hardware and I want to make a message box display when incompatible hardware is chosen.

    Can this be done in Excel and if so how would I go about doing this? I have a feeling that it can't be done, at least not easily, but I thought I would check rather than not trying at all.

  2. #2

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,197
    Thank Post
    1,806
    Thanked 2,217 Times in 1,635 Posts
    Rep Power
    802
    This really sounds more like a job for Access to me

  3. #3

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,157
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181
    Quote Originally Posted by elsiegee40 View Post
    This really sounds more like a job for Access to me
    I have to use Excel though.

  4. #4

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,653
    Thank Post
    1,257
    Thanked 781 Times in 678 Posts
    Rep Power
    236
    Quote Originally Posted by Edu-IT View Post
    I have to use Excel though.
    Why? If you give a little more detail, someone might know a solution for you. This does sound like a job for a relational database - have a table listing hardware and a "is compatible with" table. Could you use Excel as the front end to a database stored on your network somewhere?

    --
    David Hicks

  5. #5

    Join Date
    Jan 2007
    Location
    Birmingham
    Posts
    807
    Thank Post
    29
    Thanked 36 Times in 24 Posts
    Rep Power
    26
    Quote Originally Posted by Edu-IT View Post
    I have to use Excel though.
    Tell us more, could the second drop-down contents be dependant on the first? That would eliminate the need for an error message. The error message and post selection valdiation would make it messy.

  6. #6

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,157
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181
    Quote Originally Posted by dhicks View Post
    Why? If you give a little more detail, someone might know a solution for you. This does sound like a job for a relational database - have a table listing hardware and a "is compatible with" table. Could you use Excel as the front end to a database stored on your network somewhere?

    --
    David Hicks
    Once again this is for a project and the syllabus requires us to use Excel. We can use VBA if necessary.

    Quote Originally Posted by Lee_K_81 View Post
    Tell us more, could the second drop-down contents be dependant on the first? That would eliminate the need for an error message. The error message and post selection valdiation would make it messy.
    Absolutely. I didn't think of doing it that way. Could that be achieved in Excel?

  7. #7

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,197
    Thank Post
    1,806
    Thanked 2,217 Times in 1,635 Posts
    Rep Power
    802
    Quote Originally Posted by Edu-IT View Post
    Once again this is for a project and the syllabus requires us to use Excel.
    Lucky you! A syllabus that requires you to do something with a tool that isn't the best one for the job... excellent preparation for life in the real world come to think of it

  8. #8
    Jona's Avatar
    Join Date
    May 2007
    Location
    Cranleigh
    Posts
    469
    Thank Post
    14
    Thanked 50 Times in 48 Posts
    Rep Power
    23
    Should be possible with VBA.... I can't remember how to do excel VBA stuff it's been a while but pseudo code......

    on change_combo1
    if (combo1.value="value1") AND (combo2.value="value2") then
    msgbox "Incorrect values
    end if

  9. #9

    Join Date
    Jan 2007
    Location
    Birmingham
    Posts
    807
    Thank Post
    29
    Thanked 36 Times in 24 Posts
    Rep Power
    26
    Attached a pretty rough and ready example. It's not great though.

    If you look at the following examples they should give some indication on how to remove the unwanted extras.

    http://www.cpearson.com/excel/noblanks.htm
    http://www.cpearson.com/excel/named.htm#Dynamic
    http://www.cpearson.com/excel/Return...ysFromVBA.aspx

    Hope this helps.
    Attached Files Attached Files

  10. #10

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,653
    Thank Post
    1,257
    Thanked 781 Times in 678 Posts
    Rep Power
    236
    Quote Originally Posted by Edu-IT View Post
    Once again this is for a project and the syllabus requires us to use Excel. We can use VBA if necessary.
    I remember at first year undergrad computer science level we were required to demonstrate we knew how a relational database worked by writing one in Excel (you get that a lot with CS - learn about processors by writing an emulator, learn about OSes by writing one, etc). I seem to remember we simply used separate Excel worksheets as tables, with the first worksheet as the GUI part of things that we placed VB controls and suchlike on.

    --
    David Hicks

  11. #11

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,197
    Thank Post
    1,806
    Thanked 2,217 Times in 1,635 Posts
    Rep Power
    802
    Hmmm.... first year of my degree - punch cards, George3 and a lot of time in Bowland College bar waiting for jobs be turned round! Those were the days!

  12. #12

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,653
    Thank Post
    1,257
    Thanked 781 Times in 678 Posts
    Rep Power
    236
    Quote Originally Posted by elsiegee40 View Post
    Hmmm.... first year of my degree - punch cards, George3 and a lot of time in Bowland College bar waiting for jobs be turned round! Those were the days!
    We didn't have punch cards, but we had pre-OS 8.5 Macintoshes. Pretty much the same thing.

    --
    David Hicks

  13. #13

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,157
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181
    Quote Originally Posted by Lee_K_81 View Post
    Attached a pretty rough and ready example. It's not great though.

    If you look at the following examples they should give some indication on how to remove the unwanted extras.

    http://www.cpearson.com/excel/noblanks.htm
    http://www.cpearson.com/excel/named.htm#Dynamic
    http://www.cpearson.com/excel/Return...ysFromVBA.aspx

    Hope this helps.
    I'm not entirely sure whether or not that will work though because of the layout I have. It's a great idea, I just don't know whether it'll do the job.

    Lets say that AMD Phenom 9500 is not compatible with Windows Vista Business, I want Windows Vista Business not to be displayed. The problem is that the data is spread across different sheets.

    It's late and I can't think...

    Edit: I've just had a quick go, changing the data on your sheet to something like I want and it works. I just need to get my head around how it'll work when the data is spread across different sheets. I think VBA may be the way forward.
    Attached Images Attached Images
    Last edited by Edu-IT; 7th March 2008 at 12:22 AM.

  14. #14
    SteveBentley's Avatar
    Join Date
    Jun 2007
    Location
    Yorkshire
    Posts
    1,439
    Thank Post
    120
    Thanked 263 Times in 189 Posts
    Rep Power
    72
    Quote Originally Posted by elsiegee40 View Post
    Lucky you! A syllabus that requires you to do something with a tool that isn't the best one for the job... excellent preparation for life in the real world come to think of it
    People using Excel when they should be using a database is one of my pet hates (although I understand and sympathise with the OP's position).

    I've found the linked tables feature in Access to be brilliant when trying to sort out the mess people get themselves into. Set up some named ranges on the Excel Sheet, pull them in as linked tables in the DB and use proper SQL queries to do whatever reports you need and the user can continue to edit the data in Excel, which they are familiar with.

    I set up a system this way for my ex (before she was my ex!) who had to compile two monthly reports, one in a spreadsheet, one as a summary, both presented to the client on paper. The spreadsheet didn't contain enough data to be able to break things down for the summary, so the summary had to be done manually and often didn't tally with the spreadsheet. So I added a couple of extra fields outside the print area for the reporting categories and imported the lot into Excel as a linked table and set up queries to do the summaries and create the report. Worked like a charm and meant that she didn't have to learn how to input data into Access (not that it's difficult) and could continue to use Excel and just open Access up when she needed the summary report.

    Incidentally - whoever invented vlookup should be shot, if your data is complex to need that, it needs to be in a proper relational DB, not a flat spreadsheet.

SHARE:
+ Post New Thread

Similar Threads

  1. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 04:56 PM
  2. Excel Problem
    By TechSupp in forum General Chat
    Replies: 0
    Last Post: 2nd November 2007, 10:08 PM
  3. Excel 2007
    By wesleyw in forum How do you do....it?
    Replies: 7
    Last Post: 25th June 2007, 05:39 PM
  4. Excel IF statement maybe VB
    By danIT in forum General Chat
    Replies: 1
    Last Post: 5th January 2007, 12:12 PM
  5. Excel frustration
    By GrumbleDook in forum Windows
    Replies: 3
    Last Post: 24th August 2005, 10:01 PM

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
  •