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
Formula is not working in excel sheet
-
- 2StarLounger
- Posts: 171
- 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: 78585
- 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 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))
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
Hans
-
- 2StarLounger
- Posts: 171
- 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