Auto Hide Column

[makavity]

Hi All!

I'm working on a spreadsheet using a specific calendar to my company. We divide each 3 months using 4 weeks, 4 weeks, 5 weeks. I'm trying to set up each month to have 5 weeks, but to use a conditional format or something to automatically hide the 5th week column on the 4 week months.

Ex.
I'm trying to get the columns to work out like this:
A B C D F G H I K L M N O
| 4 | 4 | 5 |

However depending on which month it currently is it may be this:
A B C D F G H I J L M N O
| 4 | 5 | 4 |

Or:
A B C D E G H I J L M N O
| 5 | 4 | 4 |

The reason I'm hiding these is that the data is coming from VLOOKUP information from whatever the current month is, and rather than leaving the 5th week column blank I was wondering if Excel would be able to just hide it with a formula somewhere in that column.

The spreadsheet would show all 12 months, keeping the current month first, and following by the next 11 months.

Thanks

JR

 

[SkipVought]

Hi,

1) Just HIDING a column will not do ANYTHING CONSTRUCTIVE for your lookup. Don't waste your time on hiding columns of data. Make the data CORRECT.

2) I would use REAL DATES and, if necessary, add a column for Week Number.

3) Your data ought to be formatted in TABULAR format -- ie...
[tt]
Date Week Value
[/tt]
From a TABLE FORMAT like this (yours may have several different values, or a single values with a TYPE that describes various values) you will be aboe to use a myriad of features that Excel offers to analyze and report your data -- for instance the PivotTable Wizard, that can REPORT in the format that you seem to want to incorrectly STORE your data in.



Skip,

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

 

[makavity]

Thanks again for your help, but what I'm doing would just make the spreadsheet print better. We don't use the typical calendar for my business. We use 4 weeks, 4 weeks, then 5 weeks. I know that I can make the column empty rather than having #N/A, but would like to be able to hide the column and unhide it when I'm working in a different week. Hiding the column would be constructive because I wouldn't be printing a blank column. It's about having a polished report to present, not just the math. I know how to make the math work. I'm looking to see if the feature exists.

All of the columns calculate based on the week I enter at the top of the page, so everything else is subject to change. Depending on how many weeks are in the current month on our calendar - it will give me the data for the week and recalculate the rest.

I'm using 1 spreadsheet, trying to update 1 tab each week once we get the data - then I can analyze it. If I can put the work in up front, there will be less to do each week.

Let's see if someone else may have a thought.

JR

 

[Tiglet]

Hi,

I created something similar when I worked for a company that had 4,4,5 periods. I did it all using HLOOKUPS and VLOOKUPS (i.e. including the headings). Therefore you have a data sheet which holds the data for all periods and then say a drop down box to select current period and it all pulls through from the data sheet onto the presentation sheet.

Any rows in the headings that need to be hidden because they pull references for lookup formulae can be permanantly hidden.

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[dcompto]

I hope no one minds my asking a question out of curiosity (and probably stupidity): Would "Custom Views" be an option?

 

[SkipVought]

JR,

One of the feature I was pointing out was the PivotTable. With your data in TABULAR format, a PivotTab ACROSS Days or Weeks or Months, BETWEEN whatever dates you choose in order to format a TARGETED report. It would AUTOMATICALLY, by virtue of the DATES, be able to accomodate the 5^th week situation.



Skip,

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