Forum Discussion
How to duplicate column based on date?
However, here is the link: https://docs.google.com/spreadsheets/d/18PfLib8g3TZTRufQ2R58uMlNYlEvuj8i/edit?usp=drivesdk&ouid=104862895633418872032&rtpof=true&sd=true
Thank you for looking.
- https://www.dropbox.com/scl/fi/nt17q8i1103162sf4oxt3/Flight-Matrix-Redacted.xlsx?rlkey=e5o8ym0tlyv6ie038wandk8gw&dl=0 Let's see if a drop box link is better.
That file is not one I'd want to work with. I've written a formula that does the first thing you requested.
=INDEX($C$9:$AAZ$40,ROW()-8,MATCH($B$1,$C$1:$AAZ$1))It works, but I'm not happy with it, largely because I'm not comfortable with the layout--the design--of the worksheet, to begin with.
Part of that discomfort with the design is that I'm unfamiliar with the whole situation, what lies behind this. But more fundamentally,
- It seems to me that you're mixing data collection (input) with the output or reporting side of things, by asking that column B pull data from whatever column equals today's date; and you're doing that all on one sheet (Matrix).
- You're also arraying days sequentially on the horizontal plane, which means you'll run out of room long before you would if you turned that 90 degrees: Excel accommodates a lot more rows than it does columns. So I'm not sure what the history is here, why you chose to do it that way; perhaps replicating a process that has worked on paper (I've seen that happen many times).
I wasn't fully able to follow your second request, but perhaps you can adapt what I did for this first to your need. It may be that your "Roster" sheet is the separate output sheet I'd want to see....
Thank you for the input.
1. My original plan was to have everything on Matrix pull to the B column, so today is always visible despite wherever you are looking calendar and forecasting wise. Then I would just have that B column duplicated over on roster. However, when I made the original post, I did not sufficiently state that
.
2. We do the days horizontally as we generally only need about a year or two, then erase everything and start again. Its far easier for people to scroll left and right, especially with hiding columns on old dates until its time to delete everything.
As for the code itself, thank you. It worked wonders. I did change it to:
=INDEX($C$4:$AAZ$40,ROW()-3,MATCH($B$1,$C$1:$AAZ$1))This allows it to cover the Rows 4-8. I am not sure what the ROW()-3 does, but it took me a while to figure out that adjustment.
Secondly, I am able to just have the code in Roster =Matrix!B(whatever number) which fills in the column for the day. I see in the code provided, it uses:
MATCH($B$1,$C$1:$AAZ$1)Is there any possible way to use that code on the first page and have it reference Matrix! to fill in the array and range? B1 on Roster is the date block, so the $B$1 can stay, but after that is it possible to do something like MATCH($B$1,MATRIX!$C$1:$AAZ$1) or to that effect?