How to be obtained used answer sheets serial numbers

F

Farooqui Noor

Board Regular
Joined
Dec 31, 2019
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
Dear Sir,
I work in the exam cell, where my job is to keep track of the serial numbers of the answer sheets sent to exam halls. I have created an Excel sheet for this purpose, which I'm attaching for your reference. In column B, the block numbers are listed. The serial numbers of the answer sheets sent to the exam halls are in columns C and D. After the exam ends, the serial numbers of the answer sheets returned from the exam halls are in columns G and H.

The number of exam blocks can vary each day—sometimes 10, sometimes 12, or even 15 blocks. In cell N4, the serial numbers of all the answer sheets issued are listed in this format: 27223053-27223072,27223075-27223082. Similarly, the serial numbers of the returned answer sheets are listed in cell R4. The serial numbers of the supplements, highlighted in blue, are in cells N8 and R8.

In the yellow highlighted cells, U4 and U8, I need to write the serial numbers of the used answer sheets in the same format as shown in N4 and R4.
Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1
2Sr.No.Block No.Sr. No. of Answer Books issued to examinees in this sessionSr. No. of Answer Books remains unused in this sessionTotal Answer Books Used
3FromTo TotalGrand TotalFromTo TotalGrand Totalissued Answer Books Serial NumbersReturn Answer Books Serial Numbers from exam HallUsed Answer Books Serial Numbers
41106272230532722307220282722307227223072132527223053-27223072,27223053-27223072,27223075-27223082,--1,--1,27223085-27223109,27223109-27223108,5327223072-27223072,27223072-27223072,27223078-27223079,--1,--1,--1,--1,3N7-R7
5272230752722308282722307827223079227223075-27223082,27223078-27223079,
6-1-1--1,--1,
7-1-1--1,issued Supplements Serial Numbers--1,Return Supplements Serial Numbers from exam HallUsed Supplements Serial Numbers
825182025182455251823251824223251820-251824,251820-251824,--1,--1,--1,5251823-251824,251823-251824,--1,--1,--1,2N11-R11
9-1-1--1,--1,
10-1-1--1,--1,
11210727223085272231092525-102527223085-27223109,--1,
122722310927223108-127223109-27223108,--1,
13-10-100--1,--1,
14Total Number of Issued Main 32 Pages Answer Books53Returned Main Answer Books350
15Total Number of Issued Supplements5Returned Supplements23
16Total Number of Issued Main 32 Pages Answer Books & Supplements58Total Returned 553
17
1
Cell Formulas
RangeFormula
F4,J4F4=SUM(E4:E7)
N4,R4N4=IFERROR(CONCATENATE(M4,""), "") & IFERROR(CONCATENATE(M5,""), "") & IFERROR(CONCATENATE(M6,""), "")& IFERROR(CONCATENATE(M7,""), "")& IFERROR(CONCATENATE(M11,""), "") & IFERROR(CONCATENATE(M12,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")
O4,S4O4=F14
F8,J8F8=SUM(E8:E10)
N8,R8N8=IFERROR(CONCATENATE(M8,""), "") & IFERROR(CONCATENATE(M9,""), "") & IFERROR(CONCATENATE(M10,""), "")& IFERROR(CONCATENATE(M13,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")
O8,S8O8=F15
F11,J11F11=SUM(E11:E12)
K4,K8,K11,K13:K16K4=SUM(F4-J4)
D4:D13,H4:H13D4=SUM(C4+E4)-1
J13,F13J13=SUM(I13:I13)
J14J14=SUM(AA4:AA13)
J15J15=SUM(AB7:AB13)
J16,F16J16=SUM(J14:J15)
M4:M13,Q4:Q13M4=CONCATENATE(C4,"-",D4,",")
F14F14=SUM(Y4:Y13)
F15F15=SUM(Z7:Z13)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Dear Sir,
I issued a total of 53 answer sheets in both block numbers 106 and 107. The serial numbers of these sheets are written in cell N4. Three answer sheets were returned from the exam hall, and their serial numbers are noted in cell R4. This means that on that day, 53 minus 3 equals 50, so a total of 50 answer sheets were used. I need to write the serial numbers of these 50 answer sheets in cell U4, which means subtracting the number in cell R4 from the number in cell N4. Similarly, on that day, a total of 5 supplements were issued, noted in cell N8, and 2 supplements were returned, noted in cell R8. This means that 5 minus 2 equals 3, so a total of 3 supplements were used. I need to write the serial numbers of these 3 supplements in cell U8, which means subtracting the number in cell R8 from the number in cell N8. Remember, the method of writing serial numbers should be the same as in cell N4 and cell R4.
 
Upvote 0
You must log in or register to reply here.

Similar threads

VinceF
  • Question
Circular Reference Error
Replies
3
Views
280
VinceF
VinceF
J
  • Solved
trending value projections without counting sundays
Replies
2
Views
167
KRice
K
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
1K
felixstraube
F
MrDB4Excel
  • Solved
Capture Text in a Range
2
Replies
12
Views
657
MrDB4Excel
MrDB4Excel
etaf
  • Solved
SORT - an Extract List based on criteria - BUT 2019 Version ONLY - NOT 365
Replies
9
Views
543
Georgiboy
Georgiboy

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top