Excel Index array where a if a criteria has ended start to view another column

[p85ki]

Hi

I am currently working on a spreadsheet that lists lots of data about where people are within each department.

Please see the below example that may help understand the issue:

Example:
A Lead Manager has 25 staff and lists 25 staff using the Lead_Manager formula below.
Due to his department being extremely busy, the Lead Manager has 6 extra staff for the next month and so the quieter departments have provided him 6 staff. He is now a temp manager for those staff.

The issue I am facing is that If I start off with the Temp_Manager formula, then I will not see the 6 team members in the Lead_Manager columns (C1 - C6) as the rows are scrolling downwards and not fixed on both side. If I start off with Lead_Manager, as that will be the bulk of the list, I will never see the secondary staff the Temp_Manager has.

These staff must be listed under their original manager so we are aware of the department they are in, and potentially recruit if required.

I could after 30 rows just place the singular Temp Manager Formula, which would be the short term solution.

I am seeing if anyone knows of a effective way to put these 2 formulas together to work or best way to resolve this (if an index is not the solution)?


Lead Manager Formula
=IF(LEN(TRIM(H$5))<1,"",
IFERROR(INDEX(Data_CEC_Name,SMALL(IF(Lead_Manager=D$5,ROW(Lead_Manager)-1),ROWS(Data!$C$1:$C1))),""))

Temp Manager Formula
=IF(LEN(TRIM(H$5))<1,"",
IFERROR(INDEX(Data_CEC_Name,SMALL(IF(Temp_Manager=D$5,ROW(Temp_Manager)-1),ROWS(Data!$C$1:$C1))),""))


Thanks

 

[jasonb75]

Maybe something like this (untested, may or may not work depending on data layout which can not be determined from a formula with named ranges and no visual example).

=IF(TRIM(H$5)="","",
IFERROR(INDEX(Data_CEC_Name,AGGREGATE(15,6,ROW(Lead_Manager:Temp_Manager)/(Lead_Manager:Temp_Manager=D$5),ROWS(C$1:C1))),""))

 

[p85ki]

Maybe something like this (untested, may or may not work depending on data layout which can not be determined from a formula with named ranges and no visual example).

=IF(TRIM(H$5)="","",
IFERROR(INDEX(Data_CEC_Name,AGGREGATE(15,6,ROW(Lead_Manager:Temp_Manager)/(Lead_Manager:Temp_Manager=D$5),ROWS(C$1:C1))),""))

Soert for the delay in a reply. Its been horribly busy along with my ISP playing up for most of the day.
I've had a play and this doesn't quote do it although ca see what you done.

I've decided to make it easier and split the 2 different sections up. At least that way if something does go wrong it isn't too complex for the next person to see thee issue

Thanks for taking a look at the formula.