How to duplicate column based on date?

Question

I have a locked down workstation and can only use the basic Office 365 tools. No scripts or VBA.

There are two requests:

I have a sheet named "Matrix" that is just columns of a date range and rows of employees.

A is their name. *B* is the column I would like to make a formula for. C is July 1. D is July 2. E is July 3. So on and so forth...

Row 1 is the date. Rows 2-8 are a few rows of basic information about each column (percent out of office/number of people) followed by my workers. Lines 9-40 are my employees. I have it like this to schedule, forecast, and track where each person is on every date.

I would like to simply have Column B display whatever is in the column for the current date. If the date is December 29th and that is (for example) column HK, i would like it to be displayed in Column B as well.

The second request is based off the first one.

I would like to transcribe, copy, display, something along those lines the same data from the columns in "Matrix" to a sheet named "Roster".

"Roster" has a selectable date in B1. (A1 simply says "Date").

I would like to fill in the names from "Matrix" A9:A40 to a column and range of my choosing once I design the page. I would like another column to display the information in the column specified by the date in B1.

Thank you for any help or assistance.

mathetes · Answer

Casen_&nbsp;&nbsp;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!]&nbsp;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

mathetes · Answer

Casen_2&nbsp;&nbsp;I think you need to create a link that allows access. I get this error message&nbsp;

mathetes · Answer

Casen_2&nbsp;Is this, on the Roster page, what you mean?&nbsp;As for what ROW() means, and the associated subtraction there, there may be a more elegant way to retrieve the information, but when one uses the INDEX function, as we're doing,=INDEX($C$4:$AAZ$40,ROW()-3,MATCH($B$1,$C$1:$AAZ$1))you need a way to say "into this cell, where the formula is, put the information that you findin the large array that is named first, &nbsp;$C$4:$AAZ$40in this numbered row of that large array (in this case, we want the first row of $C$4:$AAZ$40)but the formula itself is in cell B4, in the fourth row of the Excel spreadsheet (ROW() gives the result 4)so we have to subtract 3 from the result of ROW() to get 1in the column from the range $C$1:$AAZ$1 where the date&nbsp;MATCHes the &nbsp;date in cell $B$1INDEX and MATCH combine as a powerful technique to retrieve information from a two dimensional array such as you have. Often, maybe even normally, one uses MATCH twice within an INDEX function, once to specify the row, again to specify the column. In this case, the row could be determined by starting with the row in which the formula appeared.&nbsp;Here's a reference that you can use to develop a deeper understanding of the combination.&nbsp;

mathetes · Answer

You're very welcome. That website is a good one for general research into various Excel functions...

casen_2 · Answer

I apologize. My work computer and phone have different accounts and my phone is auto logging in a different one.

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.

Forum Discussion

How to duplicate column based on date?

9 Replies

Resources