Copy rows to new sheet based cell reference, not value

[michaelberrier]

I have created a workbook to track continuing education hours/credits
for different certifications. There are two variable components: There
are blanks on one sheet for up to eight different certifications, and
then four other workseets to track different kinds of CE seperately
(classroom, cbt, etc). The certifications are variable throughout the
entire workbook based on a cell reference on the certification sheet
(A1:A8) The values of these cells enter automatically on the
subsequent sheets with checkboxes below to indicate if the CE is
applicable to the corresponding certification above. The checkboxes
are linked to the cells immediately beneath them, so a running hour
total at the bottom for the individual certification is based on a
SUMIF (TRUE) formula.

I would like for the user to be able to generate a summary of all the
individual training items that are applicable to the individual
certifications in sheet1(A1:A8).

On the sheet with the individual training items, they are laid out like
this:
Date Class Hours =Sheet1(A1) =Sheet1(A2) etc...

The cell references are easy, the variable will always be a cell
reference(Sheet1-A1) and the criteria for inclusion will be a TRUE
value, but I've got no idea how to put that together.

I've likely not explained this very well, so anyone willing to help can
download the work in progress to see what I need here:
http://upload2.net/page/download/GO4PVOyARRiR9uE/Training+Records+Blank.xls.html

Sheet1 in my file would be 'Printable Report'. The certifications are
entered into A9:A24 on that sheet. They are referred to on the
subsequent four sheets in cells D1:K1.

For arguments sake, lets say "EMT" is in Sheets(Printable Report)"A9"
and there is one entry on each of the subsequent pages (Classroom, CBT,
Journals & Magazines, Other Training) that is applicable to the EMT
certification (indicated by a checked box and relative TRUE value in
the underlying cell).

I want the user to be able to select "EMT" (the value of A9) from a
combobox in a userform (I know how to do that) and then have a routine
that would select all the rows on the subsequent sheets that have a
TRUE value in that column (would be column D in our example) and copy
them to another worksheet (will be 'Individual Summary')

In short, the routine would need to find the matching rows and copy
them into the next available empty row of the output sheet.

Sorry for the double post, but I didn't get a lot of bites in the other
group.

Thanks to all

 

[Guest]

I feel certain that somebody will answer the specific problem.
However I would approach the problen by designing any issues out.
Having looked at your workbook I observed that what you had was a log of
training events (CBT, etc) and you had created a seperate log for each event
type. I would approach this from the perspective of creating a single event
log with the different categories and build on your idea of creating specific
"reports" for each type of training, e.g. via pivot tables. I would observe
that you have constrained yourself by trying to input the data in the format
of reports rather than grouping the data in a way to best take advantage of
Excel features like lists, data validation, etc. This has led to quite a
complex spreedsheet (difficult to maintain and upgrade?). Also this proposed
approach means there is only one source of the data which you manipulate,
i.e. no need to copy. As you are planning to have userforms you obviously
have considerable Excel skills already and this approach should not be too
radical.

HTH

Chris_UK

 

[michaelberrier]

Chris,

Wow...I really made that a lot harder than it had to be.

Thanks for saving me from myself.