Formula is not working in excel sheet

prince
2StarLounger
Posts: 171
Joined: 02 Mar 2015, 17:00

Formula is not working in excel sheet

Post by prince »

Hello sir/Madam, In the attached file QP Booklet record sheet (Red Tab) I want to put the roll-no 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
Last edited by prince on 19 Nov 2021, 13:33, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula is not working in excel sheet

Post by HansV »

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 non-adjacent 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*2-6 = 2.
To get the same result for 2*COLUMN() we must use 2*COLUMN()-2 since 2*2-2 = 2.
So:

=IF(INDEX('Roll-No'!$A$1:$A$2001,12*INT(ROW()/12)+MOD(ROW()-7,4)+2*COLUMN()-2)="","--",INDEX('Roll-No'!$A$1:$A$2001,12*INT(ROW()/12)+MOD(ROW()-7,4)+2*COLUMN()-2))
Best wishes,
Hans

prince
2StarLounger
Posts: 171
Joined: 02 Mar 2015, 17:00

Re: Formula is not working in excel sheet

Post by prince »

Thank you sir, It's working perfectly.
Thanks a lot
with regards,
Prince