Lightkeepr
New Member
- Joined
- Apr 6, 2021
- Messages
- 27
- Office Version
- 365
- Platform
- Windows
Book1 | ||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | |||
1 | ||||||||||||||||||||||||||||||||||||||||||||
2 | ||||||||||||||||||||||||||||||||||||||||||||
3 | ||||||||||||||||||||||||||||||||||||||||||||
4 | ||||||||||||||||||||||||||||||||||||||||||||
5 | ||||||||||||||||||||||||||||||||||||||||||||
6 | ||||||||||||||||||||||||||||||||||||||||||||
7 | Jul-21 | Jun-21 | May-21 | Apr-21 | Mar-21 | Feb-21 | Jan-21 | Dec-20 | Nov-20 | Oct-20 | Sep-20 | Aug-20 | Jul-20 | Jun-20 | May-20 | Apr-20 | Mar-20 | Feb-20 | ORIGINAL | |||||||||||||||||||||||||
8 | ||||||||||||||||||||||||||||||||||||||||||||
9 | ||||||||||||||||||||||||||||||||||||||||||||
10 | ||||||||||||||||||||||||||||||||||||||||||||
Sheet1 |
I have a sheet where I have columns of data by months and the month that column represents is always on row 7 of the sheet. Sometimes there is 2 months, sometimes there is 36 months. What I am trying to do is have the sheet always show the columns for the most recent six months (07/2021 back to 02/2021) and then the next five end of quarter months (in this example it would be 12/2020, 9/2020, 6/2020, 03/2020) and the ORIGINAL column.
I've been trying to think of a way to achieve this but get stuck. I had thought of putting in a helper cell in row 1 that would be something along the lines of
[in cell E1]
=IF(DATEDIF(E7,$E$7,"M")<=5,"SHOW","N")
and have the vba look at each column and if it listed 'SHOW' that column and the one next to it were visible and if it listed "N", that column and the one next to it were hidden.
But where I next get tripped up would be is looking at the other dates greater than the current + five back (or before Feb 2021) to establish which are the previous 5 end of quarter dates plus the "ORIGINAL" column.
Ideally I don't want to have a helper cell and it would have the VBA look at it and hide the columns Q:R, U:X, AA:AD, AG:AJ, AM:AN in this instance and if a new month was added in at column E:F and everything pushed to the right, it would reevaluate the dates and adjust which are shown and which are hidden.