Excel

[Edu-IT]

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.

[elsiegee40]

This really sounds more like a job for Access to me

[Edu-IT]

This really sounds more like a job for Access to me

I have to use Excel though.

[dhicks]

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

[Lee_K_81]

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.

[Edu-IT]

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.

 

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?

[elsiegee40]

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

[Jona]

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

[Lee_K_81]

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/ReturningArraysFromVBA.aspx

 

Hope this helps.

dependantDDLs.zip

[dhicks]

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

[elsiegee40]

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!

[dhicks]

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

[Edu-IT]

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/ReturningArraysFromVBA.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.

Edited March 7, 2008 by Edu-IT

[SteveBentley]

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.