Is there a way to hide a row or column in excel without using VBA?
I know of several approaches using Macro (VBA) to show / hide columns and rows in Excel, but I cannot find a way to find this using a formula or conditional formatting. Of particular interest is Excel 2007 - but I would be interested to know if anyone has succeeded in doing this in any version of Excel.
For those looking for a background image, I have a data spread with dates on top and captions down the first column. I would like to specify the date box (on another sheet) as two cells with drop dates (months), which then show / hide the corresponding columns in the data sheet.
a source to share
This is not an answer to your question directly (I very much doubt there is one), but if you have organized your data with a data table, the data will go down instead of horizontally like a simple OLAP cube, perhaps you can add a criteria column which checks if the data points are between your two thresholds and then organizes your view in a pivot table.
Combining lists and tables like this is really powerful if you don't like VBA, but VBA really is the solution for this kind of thing, unfortunately.
a source to share
You probably want a third sheet (or use a second sheet where you put dates) and use the HLOOKUPS or MATCH functions.
1) Use a function to dynamically populate the column headings you need based on the date range. So in B1 you put = StartDate
2) cell C1 would be = IF (B1 + 1 <= EndDate, B1 + 1, "") and copy across an appropriate number of columns based on your estimate of how long people are likely to search
3) Cells B2 down and to the right will use your preference for the combination of HLOOKUP () or OFFSET (MATCH ()) combined with IF to determine if you want this. for example B2 would = IF (B $ 1 <> ", HLOOKUP (B $ 1, DataRange, string (B2), false)," ") and copy down and across as needed.
However, the suggestion above is about transferring data and then using a pivot table with a helper column next to your data (ie IF (AND (date> = StartDate, date <= EndDate), Show, Hide) and then filter "Show" in the page field.
a source to share