Find out who is working today.

Hi,

See attached

I've reformatted your data so that you can use a Pivot Table

Select the date you're interested in from the drop down in B2 on the PT.

Here is a solution direct from your table. It's very complex though.

For People, use an Arrayed formula

Formula:

=IFERROR(INDEX(Sheet1!$A$2:$A$17,SMALL(IF(ISTEXT(INDEX(Sheet1!$B$2:$F$17,,MATCH($A$1,Sheet1!$B$1:$F$1,0))), IF(NOT(INDEX(Sheet1!$B$2:$F$17,,MATCH($A$1,Sheet1!$B$1:$F$1,0))="Vacation"), ROW(Sheet1!$B$2:$B$17)-ROW(Sheet1!$B$2)+1)),ROWS(Sheet1!$A$1:$A1))),"")

For Hours,

Formula:

=IFERROR(INDEX(Sheet1!$B$2:$F$17,SMALL(IF(ISTEXT(INDEX(Sheet1!$B$2:$F$17,,MATCH($A$1,Sheet1!$B$1:$F$1,0))), IF(NOT(INDEX(Sheet1!$B$2:$F$17,,MATCH($A$1,Sheet1!$B$1:$F$1,0))="Vacation"), ROW(Sheet1!$B$2:$B$17)-ROW(Sheet1!$B$2)+1)),ROWS(Sheet1!$A$1:$A1)),MATCH($A$1,Sheet1!$B$1:$F$1,0)),"")

See Attached.

Originally Posted by fsorto90

Is there any way to use a pivot table and leave the data the way it is?

In short, no. The pivot table needs the layout i showed.

If you think about it though the data is essentially no different, just laid out in a list rather than the table you showed. the important thing with a PT is to ensure that every column contains a unique set of information, which is why you can't have dated spread out across the columns.

Think about it though. A PT doesn't need any formulae or macros to work and it is so much more powerful since you can just drag and drop fields of information around and see different presentations and summaries should you have any numeric data.