T
Tinä
Hi Frank,
The Formulae below tells me how frequently the ROOMS are being used b
different Groups of people and with the aide of the Formula in th
Helper Column how long they have not been used.
My Helper Column "U" increments one Row at a time and says:
Check from Current Row back to beginning of my "V" Range: if the Roo
was used previously give me the Last (MAX) time it was used b
returning the relevant Row Number of the (text based) Group Nam
(Executive, Trainees, Manager, Graphics, etc.) that used the Room Last
The Helper Column "U" then passes this data to the Formula in Colum
"T" - it checks for the criteria within the specified Range and doe
the COUNT calculation using the Current Row where the Group Nam
appears, if at all, back to the Last (MAX) Row where it appeared
subtracts Last Row Number from the Current Row Number.
Apologies for any confusion.
The Formulae below works ok on non-filtered data but when I us
Autofilter the results are not as they should be, because the result
also include the non-filtered data rather than just the Filtere
Visible Cells.
Using the Formulae below, I can obtain the required data from th
Columns in a non-filtered state.
Column T:
=IF(COUNTIF($V$10:$V10,$V11),COUNTA(INDIRECT("V"&U11+1&":V"&ROW()))-1,COUNTA($V$10:$V10))
Column U (helper column):
=MAX(IF($V$10:$V10=$V11,ROW($V$10:$V10)))
Column V:
Text Data
Further help appreciated.
Thanks
Tinä
The Formulae below tells me how frequently the ROOMS are being used b
different Groups of people and with the aide of the Formula in th
Helper Column how long they have not been used.
My Helper Column "U" increments one Row at a time and says:
Check from Current Row back to beginning of my "V" Range: if the Roo
was used previously give me the Last (MAX) time it was used b
returning the relevant Row Number of the (text based) Group Nam
(Executive, Trainees, Manager, Graphics, etc.) that used the Room Last
The Helper Column "U" then passes this data to the Formula in Colum
"T" - it checks for the criteria within the specified Range and doe
the COUNT calculation using the Current Row where the Group Nam
appears, if at all, back to the Last (MAX) Row where it appeared
subtracts Last Row Number from the Current Row Number.
Apologies for any confusion.
The Formulae below works ok on non-filtered data but when I us
Autofilter the results are not as they should be, because the result
also include the non-filtered data rather than just the Filtere
Visible Cells.
Using the Formulae below, I can obtain the required data from th
Columns in a non-filtered state.
Column T:
=IF(COUNTIF($V$10:$V10,$V11),COUNTA(INDIRECT("V"&U11+1&":V"&ROW()))-1,COUNTA($V$10:$V10))
Column U (helper column):
=MAX(IF($V$10:$V10=$V11,ROW($V$10:$V10)))
Column V:
Text Data
Further help appreciated.
Thanks
Tinä