Seeking help with complex (to me) job for selectively hiding columns

[ScotiaSpinner]

I have been trying to figure out this project on my own, but running into trouble, so I thought I'd try a forum. Please be gentle, I'm new here!

Here's what I have. I have a spreadsheet with columns that represent certain types of training (from column E through DK, so a lot of columns). I have rows with job titles in them (about 60). Each cell in the table has an R for "required" or N/A for "not required." Client wants to be able to click on a job title and see only the columns that contain the R. So for example, let's say job row 50 is "chief bottle washer" and that job has an R listed in "glassware safety" and "sink cleaning" and "detergent training" as the 3 required training sessions. The client wants to click on the cell containing "Chief Bottle Washer" and the all the many other training types would disappear, and just the 3 required training columns would be showing. (preferably, then, click again on chief bottle washer - or elsewhere - and the whole table comes back).

Secondarily to this, there will be names listed for each job in rows under the job title. Client also wants to be able to click on a name (listed under a certain job) and have the list of requirements show up (as described above) and then the name in the row underneath, showing which of the requirements the individual has, or hasn't, completed. So, click on Joe Smith, the Chief Bottle Washer #1, and see that he has completed glassware safety and detergent training but still needs to go learn sink cleaning.

So, I know how to do auto-filter, but that doesn't get me where I need to go with this. Do I need macros? Pivot tables? What? This is just beyond my previous experience with Excel. I've been told that MS Access would be better for this type of thing, but that is not something the client has. The spreadsheets already exist for all the positions, the training, and the R or N/A cells. I am just supposed to make them do these cool things. I'm usually a technical writer, not an Excel guru. Kind of got roped into this one without realizing it.

Any help would be very much appreciated.
Cheers,
Claire

[ScotiaSpinner]

I found the following macro in my hunting for information, which apparently will hide column H if the value of cell B is zero.

Please Login or Register  to view this content.

Would this be something that could be adapted to my situation as described above? So a macro that says if the value of a cell is N/A, then hide the column of that cell, and if the value of a cell is R, then don't hide that column. It would just check across the whole row, so a range of cells? Can such a thing be done?

[stnkynts]

Yes and No. The if statement will only work on a single range. You will need a loop incorporated in what you are trying to accomplish if you want to use a macro, but you are correct in hiding the columns. I actually thought about tackling this project but there is a lot of unknown variables that I don't want to guess at, specially on a friday.

Probably one of the first things you should figure out is how you are going to trigger this macro to execute. There isn't a single click option that I am aware of. You could have it trigger on a double click event and then have everything unhide on the selection change event. Just my thoughts. Hope this points you in a helpful direction.

[ScotiaSpinner]

Thank you for responding. That is helpful indeed. I didn't know it would need a double click although the
client in question probably won't mind that aspect. I can see how a single click wouldn't be useful because
of course, then one could not change text in cells, etc, without activating the macro. Glad to know that it
would deactivate upon selecting something else. A friend of mine who knows some Visual Basic is pondering it. I will tell her this piece of info. One step at a time!

[jaslake]

Hi Claire

You might consider a UserForm for this exercise. For help with that a Data File would be required (sanitized for confidential information).

[FDibbins]

Hi ScotiaSpinner and welcome to the forum

We would love to help you with your question, but 1st, please rename your thread to something more meaningful, that actually describes your problem, not your outcome. Many members will look at a thread title, and if it is of interest to them, or falls within their area of expertese, they might only open those threads. also, searching for "help" will not turn up too many results that will benifit you, sorry.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

[ScotiaSpinner]

Glad to say that with the help from stnkynts and some of my own hunting, and a friend with some VB knowledge, we worked out the necessary macro. In case anyone ever reviews this thread and wants to know what it was, here it is! Does exactly what was needed and works every time. I am so happy to have this solved.

Please Login or Register  to view this content.

[FDibbins]

ScotiaSpinner, I am happy you arrived at a solution. In future please remember 2 things...


Do not ignore Moderator or senior member's requests (for instance regarding thread title changes)

You are required to use code tags when when posting VBA code.

Thanks

[ScotiaSpinner]

I am sorry, I changed my title....I wasn't sure what to change it to because I really did not know how to describe what I wanted, or if I even needed a macro because I didn't really know what one was, but someone told me I needed one and someone else said I needed a pivot table, and I still don't know what that is, so I hope that the change I made is acceptable.

I apologize for my ignorance, but I do not know what a code tag is. I would like to edit the post to make it conform, but do not understand what you are asking me to do. Do I just write the words "code tag" at the beginning and end of the lines of code? I didn't even know until today that there was something called Visual Basic and I didn't know the acronym VBA, so I'm really green with this stuff.

[jeffreybrown]

See if this helps guide you in the right direction...

http://www.excelforum.com/misc.php?do=bbcode#code

and also the forum rules...

http://www.excelforum.com/forum-rule...rum-rules.html

[ScotiaSpinner]

OK, very very sorry again, for having broken rules.
I think I have done what you asked but please say if not.
If anybody would like me to change the title again I will change it to whatever you find most appropriate.
Very sorry to bother everyone. Shall try not to cause upset again.

[jaslake]

@ Mods
@ Administrators

Ah well...we lost another one.

[FDibbins]

ScotiaSpinner, our/my intent was not to offend, and if I did, I apologize. Every organisation has rules, and ours is no exception, our rules are there to try and make things just a bit more organised and easier for every-one concerned - and admittedly not everybody takes the time to read the fine print when they start something. Perhaps you could just chalk this 1 up to a learning experience, and continue to learn and grow in excel with us all here