Struggling to produce report based on horizontal table.

[Vampyr]

Hello All,

This is my fist post here so please accept my apologies if this doesn't followthe usual format for asking questions.

I have a worksheet which I use to monitor staff holidays and plan resourcesaccordingly. Part of this sheet is a table with a list of about 50 staff on theleft and the dates for the year across the top. In this table managers willenter annual leave etc to enable them to plan ahead, but also to keep a recordof this leave. In a simplified form it is laid out as per the example below.


[TABLE="width: 718"]
<tbody>[TR]
[TD="width: 80, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]A1
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]1/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]2/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]3/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]4/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]5/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]6/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]7/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]8/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]9/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]10/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]11/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]12/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]13/1/20
[/TD]
[TD="width: 63, bgcolor: transparent, align: right"]14/1/20
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]John
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dave
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]LD
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Fred
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]LD
[/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tom
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]LD
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Harry
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: #92D050"]R
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]LD
[/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"]AL
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


This all works fine from dropdowns and populates the restof the sheet regarding resources etc

The problem is exporting this data should member of staff wish to seetheir records as we cannot show them the main sheet due to data protection asthey would also be viewing other staff members details.

What I would like to do is have a separate worksheet within the workbook whereI can select the staff members name from a dropdown and it will show a table orlist of what they have taken throughout the year and have booked in advancealready. This would need to be displayed something like the example below. Itwill also need to ignore certain codes such as "R" in the aboveexample as the sheet would be much too large to make any kind of senseotherwise.


[TABLE="width: 308"]
<tbody>[TR]
[TD="width: 62, bgcolor: transparent"]Name
[/TD]
[TD="width: 114, bgcolor: transparent"]Fred
[/TD]
[TD="width: 233, bgcolor: transparent"]<selected font="" list<="" down="" drop="" a="" from=""></selected>
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Date
[/TD]
[TD="bgcolor: transparent"]Code
[/TD]
[TD="bgcolor: transparent"]Details
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] , align: right"]3/1/20
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]AL
[/TD]
[TD="bgcolor: transparent"]Anual Leave
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] , align: right"]4/1/20
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]AL
[/TD]
[TD="bgcolor: transparent"]Anual Leave
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] , align: right"]12/1/20
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]LD
[/TD]
[TD="bgcolor: transparent"]Lieu Day
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] , align: right"]13/1/20
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]AL
[/TD]
[TD="bgcolor: transparent"]Anual Leave
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]/\
[/TD]
[TD="bgcolor: transparent"]/\
[/TD]
[/TR]
[TR]
[TD="width: 176, bgcolor: transparent, colspan: 2"]This is the issue populating the information into these two columns
[/TD]
[TD="width: 233, bgcolor: transparent"]This bit is fine, as long as I can populate column 1 and 2
[/TD]
[/TR]
</tbody>[/TABLE]


I have tried to get to grips with index match but am notsure I am getting the syntax correct or even if this would be the correct wayforward.

Hopefully there is a simple solution to this and I look forward to any help anyof you can offer as I a currently stumped.

Thanks in advance

 

[James006]

Hello,

With your table located in Sheet1 in range A1:O6

and in Sheet2 ...the name Fred in cell B1 ...

Two array formulas for cell B4 and cell A4:

cell B4

=IFERROR(INDIRECT("Sheet1!"&SUBSTITUTE(ADDRESS(1,SMALL(IF(OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),1):OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),14)<>"",IF(OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),1):OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),14)<>"R",COLUMN(OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),1):OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),14)))),ROW()-3),4),1,"")&MATCH($B$1,Sheet1!$A$1:$A$6,0)),"")

cell A4

=IFERROR(INDIRECT("Sheet1!"&SUBSTITUTE(ADDRESS(1,SMALL(IF(OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),1):OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),14)<>"",IF(OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),1):OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),14)<>"R",COLUMN(OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),1):OFFSET(Sheet1!$A$1,(MATCH($B$1,Sheet1!$A$1:$A$6,0)-1),14)))),ROW()-3),4),1,"")&1),"")

Hope this will help

 

[Vampyr]

Hi James,

Thank you so much for this, I've tested it with the example sheet that I posted in the forum and it works perfectly, does exactly what I was struggling to achieve.

However, I cannot get it to work with the actual workbook.

The data table I the main workbook is much larger in the range of K15:NO60 still in sheet1.

I've tried adjusting the formula range above but it's returning completely erroneous results so I know I'm missing something but not sure what?

If you have the spare time could you look at it for me and adjust it accordingly, but also let me know where I've gone wrong as I'd like to learn not just plagiarise someone else's work.

This is what I amended A4 to

=IFERROR(INDIRECT("Sheet1!"&SUBSTITUTE(ADDRESS(1,SMALL(IF(OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),1):OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),369)<>"",IF(OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),1):OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),369)<>"R",COLUMN(OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),1):OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),369)))),ROW()-3),4),1,"")&1),"")

This is B4

=IFERROR(INDIRECT("Sheet1!"&SUBSTITUTE(ADDRESS(1,SMALL(IF(OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),1):OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),369)<>"",IF(OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),1):OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),369)<>"R",COLUMN(OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),1):OFFSET(Sheet1!$K$15,(MATCH($B$1,Sheet1!$K$15:$K$60,0)-1),369)))),ROW()-3),4),1,"")&MATCH($B$1,Sheet1!$K$15:$K$60,0)),"")

Look forward to your reply

Craig

 

[James006]

Hello,

Glad to hear formulas do produce your expected results ...

In order to adapt them to your real-life workbook, could you clarify

Everything is located in Sheet1 ...

1. what is the range (A1:O6) of your reference table ?

2. what is the cell (Fred in cell B1) of your requested search ?

3. what are the cells where you need your array formulas ? ( cells A4 & B4 )

 

[Vampyr]

Hi James,

Thanks for the reply, appreciate your help with this.

The Range for the reference table is in sheet1, (K15:NO60)

The cell for "Fred" is still in B1 on sheet3

The cells for the array are also in sheet3 at A4 and B4 autofilled to approx 30 rows directly below, I may move them around slightly, not a problem if they have to be locked to that position though.

What did I get wrong in my attempt to adapt it? I worked out that the formulas are using an absolute reference somewhere but couldn't see where to change it?

Thanks again

Craig

 

[Vampyr]

Hi James,

Did you get a chance to look at this?

Craig

 

[sandy666]

are you able to use Power Query?