Thread: Manipulating data throughout a workbook

I would need to see the formulas you are using in the summary and your criteria for getting data from the master.

Best thing would be to attach your workbook. If it has sensitive data, can you boil it down to show how it works?

I'm not completely following your idea, particularly what you mean by "dump" and what you are going to hide.

Here are some hints that hopefully will take you the right direction but I'm sorry that I don't have the time for a more complete ready-made solution.

In the attached, I have used the sheet "Blue Grass Summary" as an example, and have updated the formula in L9. (I noticed that you have some protection turned on so I could not copy the sheet nor create a new one.)

My strategy is to have a single set of formulas that will work for all worksheets, and without specifying which rows of the master sheet you want included. This gives the most flexibility for future changes.

The first thing I did was to put a formula in A1 that provides the name of the region for that worksheet. It took advantage of your naming convention, and takes the part of the worksheet name that indicates the region. (You can use this elsewhere, such as cell D7, instead of changing every sheet.)

Then in L9 I modified the formula to look at all rows, instead of a hard-coded set of rows. I had to add the criterion in the COUNTIFS functions to also include it only if column B in the source also matched the region name. And to do that, I used the reference to A1, which now contains the region name. Now you no longer care what is in what row, or even what order they are in.

If you work your way through the other formulas on the sheet you can make similar changes, you can create a generic sheet that can be copies, then all you have to do is give it the correct name and everything else is automatic. Some of these formulas are not simple, I will grant you, but I think some are overcomplicated. For example, in L11 there is an array formula

{=SUM(
('TN''s Survey Data by Region'!AE2:AE27>(1.5))*
 (('TN''s Survey Data by Region'!AE2:AE27<=(3)) +
SUM(('TN''s Survey Data by Region'!BC2:BC27>(1.5))*
 (('TN''s Survey Data by Region'!BC2:BC27<=3)))))}

which can be replaced by a simpler SUMIFS (or the sum of two SUMIFS).