Hide with formula

Microsoft Excel 2007 home and student
February 7, 2010 at 07:44:49
Specs: Windows Vista
I have a spreadsheet that shows 4 week of a month.
Is it possible to just show the current week and hide the other weeks by going off the days date?

See More: Hide with formula

Report •

February 7, 2010 at 08:40:39

Without any information about what data you have or how it is organized, its pretty hard to give you good suggestions.

One way would be to have all the monthly data in one worksheet and then on another worksheet have this week's dates and a lookup function returning the results for this week, using the month's data on the other sheet.

If Sheet1 contains dates in column D, from row 5 to row 35
and there are two columns of associated data in columns E and F

On Sheet2 create a list of dates for 7 days, in cells B2 to B8
Put =TODAY() in B2
then in B3 put =B2+1 and drag to extend the formula down to cell B8
Format cells B2 to B8 in a suitable date format.

In Cell C2 put this:=VLOOKUP($B2,Sheet1!$D$5:$F$35,2,FALSE)
in cell D2 put this:=VLOOKUP($B2,Sheet1!$D$5:$F$35,3,FALSE)
Drag to extend both formulas down to row 8.

You will now have 7 days worth of data drawn from the whole month's data.

This is just a basic idea, and there are several ways you could do this.


Report •

February 7, 2010 at 09:35:53

As far as I know, you can't use formulas to do things like hide rows or columns.

You could do that with a macro based on dates, and add a button to hide all rows or columns not in current week and make rows or columns in the current week visible.

You could also make that happen when the workbook is opened, using the Workbook Open event.


Report •

Related Solutions

Ask Question