I am working with the Exam cell of my school and I want a Seat allotment system.
I need something like https://chandoo.org/forum/threads/c...ting-cell-content-and-end-cell-content.36789/ but in my case, two changes are there.
1)I have 50 rows in one room and
2)The subject name may vary room to room so here the code should check subject name then check for starting and ending range.
I think I've solved the 1st problem with using the following code
=IF(ROWS($1:1<=MATCH(9.9E+307,D:D)+47,IFERROR(1/(1/TEXT(OFFSET(Sheet1!$A$4,MATCH(LOOKUP(9.9E+307,C$3:C3),OFFSET(Sheet1!$A$4,,MATCH(B$3,Sheet1!$A$2:$N$2,0),850),0)+MOD(ROWS($1:1)-1,50)-1,MATCH(B$3,Sheet1!$A$2:$N$2,0)),"[>"&LOOKUP(9.9E+307,D$3:D3)&"]\0;0")),""),"")
but I can't solve the 2nd one.
Here I am attaching a sample workbook for better understand.
Thank you in advance.
Sayandip Chattopadhyay
I need something like https://chandoo.org/forum/threads/c...ting-cell-content-and-end-cell-content.36789/ but in my case, two changes are there.
1)I have 50 rows in one room and
2)The subject name may vary room to room so here the code should check subject name then check for starting and ending range.
I think I've solved the 1st problem with using the following code
=IF(ROWS($1:1<=MATCH(9.9E+307,D:D)+47,IFERROR(1/(1/TEXT(OFFSET(Sheet1!$A$4,MATCH(LOOKUP(9.9E+307,C$3:C3),OFFSET(Sheet1!$A$4,,MATCH(B$3,Sheet1!$A$2:$N$2,0),850),0)+MOD(ROWS($1:1)-1,50)-1,MATCH(B$3,Sheet1!$A$2:$N$2,0)),"[>"&LOOKUP(9.9E+307,D$3:D3)&"]\0;0")),""),"")
but I can't solve the 2nd one.
Here I am attaching a sample workbook for better understand.
Thank you in advance.
Sayandip Chattopadhyay