Excel Suppress Columns

[EnforcerPSU7]

Hello, I am new here, but my teacher who posts on these forums a lot recommended this place

I have a spread sheet that contains the columns:
ClassCode, LastName, FirstName, MI, 1/24/2003, 2/7/2003, ...the date columns continue.

The user adds a new date column every 2 weeks. She would like to filter and just view one column or a few date columns are any one time. I am familiar with excel, VBA, and macros, but I am having a hard time thinking up a reasonable solution for this. Any help would be appreciated.

Thank you.

 

[EnforcerPSU7]

UPDATE:

The most important column is the last column. She would like to make all the other columns disappear except the last date column. This wouldn't be bad if it was a static # of columns, but if she makes a new date column every 2 weeks how do I suppress all the columns except the last one?

 

[GlennUK]

Hi EnforcerPSU7,

I do something similar to this, and to allow easy viewing I use the Data Group functionality.

Doing Data Group on columns allows them to be shown or hidden by the click of an outline symbol which Excel displays when columns or rows are outlined.

For your case, you could Group all columns for previous months, and then Group the columns older than 1 month again, and Group the columns older than 2 months again, so than the outline level drops for every month past. When your user adds a new column for a new month she could select all months columns ( click the level number that is largest on the left of the outline control area in order to show all columns ) and perform the menu command Data/Group to Group all the past weeks together, and then click on the hide group symbol to hide those weeks ( the "+" symbol shows that hidden groups can be displayed, while the "-" symbol shows that a group can be hidden with a click.

Have a play with this and see what you think. ( no macro writing necessary for this solution ).

Cheers, Glenn.

 

[SkipVought]

Enforcer,

CAN'T FILTER on ROWS--ONLY COLUMNS!

Your user has made a commmon mistake -- designing a non-normalized spreadsheet table.

Your usere has DATA (Dates) in repeated columns.

Rather, the table ought to be structured like this...
[tt]
ClassCode, LastName, FirstName, MI, WhateverDate
[/tt]
There may be OTHER normailzation problems as well (repeated LastName, FirstName, MI) which ought to be in a Customer, or Contact or Personnel Table with a Foreigh Key in THIS table. Of course, many users don't understand tables and queries of various types that can be done via Data/Get External Data or via External PivotTable.

Then the table can be the basis for an INTERACTIVE REPORT using WhateverDate.

http://www.abcdataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[EnforcerPSU7]

Good ideas guys, I am trying the group command now and it seems to be working just fine. SkipVought, you are right...I know the table is non-normalized. BUt I didn't make it and now I have to deal with it.

Anyway, GlennUK...i was playing with the group command but when I look at the print preview it will not print how it looks on the spread sheet. I have it all collapsed so only the last column is showing. Is there anyway to get that to print?

 

[EnforcerPSU7]

Wait, NVM.

I got it, I just edited the print area.

My bad. Thanks a lot!

 

[GlennUK]

My pleasure.

I had to make someones existing spreadsheet work that way, I didn't design it, but found the grouping did what the user wanted.

Cheers, Glenn.