Forum Discussion
How to duplicate column based on date?
What you're seeking to do is probably not all that difficult. It IS difficult, however, to work with images alone and test a formula. So, in effect, you're asking us to re-create the workbook/spreadsheets on our own so as to test any formulas we might suggest. [There may be a few of the experts here who are confident enough to write a formula without testing it; I'm not one of them!]
You could help us help you--your workbook is already using no names, so there is no issue of privacy being violated--by posting the actual workbook(s) on OneDrive or GoogleDrive, with a link pasted here that grants access
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....