Copy data from cells on info sheet to cells on data sheet based on date.

Hi @CASEY BRYANT,

Thank you for posting your question in the Microsoft Q&A forum.

According to your request, you want to type week number or date value, then it will pull from the info sheet the data of start date - end date and the list of departments need to be counted in that week.

I suggest you can try following this guidance to see if this would help resolve your concern:

• In my Excel worksheet, I've created a test info sheet as below

• In the Main sheet, I will have a set up like this: cell A2 is used for inserting week number/date value, cell B2 for normalized week.

• In cell B2, I will use this formula:

=LET(input,A2, wk_from_date, MOD(WEEKNUM(input,1)-1,52)+1, wk_from_number, MOD(INT(input)-1,52)+1, weekNum, IF(ISNUMBER(input), IF(input>40000, wk_from_date, wk_from_number), wk_from_number), startDate, INDEX(Info!$B$2:$B$54, MATCH(weekNum, Info!$A$2:$A$54,0)), "Week " & weekNum & "  " & TEXT(startDate,"mm/dd/yy") & " - " & TEXT(startDate+6,"mm/dd/yy"))

• Then if I input the week number or random date value in cell A2, Excel will refer to data in the Info sheet and export the date according to the input data.
• Please note that if you input general number (e.g 48) but cell A2 shows the value as date, it means the cell is stuck as Date formatting and you will need to change it back to General. However, the formula in cell B2 still works well.

• To export the departments, in cell A6, use this formula:

=LET(input,$A$2,week,IF(input>40000,WEEKNUM(input,21),input), TRANSPOSE(XLOOKUP(week, Info!$A$2:$A$54, Info!$C$2:$G$54, "No match")))

Important note: The formulas use cells and data ranges refer to the structure of my testing worksheet. You need to change them based on your Excel file structure to make sure the formulas work properly.

You can try the formulas and let me know if it works for you. If not, we can work together to resolve this.    

Thank you for your patience and your understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. 

I look forward to your response.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

So, I ended up using xlookup, but I got it to work! Your idea did help me figure it out, so thank you!