copying column headers from multiple sheets into a "summary" sheet, based on criteria

In my head this seems like it should be straight forward. A breakdown of what I have (with attached spreadsheet):

I have 8 sheets all with the first 2 columns containing the same information.

Within each of these sheets there are columns with unique IDs (series of letters followed by a number which corresponds to the sheet that ID can be found on, see below)

screengrab1.jpg

What I'm looking for is a formula/array formula? that will search through row 2 of all 8 sheets, and create a new table in "summary sheet 1" where the column headers are those where the area ID's contain a "1" e.g. M1, LLL1 etc (with the idea that I'd then create 3 other sheets for ID's containing a 2, 3 and 4). and to also copy across the associated information from row 3

Once this has been achieved I then need a way for all the numbers in the respective columns to also be copied across into the summary sheet i.e. 30,119 and 149 etc.

One extra snag. I need the summary sheet to also update in the event that additional area ID's are added to one of the sheets. e.g. if in the attached screen grab I add an area ID labelled NNN1 in Column BB, I need the formula to add the info from NNN1 to "Summary Sheet 1"

The general outcome I'm looking for is 4 summary sheets with all of the Area IDs with the same number in each sheet. Column A and B will also be copied across into each of the Summary sheets so that I can sum the total number of each plant type as well as the final total for the number of plants in that sheet.

I hope all of this makes sense and that there's someone out there in the world who knows what I'm after and can help!

any help would be massively appreciated (I'm doing this for the Mrs, I'm semi proficient with excel but even this has got me stumped!)

Cheers,

Dan

Hi Richard,

Thank you for your reply, I think I half understand where you're coming from but your response may be hindered by something which I'd originally left out of my original post as I didn't think it would affect what I was trying to do. The values in the columns are calculated using an index/match formula.

Each botanical name may be associated with more than one category and a percentage of that category is made up by that particular plant type. see below (Code "max"- Row 62, falls under both HN1 and HN2 with 15% allocated to each)

screengrab 2.jpg

The formulae which calculates those numbers, once it has identified the Category is - Column H * Column M(result of index/match formula when entering the searchable item in cell AV3 as an example)* SQM area.

So as per your original post, I wouldn't be able to enter any data into the "values" column that you've suggested I create.

To provide a bit more background, there are 4 plans that have been divided up into different areas. Each of these areas needs a unique ID associated with it (Area ID) and the SQM area needs to be worked out from the plan and then entered into the spreadsheet.

The knowns in the spreadsheet are:
the number/m2 for each plant,
the botanical name,
the Category (which are in actual fact sub-categories of each of the tabs i.e. LSE1 is a subcategory of Low Shrub Edge see spreadsheet).

The purpose of the original sheet was to be able to show the area IDs which all have the same category associated with it, and to then calculate the total number of each plant type and subsequently total number of plants for each Area ID.

What I want to do now is to take all of that data that has already been calculated and present it by those Area IDs mthat all fall within the same plan (identified by having a 1, 2 ,3 or 4 in the Area ID).

Hope this helps and further explains what I'm trying to get at?

And thanks again Richard for your swift reply, I really appreciate it.

Cheers,

Dan