Collating different types of data into one

BungleZippy

New Member
Joined
Dec 11, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Afternoon everyone,

We have a dilemma in trying to create a 32 multi-choice question exam from a bank of questions.
All the information comes from different spreadsheets and within each subject there are differing levels of questions.
After selecting the required number of questions from the right chapter with the correct level we then need the answers randomised in each question, therefore not having answer A every time.!!

Trying to find formulas to populate the exam for us.

We have a spreadsheet that gives us the chapters, the level of the question and the number of questions (MCQ) required (samples all attached).
Also attached is a bank of questions in ATA chapter order, with levels 1 to 3.

Can anyone please help??

Kind regards

BungleZippy
 

Attachments

  • Chapters.jpg
    Chapters.jpg
    88.2 KB · Views: 8
  • Exam.jpg
    Exam.jpg
    77.3 KB · Views: 8
  • Questions.jpg
    Questions.jpg
    107.6 KB · Views: 8

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Might be best if you provided some dummy data for people to work with, no one wants to retype stuff from images, use the XL2BB tool available on this site
 
Upvote 0
Afternoon @Kerryx

I would love to provide that information with XL2BB but our work IT policy will not allow me to install the Add in. This is very frustrating for all i know!! Sorry
 
Upvote 0
Trying to upload mini sheets!!

Questions.xlsx
ABCDEF
1ChapterLevelQuestionCorrect AnswerAnswer BAnswer C
201Which version of the A109 introduced composite fuselage structures?AW109SP A109SA109E
301How is the Main rotor system of the A109 best descibed?Fully articulated main rotor with four composite blades and a titanium Main Rotor HeadFully articulated main rotor with four composite blades and a alloy steel Main Rotor HeadSemi rigid main rotor head with four alloy and composite main rotor blades
401What are the primary differences between the A109 E/S and the SP?The SP introduces a composite fuselage structure and 4 axis auto pilot systemThe SP introduces a composite fuselage structure and 3 screen glass ****pit PFD/MFD systemThe SP introduces shrouded tail rotor and integrated mission computer FMS system
5211How many defusers in the passenger compartment are supplied by the nose ram air intake?246
6212The A109SP avioinc cooling system has;3 fans, a Low speed detector and “EFIS FAN” caution light on EDU #1. 4 fans with low speed protection triggering the MCLLow speed detector and caution switch on instrument panel.
7213In case of ENG FIRE:All AW109 E/S/SP DAU will close both shut off valvesA109E DAU will close only affected engine SOV. A 109S/SP DAU will close only affected engine SOV.
8212Where is the ECS over temperature switch located?In the plenum chanber duct assemblyIn the Pilot vent tubeIn the Co-pilot vent tube
9213How are the engine SOVs operatedOpened by a pressure differential controlled by a solenoid and sprung closed with bleed air pressure assisting in closureClosed by an electrical solenoid and opened by bleed air.Sprung closed and opened by an electrical motor
10213What is the function of the Economy solenoid valveWhen switched to ECON the economy solenoid valve is de- energized and opens, this increases the actuating pressure supplied to the turbine inlet control valve, reducing turbine speedWhen switched to ECON the economy solenoid valve reduces bleed air pressure supply by aadjusting the venturi tube in the plenum chamberWhen switched to Econ the fan motor speed is reduced, using less energy from the 28V main bus
11212The basic ventilation system configuration consists of:Ram air intakes located in the nose and on the forward MRGB cowlingRam Air Intakes located in the nose between pilot and copilot windshield and an upper deck air scoop which are both interconnectedTwin ram air ducts below the windscreen and an upper ram air duct feeding the cabin.
12213What happens if there is a bleed air over temperature conditionThe bleed air over temp switch provides an earth to the ECS control relay and removes power from the engine SOVsThe bleed air over temp switch provides an earth to the DAU which closes the engine SOVsThe bleed air over temp switch removes the earth from the engine SOVs causing them to open.
13213What indication does the pilot have of ECS funcitonalityOverhead switch positions and ECS advisory messageOverhead switch position, ECS advisory message and ECS FAIL caution messageOverhead switch position only
14223When GA mode is activated at less than 55knts what does the aircraft do?Commands a level attitide and zero rate of climbCommands a nose up attitide and a 750 feet per minute rate of climbCommands a level attitide and zero to 750 feet per minute rate of climb
15223Where do the autopliot cyclic position sensors get their power from ?The pitch position synchros are powered by the 26VAC bus The pitch position resolvers are powered by the 115VAC bus The pitch position Synchros are powered by the 28VDC bus
16223What colour are the EADI FD decoupled annunciations?MagentaGreenCyan
17223In attitude hold mode disengaged….?The trim actuators do not attempt to recentre the linear acutatorsThe linear actuators are centred throughout the operating rangeThe FD system only shows sterring advisories
18223The electro magnetic clutch within the parallel actuators is….....?Energised on to anchor the flight controlsDe-energised to anchor the flight controlsEnergised by an internal microswitch to anchor the flight control
19222How is FCC cooling achieved?Via two blower fans on the FCCVia ducting from the avionics cooling fansVia FCC case fins and ambient conditions in the avionics compartment
20223What is the purpose of the collective control overide functionProvides low height and power limit protectionAllows the pilot to override the FCS collective controlPrevents the FCS collective control from over powering the pilots commands
21223What impact on the AP system would an inverter 1 failure have?No impact as the number 2 inverter will supply both systemsThe autopilot system degrade with non critical functions disabledThe number 1 channel will trip offline and number 2 will take over full control
22222How many linear actuators are there in the A109SP auto flight system?653
23223How many trim actuators are there in the A109E auto flight system?342
Sheet1
 
