Excel 2007 : How to grab information from other sheets and place it in the main sheet

[tiggi]

Hi,

Refer to attached. I have a tab for a number of sports e.g. golf, tennis etc. Each sheet contains NAME, *** and SIZE. In sheet name 'Result', I need:


- column a - automatically lists all the names that appear in each sheet but only appears once
- column b - extracts what *** they are from any tab and places it in column b
- other columns - automatically set the tab names (sports) - one header per tab/sheet name e.g. golf
- beneath each of the sport headers, and if that name appears in that specific sport sheet, it puts the size in that cell for that person in that row


thank you so much.
i need to do this automatically because the original xls contains over 100 different sports and over 10,000 people records.

tiggi

[JBeaucaire]

I've placed this macro into the RESULTS sheet module. When you switch to another sheet, the come back to RESULTS, it will ask if you want to rebuild the data.... say YES.

Please Login or Register  to view this content.

[Marcol]

Perhaps another way

Please Login or Register  to view this content.

[tiggi]

Thanks guys, but I should have applied the original format. See attached. Could you please make it work for this attachment and create a new sheet 'Results'? all I need is the Email, ***, and then a column header for each sport with the size filled out per sport category only if that person exists in that sheet.

[JBeaucaire]

I don't see a new attachment

[tiggi]

Sorry, here you go. I've removed the sensitive information.

[JBeaucaire]

You've introduced non-trivial randomness to your sheets, introduced more sheets to be ignored, removed the single source of "uniqueness" from the detail sheets(names are now in two columns) AND decided you don't even need those names.

I can fix the macro but first you're going to design some commonality into your sheets.

1) If you want put "notes" at the top, use a text box so the "sport" sheets themselves can be consistent across the entire workbook.
2) On each sport sheet:
--- the data should start on the same row on each sheet, titles on the same row
--- names in two columns? Ok, add another column (out on the right, if you wish) that concatenates the names together...
=TRIM(D5 & " " & E5)
....and copy down so the names will appear as a single value as you fill out your forms.
--- Decide WHAT on each of these sheets can be "checked" to make sure this sheet should be evaluated. How about:
"If B4 = "Team Name", then process this sheet" ?


Create an updated design that is consistent as shown, and I'll tweak the macro to gather your data for you.

[tiggi]

Thanks, sorry I was trying to avoid posting the original (which was handed down to me from someone else). Here's what I've done:
1.) Remove the blank header rows. The headers now sit on the 1st row.
2.) The titles that I need to focus on are now consistent on every sheet:
- First name = column D
- last name = column E
- concatenate = column f (first name and last name combined - formula values)
- *** = column H
- t-shirt size = column O
3.) If B2 = "Team Name"

Thanks and much appreciated.

[JBeaucaire]

You've got a couple of sheets with the wrong columns for EMAIL and Shirt size, add in the missing DATE OF BIRTH column on those pages to fix. Then try this:

Please Login or Register  to view this content.