Struggling to produce report based on horizontal table.

Vampyr

New Member
Joined
Nov 3, 2019
Messages
4
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.



A1
1/1/20
2/1/20
3/1/20
4/1/20
5/1/20
6/1/20
7/1/20
8/1/20
9/1/20
10/1/20
11/1/20
12/1/20
13/1/20
14/1/20
John
AL
AL
AL
R
R
R
Dave
AL
R
R
R
LD
Fred
AL
AL
R
R
R
LD
AL
Tom
LD
R
R
R
AL
AL
Harry
R
R
R
LD
AL
AL

<tbody>
</tbody>


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.



Name
Fred
<selected font="" list<="" down="" drop="" a="" from=""></selected>
Date
Code
Details
3/1/20
AL
Anual Leave
4/1/20
AL
Anual Leave
12/1/20
LD
Lieu Day
13/1/20
AL
Anual Leave
/\
/\
This is the issue populating the information into these two columns
This bit is fine, as long as I can populate column 1 and 2

<tbody>
</tbody>


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

Well-known Member
Joined
Apr 4, 2009
Messages
3,660
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
Code:
=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
Code:
=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

New Member
Joined
Nov 3, 2019
Messages
4
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,660
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

New Member
Joined
Nov 3, 2019
Messages
4
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
 

Forum statistics

Threads
1,077,784
Messages
5,336,327
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top