Placing Data into a Repeated Form

[Big Bill]

I am the secretary for our high school football officials chapter and every year we provide our members with a summary of relevant information from the previous year. I have all the information in various tabs in a large workbook, but I have pulled all the relevant information into one tab (Member Summary). I would like to pull the data (marked in yellow on the first sheet) into the same summary sheet for each member (66 in all). The data in orange was pulled from another source in the workbook, but that will never change, hence the $absolute position.

I also need any data pulled from AD - AO which is a zero to show as a blank in the summary. I wrote a formula for that, but it may not be the best solution.

I realize I could copy the sheet 66 times and substitute the data, but that is ridiculous and saves me no time, at least not this year, even though I would have a template for future years.

I have uploaded my file, hopefully it will open to the Member Summary tab, if not, it is the last one in the workbook.

Thanks for your help.

 

[Luke M]

Hi Bill,

Summary sheet looks very nice. I am a little unsure about what you are requesting. We could make all of the yellow cells use lookup type formulas, where you give the name at top, and rest of informations populates off of that. You still have to choose the name. Is that what you are wanting, or more?

If need to create reports, we could also write a macro that could loop through your name list. Ie, put a name at top, formulas do their calculations, print to pdf (or something), pick next name, repeat. Is that more like what you are wanting?

 

[Big Bill]

Hi Bill,

Summary sheet looks very nice. I am a little unsure about what you are requesting. We could make all of the yellow cells use lookup type formulas, where you give the name at top, and rest of informations populates off of that. You still have to choose the name. Is that what you are wanting, or more?

If need to create reports, we could also write a macro that could loop through your name list. Ie, put a name at top, formulas do their calculations, print to pdf (or something), pick next name, repeat. Is that more like what you are wanting?

Luke,

Thanks for the compliment about the Summary Sheet. I guess what I'm asking is a way to fill the same form 66 times with the information in yellow, whether that is through a macro (which I know very little about) or a mail merge type of program. A lookup would be acceptable, if all I have to do is pick the name, at least it saves me some time. My ultimate hope, though is create the form and have the data fill it in 66 times, maybe I'm being overoptimistic.

 

[Luke M]

Hi Bill,

Please see the attached, which takes the summary sheet and adds lookup formulas to all the yellow cells. I also placed a combo box dropdown over the Name cell. The advantage of that little tool is that it contains a list of all the people, and you can click on it and either a) select a name from dropdown, b), start to type the name, and it autofills, or c) use up/down arrow keys to scroll through list. Note that the formulas update in real-time.
The box itself won't print, so don't have to worry too much about it's apperance.

Next, I could write a quick macro that would go through the list. However, need to know what to do after we select a name. Are we printing to pdf, saving the single sheet into a new workbook, creating a jpg, etc.?

 

[Big Bill]

Luke,

That is awesome! As far as what's next, I have to print a summary for each person to give to them at our first meting next week. I don't know what would be easier in regards to a macro (no experience there). I'd love to just open it and hit print and have all 66 print without asking each one to print, but beggar's can't be choosers.

Also, can you give me a brief explanation of the formula you wrote for the ratings chart?

=VLOOKUP($E$1,$Z:$AO,4+(INT((COLUMN(A$1)-1)/2)+1),)

Thanks,
Bill

 

[Luke M]

Bill,

No need to beg, a printing macro is fairly short. See attached. Button is below the page. Click it, and go grab a coffee. Will print to whatever is the current selected printer.

For the VLOOKUP, we are looking for the name in E1, and we are searching the table in Z:AO. However, depending on which cell were were in, want to return information from a different column. I don't like having to make a bunch of different formulas manually when I can have XL do the work for me.
So, need to make a counter. The other trick was that with the merged cells, the formula was getting copied to every 2nd cells (first in col A19, C19, E19, etc.) So, need a counter that will go 5, 6, 7, but when I keep moving 2 columns.
So, we use the COLUMN function as the basis for the counter. This will return the column number, and increments as we copy to the right. I want it to change every 2 columns I move, so we devide by two and take the integer.
Normally, this would change:
1,2,3,4,5
into
0,1,1,2,2
But look! I only have 1 zero at the beginning. This is why we subtract 1 before dividing. Now, we'll convert:
0,1,2,3,4
into
0,0,1,1,2
Finally, as I said, we want to return the 5th column from the table in Z:AO. So, we add 5 (4 at the front, and 1 at the end...no real reason, just how I wrote it. Could have also written:
5+INT((COLUMN(A$1)-1)/2

does that make sense?

 

[Big Bill]

Luke,

Thanks for all your help, I appreciate it.

If I understand correctly, The COLUMNS formula you wrote would not have been necessary, if I didn't have merged cells (A19 & B19, C19 & D19, etc.).

If the cells weren't merged, I could have just used =VLOOKUP($E$1,$Z:$AO,5,) and =VLOOKUP($E$1,$Z:$AO,6,) and =VLOOKUP($E$1,$Z:$AO,7,), etc. for each of the subsequent cells, correct?

 

[Luke M]

Almost. If they weren't merged, we would not have needed to be as elaborate, but I still don't want to have to manually create different formulas for each cell. Could just do:
=VLOOKUP($E$1,$Z:$AO,COLUMN(E$1),)
The column function evaluates to 5. Copy this to right, formula becomes:
=VLOOKUP($E$1,$Z:$AO,COLUMN(F$1),)
and column function evaluates to 6.

All in all, the point was to not have to manually create different formulas, but rather type 1 formula, and let XL do the work.

 

[Big Bill]

Almost. If they weren't merged, we would not have needed to be as elaborate, but I still don't want to have to manually create different formulas for each cell. Could just do:
=VLOOKUP($E$1,$Z:$AO,COLUMN(E$1),)
The column function evaluates to 5. Copy this to right, formula becomes:
=VLOOKUP($E$1,$Z:$AO,COLUMN(F$1),)
and column function evaluates to 6.

All in all, the point was to not have to manually create different formulas, but rather type 1 formula, and let XL do the work.

Your way is quicker and easier (and much cooler), but my way would have worked if I didn't know how to use the COLUMN function, correct?

 

[Luke M]

Correct, both ways work just as well.