Concatenate and Summarize Strings Across Sheets

[smn]

Hi!

So I'm facing this issue where I have mutiple sheets (named in a way that follows a certain pattern - A1, A2, A3, A4, etc.). I need to summarize the information for each sheet in one row.

The data from each sheet looks like the below. I need to extract information from only certain columns in this table (the named ones in this case - i.e., for Fruits I will need the price, number, and size of apples, oranges, and lemons all in one row for A1. The columns and rows I need to extract from are identical across the sheets. The header values (Rows 2, 8, 19) and column A are also identical across the sheets. The only difference is in the values of the prices, numbers, sizes, etc. across the sheets.

I will need to develop a summary sheet ("Summary") whereby the information is summarized as the below:

This needs to continue on for all the tables (i.e., columns with Vegetables_Cucumber_Color, Vegetables_Cucumber_Weight, Vegetables_Cucumber_Country, Vegetables_Tomato_Color, etc..

Any help to solve this would be extremely helpful!

 

[Herakles]

Do your tables have a naming convention?

 

[smn]

Do your tables have a naming convention?

No, they are all strings that are unrelated to each other. The only similarity is their name is always placed at the top left corner of the tables.

 

[Herakles]

Are these actually Excel tables or are they just ranges?

 

[smn]

Apologies on the terminology I used, but they are just ranges and not Excel tables