Hello sir/Madam, In the attached file QP Booklet record sheet (Red Tab) I want to put the rollno in three columns (Highlighted with yellow color)but am not able to set the accurate formula. The same formula is working with the rest of the sheets. Kindly solve this problem.
Thanks and regards
Prince
Formula is not working in excel sheet

 2StarLounger
 Posts: 157
 Joined: 02 Mar 2015, 17:00
Formula is not working in excel sheet
Last edited by prince on 19 Nov 2021, 13:33, edited 1 time in total.

 Administrator
 Posts: 72885
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Formula is not working in excel sheet
On the other sheets, the columns with the Roll nos are adjacent: columns B, C and D. On the Q.Booklet Record sheet, they are in nonadjacent columns B, D and F. So you must adjust the 4*COLUMN()6 part.
Because the distance between the columns is twice as large, 4 must be decreased to 4/2 = 2.
For column B, 4*COLUMN()6 = 4*26 = 2.
To get the same result for 2*COLUMN() we must use 2*COLUMN()2 since 2*22 = 2.
So:
=IF(INDEX('RollNo'!$A$1:$A$2001,12*INT(ROW()/12)+MOD(ROW()7,4)+2*COLUMN()2)="","",INDEX('RollNo'!$A$1:$A$2001,12*INT(ROW()/12)+MOD(ROW()7,4)+2*COLUMN()2))
Because the distance between the columns is twice as large, 4 must be decreased to 4/2 = 2.
For column B, 4*COLUMN()6 = 4*26 = 2.
To get the same result for 2*COLUMN() we must use 2*COLUMN()2 since 2*22 = 2.
So:
=IF(INDEX('RollNo'!$A$1:$A$2001,12*INT(ROW()/12)+MOD(ROW()7,4)+2*COLUMN()2)="","",INDEX('RollNo'!$A$1:$A$2001,12*INT(ROW()/12)+MOD(ROW()7,4)+2*COLUMN()2))
Regards,
Hans
Hans

 2StarLounger
 Posts: 157
 Joined: 02 Mar 2015, 17:00
Re: Formula is not working in excel sheet
Thank you sir, It's working perfectly.
Thanks a lot
with regards,
Prince
Thanks a lot
with regards,
Prince