PDA

View Full Version : Populate Excel Template from another worksheet



noobinnola
03-30-2013, 06:55 AM
Hey all, New here and looking for help as I am not sure even how to approach the problem.

I have an excel template created by an external program and I need to populate it then import template back into the program. The program will only accept the template.

The program creates little quiz's.

In the attached workbook, the "template" tab shows a sample of what the program creates, the number of questions varies from 1 to whatever. In this case it is a Multiple Choice quiz, with each question having 4 possible answers.

the constants in the template are:
Cell A1 is always the same
Cell A2 contains the question number, next question number is in Cell A22, etc
Cell B2 must always have the 90002 identifier
Each question spans 20 possible rows
The heading is typed in cell c1
Row 2 col C:G are merged and the question is in C2
in Col C rows 3 & 4 are merged, 5 & 6 are merged, etc to row 20
in Col D rows 3 & 4 are merged, 5 & 6 are merged, etc to row 20

The 9 pairs of Merged Cells in Col C (C3 & C4, C5 & C6, etc) can only contain a "Y" or be blank.
The "Y" represents the correct answer as shown in corresponding Col D.
For this quiz only the first 4 cell pairs in Cols C & D are needed.
The "Y" is actually in the odd numbered rows of each merged cell pair (3, 27, 47, 69, etc)
Cols C & G are left as they are
Row 21 is blank
Row 22 is next question

The data lives on sheet2, named Data
Col A is the Question Number on Sheets("template")
Col A on Sheets("data") is the question that goes into C2 on Sheets("template")
Col C on Sheets("data") is the answer that goes into Col D on Sheets("template")
Col E on Sheets("data") is the Cell on Sheets("template") that needs a "Y".

Notes:
If a quiz has 100 questions and each question has only 4 choices, then 25 of the correct answers should be in each of the 4 merged cell pairs in range D3:E20 (for Question1, D23:E40 for Question2, etc)

The "random" entries I will fill in later by hand unless there is some way to populate them following the pattern of 4 sets of 25 cell pairs (out of 100 questions). T

The program will randomize the order of the questions every time it is started, so that "answers" are not grouped in 25's. Quest 1 might be answer A, Quest 2 might be Answer b, etc)

I have filled the first 2 questions to show pattern. The unused/blank entries need to have nothing in them.

I have no clue where to begin with this!!!!:banghead::help:bug:

Should each question and appropriate span of rows be defined as an "area"? how do I select that defined area? how would I move to the next area?

or should I try and create an array like array(sheets("template").Cells(1,2), Cells(1,22),,,,)) vlookup sheets("data!A1") for a matching Quest# and then try to populate the c and d cells of sheets("template")?

I have looked at about 900 examples of how to copy paste date from one sheet to another and I cannot find one that comes close to this problem.

At this point I don't even know what to Dim and what to Set and if I should create named ranges to try and pass to an array..

please help to get me on the right path.

SamT
03-31-2013, 06:17 AM
:115:

noobinnola
03-31-2013, 09:44 AM
@SamT

thank you for that information. Now I just have to figure out how to use it:doh:

I will post what I come up with as I cannot be the only person trying to figure out
how to make this work.

One question off of the top of my head, since I assume you actually wrote code to test the statements you posted, did the Merged cells in the template cause you any problems?

thanks again

Mike