Automatically hide multiple columns in one worksheet based on multiple inputs on another

A

AlexCross

New Member
Joined
Jun 29, 2019
Messages
4
Hello,

My employees work on multiple projects, they use excel to record their hours. We currently have 10+ projects but an employee may only be working on any 5 of those. Each employee has their own excel spreadsheet (stored on sharepoint- online 365)

I would like to be able to automatically hide columns /projects(3 columns per project) on sheet 2, that the employee is not working when the value under the Assigned heading on Sheet 1 (see C2-C13 etc) for a project is "N". Column C has a data validated function to be either "blank", "Y" or "N"

Sheet 1 below

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]Notes:corresponding column on sheet 2 to hide[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee:[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Project Name[/TD]
[TD]Assigned [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]T500[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]T501[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]F-H[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]T502[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]T503[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]L-N[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]T504[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]T505[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]T506[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]U-W[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]T507[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]X-Z[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]T508[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]T509[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]AD-AF[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]T510[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 below

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]l[/TD]
[TD]m[/TD]
[TD]n[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD]r[/TD]
[TD]s[/TD]
[TD]t[/TD]
[TD]u[/TD]
[TD]v[/TD]
[TD]w[/TD]
[TD]x[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project[/TD]
[TD][/TD]
[TD]T500 (Col:C-E)[/TD]
[TD][/TD]
[TD][/TD]
[TD]T501 (Col:f-h)[/TD]
[TD][/TD]
[TD][/TD]
[TD]T502 (Col: i - K[/TD]
[TD][/TD]
[TD][/TD]
[TD]T503 (Col: L-N[/TD]
[TD][/TD]
[TD][/TD]
[TD]T504 (Col: O-Q)[/TD]
[TD][/TD]
[TD][/TD]
[TD]T505(Col: R-T)[/TD]
[TD][/TD]
[TD][/TD]
[TD]T506 (Col: U-W)[/TD]
[TD][/TD]
[TD][/TD]
[TD]ETC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Can you please assist me with appropriate coding to complete this function, your assistance is greatly appreciated.

Alex

The result of the coding would be as follows:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD]r[/TD]
[TD]s[/TD]
[TD]t[/TD]
[TD]x[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project[/TD]
[TD][/TD]
[TD]T500 (Col:C-E)[/TD]
[TD][/TD]
[TD][/TD]
[TD]T502 (Col: i - K[/TD]
[TD][/TD]
[TD][/TD]
[TD]T504 (Col: O-Q)[/TD]
[TD][/TD]
[TD][/TD]
[TD]T505(Col: R-T)[/TD]
[TD][/TD]
[TD][/TD]
[TD]ETC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make sure that the columns to hide have been entered in column E. Then enter an "N" in the appropriate row in column E and press the RETURN key.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Dim splitCols As Variant
    If Target = "N" Then
        splitCols = Split(Target.Offset(0, 2), "-")
        With Sheets("Sheet2")
            .Columns.Hidden = False
            .Columns(splitCols(0) & ":" & splitCols(1)).EntireColumn.Hidden = True
        End With
    End If
End Sub
 
Upvote 0
mumps said:
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make sure that the columns to hide have been entered in column E. Then enter an "N" in the appropriate row in column E and press the RETURN key.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Dim splitCols As Variant
    If Target = "N" Then
        splitCols = Split(Target.Offset(0, 2), "-")
        With Sheets("Sheet2")
            .Columns.Hidden = False
            .Columns(splitCols(0) & ":" & splitCols(1)).EntireColumn.Hidden = True
        End With
    End If
End Sub
Click to expand...



Mumps, thanks for the quick response, I inserted code as specified in Microsoft Excel Objects/Sheet1 (Sheet1). in the Workshop panel, then saving as XLSM file. I received Run time error 9 Subscript out of range error message on the following code:

.Columns(splitCols(0) & ":" & splitCols(1)).EntireColumn.Hidden = True

I have also realised that my original message should have been more clear. If I remove the "N" from Column C / against a project, I would also like the project to reappear (ie the relevant columns become unhidden - sheet 2) PS in your notes you refer to column E... I presume this was actually column c in sheet 1 where I have place "N" against the project.

Regards Alex
 
Upvote 0
I tested the macro using the data you posted and it worked properly. Based on how your data was posted, it looked like column E contained the columns to hide. I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Mumps,
Got it working!!!! Didnt update column references in column E as you specified on sheet 1. Sorry

At the moment it only hides the last "N" assigned to a project - ie it can only hides one at a time and previous project hidden with "N" reappear. I was hoping to hide/ unhide multiple projects/ column when I entered an N or removed it against a project?

Appreciate your support...
 
Upvote 0
Try this version. Enter "N" to hide and "Y" to unhide.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Dim splitCols As Variant
    Select Case Target.Value
        Case "N"
            splitCols = Split(Target.Offset(0, 2), "-")
            With Sheets("Sheet2")
                .Columns(splitCols(0) & ":" & splitCols(1)).EntireColumn.Hidden = True
            End With
        Case "Y"
            splitCols = Split(Target.Offset(0, 2), "-")
            With Sheets("Sheet2")
                .Columns(splitCols(0) & ":" & splitCols(1)).EntireColumn.Hidden = False
            End With
    End Select
End Sub
 
Upvote 0
mumps said:
Try this version. Enter "N" to hide and "Y" to unhide.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Dim splitCols As Variant
    Select Case Target.Value
        Case "N"
            splitCols = Split(Target.Offset(0, 2), "-")
            With Sheets("Sheet2")
                .Columns(splitCols(0) & ":" & splitCols(1)).EntireColumn.Hidden = True
            End With
        Case "Y"
            splitCols = Split(Target.Offset(0, 2), "-")
            With Sheets("Sheet2")
                .Columns(splitCols(0) & ":" & splitCols(1)).EntireColumn.Hidden = False
            End With
    End Select
End Sub
Click to expand...


Mumps, that is fantastic... cannot tell you how much I appreciate your help. It is after midnight here in South Australia on Saturday night - got to work tomorrow to get job done for Monday... just taken a load off my mind with your solution. Thanks again. Alex
 
Upvote 0
You are very welcome. Sleep well. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
933
Alex Blakenburg
A
V
  • Question Question
Multiple linked tables that can all be edited, and automatically update across all?
Replies
0
Views
63
VikingLink
V
youbitto
  • Solved
Copy paste from multiple Excel and Csv files to another excel file VBA
Replies
2
Views
199
youbitto
youbitto
S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
98
HighAndWilder
H
B
  • Solved
Issue rearranging/sorting data based on multiple columns
Replies
1
Views
422
BasicContract8989
B

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top