referencing data from another spreadsheet within same workbook

[vtentarelli]

This is a continuation of a project with many parts. Previous threads provided valuable information. Hoping to get more help.
Problem: Workbook contains several spreadsheets, each containing data relating to specific equipment. Current arrangement has each spreadsheet with over 3000 lines copied from the Main Page, where all data is entered. Because of the formulas below, many of the cells in Column C of each spreadsheet are blank. Only those cells that match the criteria contain data. Column D on each spreadsheet collects all pertinent data and sorts from largest to least. I want to reduce size of each spreadsheet by eliminating copying all the dates and all the TAPC values from Main Page, and just include only those TAPC values specific to equipment. Many of the formulas were provided through this forum. I tried the following formulas, all without success. Either I got error messages that stated I had too many arguments for the formula, I get an error message that highlights a parenthesis, or I got the largest number for the Year regardless of Source.

=IF(AND(MATCH("Person*",'Main Page'!N$3:N$5000,0),YEAR('Main Page'!B$3:B$5000)=E$3),LARGE('Main Page'!D$3:D$5000,D11),"")
This produced the largest value for the specified year without factoring the source.

=IFERROR(LARGE(IF(YEAR('Main Page'!B$11:B$5000)=E$3,'Main Page'!D$3:D$5000),D11),"")
This produced a blank. If I replace blank at end of formula with "NO" I get "NO"
Edited workbook provided.

[Glenn Kennedy]

Your explanation wasn't clear. It is easier to figure it out if you tell us exactly what you want, instead of telling us what did not work!!

I've made a guess at what you want...

In People, B11, copied down;

=IFERROR(INDEX('Main Page'!B:B,SMALL(IF(LEFT('Main Page'!$N$3:$N$20,6)="Person",ROW('Main Page'!$N$3:$N$20)),ROWS(People!$B$11:B11))),"")

and in E11, the same, except that B:B is D:D.

Both are array formulae.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

If this is incorrect, please explain what you want to appear where and (preferably) why.

[vtentarelli]

I have not had a response to this thread which makes me think that my explanation is confusing. I will attempt to rephrase my problem more clearly. Please refer to the spreadsheet, "Tanks, 250 Gal" in the attached file for the following discussion. I had to eliminate most of the rows to decrease the size. The original spreadsheet, "Main Page" as well as all spreadsheets in the workbook, have close to 3000 rows.

I have a workbook with approximately 30 spreadsheets that logs all microbial data obtained from sampling various pieces of equipment. Each spreadsheet compiles data for a specific equipment group. All data are entered onto the spreadsheet, "Main Page." The sample code for each entry identifies, among other things, the date and type of equipment. Each spreadsheet copies the relevant data from Main Page and performs various statistical analyses. The formula in Column B of each spreadsheet copies and pastes the data specific to the piece of equipment onto the individual spreadsheet to the same row as that in Main Page. So if the result for June 5, 2015 for the 250 gallon tank was entered on row 1590 on Main Page, it will paste onto row 1590 of the spreadsheet. Therefore each spreadsheet has as many rows as Main Page. Column D of each spreadsheet then sorts the data in Column B in descending order with the largest value listed first.

I want to combine the formulas in Column B and Column D in "Tanks, 250 Gal" to capture the relevant data specific to the year and piece of equipment directly from "Main Page" without having to copy and paste the data onto each spreadsheet, thereby allowing me to delete column B on each spreadsheet and decreasing the size of the file. My objective is to list only the number of rows equal to the value in cell E7, "Data Points."