Upvote 0
Exam.xlsx
ABCDEFGHI
3Question 1
4A)
5B)
6C)
7
8Question 2
9A)
10B)
11C)
12
13Question 3
14A)
15B)
16C)
17
18Question 4
19A)
20B)
21C)
22
23Question 5
24A)
25B)
26C)
27
28Question 6
29A)
30B)
31C)
Exam A
 
Upvote 0
TNA.xlsx
ABCDEFGH
1ATA ChapterDescriptionContentsLevelTeaching HoursTimeDelivery MethodMCQ Numbers
20HELICOPTER GENERAL101:00Classroom lecture1
300-00 General00:15
400-10 General Maintenance 00:15
500-20 Safety00:15
600-40 Technical Publications (A109E/S/SP)00:15
721Air Conditioning303:00Classroom lecture3
821-21 Air Distribution00:30Hangar
921-22 Avionics Ventilation00:30
1021-40 Heating00:45
1121- 50 ECS (Environmental Control System)01:15
1222Autoflight308:00Classroom lecture8
1322-00 General Description (AW109SP)01:30Hangar
1422-10 Autopilot (A109E/S/SP)03:30
1522-20 Speed-Attitude Correction (A109E/S/SP)03:00
1623Communications308:00Classroom lecture8
1723-00 General (AW109SP01:00Hangar
1823-10 VHF system (Collins VHF/AM-22A) (A109E)01:00
1923-10 VHF system (King KX165A) (A109E)01:00
2023-10 VHF system (GARMIN 430) (A109S)01:00
2123-10 VHF system (GARMIN 530) (A109S)01:00
2223-10 VHF/AM system (VHF/AM Collins 4000E) (AW109SP)00:45
2323-50 AUDIO INTEGRATING AND VOICE COMMAND SYSTEM (A109E)00:45
2423-50 AUDIO INTEGRATING AND VOICE COMMAND SYSTEM (A109S)00:45
2523-50 AUDIO INTEGRATING AND VOICE COMMAND SYSTEM (AW109SP)00:45
2628Fuel Systems304:00Classroom lecture4
2728-00 GENERAL00:30Hangar
2828-10 STORAGE00:45
2928-11 AUXIILIARY FUEL TANK00:15
3028-20 DISTRIBUTION01:30
3128-40 INDICATING01:00
3229Hydraulic Power306:00Classroom lecture7
3329-00 GENERAL01:00Hangar
3429-11 #1 FLIGHT CONTROL HYDRAULIC SYSTEM01:00
3529-12 #2 FLIGHT CONTROL HYDRAULIC SYSTEM01:00
3629-20 UTILITY HYDRAULIC SYSTEM01:00
3729-31 FLIGHT CONTROL HYDRAULIC SYSTEM MONITORING AND INDICATING01:00
3829-32 UTILITY HYDRAULIC SYSTEM MONITORING AND INDICATING01:00
Sheet1
 
Upvote 0
Might be best if you provided some dummy data for people to work with, no one wants to retype stuff from images, use the XL2BB tool available on this site
@Kerryx

Ive managed to get IT to allow the add in.

Mini sheets attached in the replies.

Kind regards

BungleZippy
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,210
Members
453,023
Latest member
alabaz

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