Filters in excel

  B99 23:17 30 Jun 2006

Excuse my ignorance, but I am working with a spreadsheet with multiple columns. If I enter a given field in, say, column A, I only need to see columns B,C and D for the rest of my data entry. Whereas if I put a different value in A, I only need to see E,F and G.

Any tips as to how I can do this? Presumably it has something to do with filters, but I would welcome any help.


  Simsy 23:39 30 Jun 2006

when you say you "only need to see" B,C and D, and then, "only need to see" E,F and G, do you mean that you want the columns BC and D to be hidden, i.e. actually not present on screen, if the appropriate value is entered in col A?

What do you want to be visible while you are actually making the entry?

I suspect that what you really need is a macro that make the selected cell jump from col A to col E for a given entry, or from col A to col B for a different entry... in order to make entering data easier. Am I correct?



  VoG II 08:13 01 Jul 2006

that you are looking for a Worksheet_Change macro. But we need to know the criterion that you want to use to show or hide columns. I.e. a lot more information please.

  Noldi 08:44 01 Jul 2006

Do you actually want to hide the coloums with the unwanted Data or just not see it.

Simsy and VOG are looking in the hide direction and I think with the mention of filters that is what you want.
But maybe you just want to format the cells so the unwanted text turns white?.


  B99 23:50 01 Jul 2006

Ok. Hope this makes things clearer. Column A has the option of inputting any day of the week for any given cell by using the data;validation;list command. If A2 is Monday, I only need the user to input data in cells B2, C2 and D2. However if cell A2 is Tuesday, I only need to input data in cells E2,F2, G2 etc.

Essentially I want to try and avoid having to scroll across to fill in the relevant column, and as this is to be used by a colleague, make the columns that require data to be pretty obvious.

Not sure of the difference between "hiding" the data and "not seeing it" but I'm sure this is more a reflection on me than anything else. Simsy seems to be very close to the mark. I don't mind what is shown prior to data entry, so long as the prompt is there for given only selected cells to be filled dependent on Column A.

Hope this makes things a little clearer. Thanks for the help so far.


  Simsy 07:21 02 Jul 2006

I'm at work at the moment and can't apply myself to it.

It should be quite easy to achieve this with a little VBA.

If no-one else does so I'll get onto it later/tomorrow!



  Simsy 07:27 02 Jul 2006

other days are selected... Weds to Sunday?



  VoG II 08:38 02 Jul 2006

This won't hide anything but will jump to the next cell for data entry.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If UCase(Target.Value) = "MONDAY" Then
Target.Offset(0, 1).Select
Target.Offset(0, 4).Select
End If
End Sub

To use this, right click the sheet tab, select View Code, paste in the above then close the code window.

I haven't tried this with Data Validation but it should work.

  Simsy 09:50 02 Jul 2006

I was going a similar route... but I really like the UCase bit.. I wouldn't have thought of that!



  B99 23:25 03 Jul 2006

Just what I needed, and yes, it works with data validation.



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

Iconic New York graphic designer Milton Glaser on his uplifting new subway posters

New iMac Pro release date, UK price & specs rumours

Comment suivre le parcours du père Noël ?