MS Excel Question

[SHawk]

Hello to all the MS software experts
here


I hope I can get some help here with a function/program that I am trying to create in MS Excel. I have a questionnaire in the form of an Excel document with several (25 or so) worksheets in it, each dealing with a different topic. Within each worksheet, there are several tables with usually 3 columns: Question, Answer (Y/N/Not Applicable/Can't Say), and Comments.


I want to create a drop down list on the first sheet which will list several categories. When a particular category is selected, I want that those sections of the document that do not apply should be automatically greyed out.


For example, suppose the questionnaire asks about the different features of your computer. On the first sheet, I will have a drop down list specifying the type of computer. If I select a desktop, the entire Worksheet that deals with the topic 'Mobility' should be greyed out. In another Worksheet, 'Connectivity', I only want certain columns in a table like 'Bluetooth' and 'Wi-Fi' to be greyed out, but not others like 'USB' etc.



How hard would it be to do this and how can I go about doing it?

 

[PowerEngineer]

I'm such a fan of Excel that I'd use it to tie my shoes if I could, but a 25-page questionnaire isn't something I'd want to do. Still...

If the areas you intend to grey out are all complete rows, complete columns, and/or complete sheets, then I suggest you consider hiding them instead (or actually unhiding those that apply once the selection from the pull-down list on the first sheet is made). This will require you to write a VB macro.

The easiest way to get started is to record a macro as you manually hide a worksheet and a couple of columns on another worksheet. You can then open the VB editor and see what commands are needed to do this in VB. If you're generally familiar with if-then and for-next kind of programming logic, some experimentation (and reading of the manuals) should get you there.

I'd write the macro so it made sure everything was hidden first and then would unhide the items appropriate for the pull-down selection. You'll obviously need a have a table that flags whether or not each item needs to be hidden or unhidden for each selection; I'd do this on a separate (normally hidden) worksheet (which means you'll need to figure out how to reference worksheet values from VB).

You can tie the resulting macro to the pull-down menu so that it executes whenever a selection is made.

Hope this helps...

 

[NeoV]

drop-down lists are easy with conditional formatting

the rest can be done to, but you have to use some stuff that isn't part of the typical Excel install, I'll post more when I'm back on a PC with Excel on it

 

[JJChicken]

Hello to all the MS software experts
here


I hope I can get some help here with a function/program that I am trying to create in MS Excel. I have a questionnaire in the form of an Excel document with several (25 or so) worksheets in it, each dealing with a different topic. Within each worksheet, there are several tables with usually 3 columns: Question, Answer (Y/N/Not Applicable/Can't Say), and Comments.


I want to create a drop down list on the first sheet which will list several categories. When a particular category is selected, I want that those sections of the document that do not apply should be automatically greyed out.


For example, suppose the questionnaire asks about the different features of your computer. On the first sheet, I will have a drop down list specifying the type of computer. If I select a desktop, the entire Worksheet that deals with the topic 'Mobility' should be greyed out. In another Worksheet, 'Connectivity', I only want certain columns in a table like 'Bluetooth' and 'Wi-Fi' to be greyed out, but not others like 'USB' etc.



How hard would it be to do this and how can I go about doing it?

I think having 25 worksheets or so will make the Excel file too unwieldy to manage. Step back and think if there is a better way to achieve your goal.

If you must continue with the current approach, I agree with the first reply to your post - it would be best to use VBA to hide/unhide relevant sections. If you have never used VBA, then it is probably not a good answer. Can you upload your draft workbook somewhere for us to better understand what you are looking for?