Formula to Convert Week Chart to Day Chart

[willhh3]

Hi!

I'm not sure how to get started on this. I have an on call rotation that runs from Wednesday to Wednesday each week. The data i track on is based on weeks (Wed-Tues) and I need to see if I can some how extract the data to a second table and break it out day by day with the person's name next to each day. I'm attaching a workbook that should illustrate it better than I'm able to describe. I believe I need to use formulas because I have a requirement to maintain table 1 (in my sample) as is.

Any help getting going would be appreciated.

Whh3

[63falcondude]

Try this in L4:

=IF(COUNTIFS($A$4:$A$11,$J4,C$4:C$11,"x"),L$3,"")

Then drag the formula to the right and down.

[MarvinP]

Hi Whh3,

I did some restructure of your data and then a Pivot Table with this new design. See if you can deal with the Pivot Table answer. You need to group the dates by 7 days to show like the attached.

PT for Schedule.xlsx

[63falcondude]

If you want the formulas to reference the table instead of an absolute range, try these instead:

L4 =IF(COUNTIFS(Table1[Week],[@Week],Table1[Joe],"x"),Table2[[#Headers],[Joe]],"")
M4 =IF(COUNTIFS(Table1[Week],[@Week],Table1[Sam],"x"),Table2[[#Headers],[Sam]],"")
N4 =IF(COUNTIFS(Table1[Week],[@Week],Table1[Sue],"x"),Table2[[#Headers],[Sue]],"")
O4 =IF(COUNTIFS(Table1[Week],[@Week],Table1[Jack],"x"),Table2[[#Headers],[Jack]],"")
P4 =IF(COUNTIFS(Table1[Week],[@Week],Table1[Jane],"x"),Table2[[#Headers],[Jane]],"")

[63falcondude]

Thanks for the rep!

I'm assuming that one of the given solutions worked for you?

[willhh3]

Thanks and it did help. Was wondering if I could get the names to line up in one column and use the table references instead. The table reference will cover when I add move dates and people. Attaching an updated workbook with a second tab showing what I'm thinking. Thanks again!