Row/Index Function

[mulholm]

=IF(ROWS($A$28:A28)>COUNTIF(Utilisation!C62:C68,"Paid Breaks"),"",INDEX(Utilisation!A62:A68,SMALL(IF(Utilisation!C62:C68="Paid Breaks",ROW(Utilisation!C62:C68)-ROW(Utilisation!C62)+2),ROWS($A$28:A28))))

Is there any way to amend this formula so that it searches all of column "C" for the word "Paid Breaks" and returns the corresponding namein column "A".

I have managed to get it to work if the rows in column "C" were a constant, i.e "C62:C68" as per above forumla however the rows will change depending on how many people i have in that day so is not a constant.

Therefore i need it for the whole column rather than specific rows.

 

[James006]

Hello,

If you need to get dynamically the last row used in your C Column, you can test :

=SUMPRODUCT(MAX((ROW(Utilisation!C1:C200))*(Utilisation!C1:C200<>"")))

Hope this will help

 

[mulholm]

is that how the whole formula would look or is that just replacing sections of the formula above.

 

[Aladin Akyurek]

Define in Formulas | Name Manager the following names:

Crange as referring to:

=Utilisation!$C$62:INDEX(Utilisation!$C:$C,MATCH(REPT("z",255),Utilisation!$C:$C))


Arange as referring to:

=Utilisation!$A$62:INDEX(Utilisation!$A:$A,MATCH(REPT("z",255),Utilisation!$C:$C))


Now implement:


=IFERROR(INDEX(Arange,SMALL(IF(Crange="Paid Breaks",ROW(Arange)-ROW(INDEX(Arange,1,1))+1),ROWS($1:1))),"")

Don't forget applying control+shift+enter, then copying down.

 

[mulholm]

Thanks for getting back to me.

i think my explanation was a bit confusing basically what i have is 2 sheets ("Stats" and "utilisation")
What i need to do is the following.
Search the utilisation sheet for the word "Paid Breaks" and then return the name of the person who took a break.
The Formula i put above works if the word "Paid Breaks" were always located in C62:C68 however this is not always the case. The row is a variable but it will always be in column "C"
I need the above formula altered to allow it to work with any row in column "C".

 

[Aladin Akyurek]

Thanks for getting back to me.

i think my explanation was a bit confusing basically what i have is 2 sheets ("Stats" and "utilisation")
What i need to do is the following.
Search the utilisation sheet for the word "Paid Breaks" and then return the name of the person who took a break.
The Formula i put above works if the word "Paid Breaks" were always located in C62:C68 however this is not always the case. The row is a variable but it will always be in column "C"
I need the above formula altered to allow it to work with any row in column "C".

Have you tried the suggestion?

 

[mulholm]

Yeah I tried, it didn't seem to do anything. it just printed the formula in the cell instead of actually doing anything

 

[Aladin Akyurek]

Yeah I tried, it didn't seem to do anything. it just printed the formula in the cell instead of actually doing anything

Select the formula cell. Run the Replace option from the ribbon with:


Find what: set to =

Replace With: set to =

Click OK.

 

[mulholm]

I've tried formula again and it is now showing blank.

=Utilisation!$A$62:INDEX(Utilisation!$A:$A,MATCH(REPT("z",255),Utilisation!$C:$C))

The section A62 not going to be the same every day. One day it may be A1. another day it may be A45.
Same with Crange.

 

[Aladin Akyurek]

What is the current range in C and in A? When these ranges change, do they change at the end or at the start or at both the start and the end?