Lookup Data Across Several Sheets

[ithoughtiknewit]

Hello!

I have about 50 sheets that contain a small amount of data. I have a name in A and the data I need in E. For example, "Jane Smith" is in A, and E says she worked virtually that week. Sheet 2 is the following week, and so on. I need to find out how many times Jane worked virtually or in-person. Part of the issue is that whoever made the sheet didn't keep any type of order. Sometimes Jane is the first on the list, sometimes the fourth, and sometimes she appears three times for the same thing (I only care about 1). Lastly, sometimes she has a "FO" or some other indicator after her name that doesn't matter. I need this for Jane and a few others.

My hope was to run something like =COUNTIFS(Sheet2!A4,"John Baker",Sheet2!E4,"In-Person") but I am at a loss as to repeat it 50 times and the fact it has a range because the name can move. I also need it to "contain" the name because of other characters.

Thanks as always!

 

[NickFru]

Hello - you may want to try this formula

On a main header sheet, set yourself a heading called Reference Sheets then undeneath this title, list the following (you can add lots more if you so wish)

Say this range was E2:E4
Sheet 2
Sheet 3
Sheet 4

On each of the lookup sheets, your names are say in column A and deployment type in column D, so within the INDIRECT formula on the summary page, you can enter from A2:A100 & E2:E100 (this range can be expanded more if needbe).

Main Header Sheet

Name (Just type this In Cell A1)	John Baker (Drop Down Data Validation) Cell			Reference Sheets
Deployment Type (Just type this In Cell A2)	In-Person (Drop Down Data Validation)			Sheet2
Count Of Records (Just type this in Cell A3)	2 (See Formula Below)			Sheet3
				Sheet4

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$E$2:$E$4&"'!$A$2:$A$100"),B1,INDIRECT("'"&$E$2:$E$4&"'!$E$2:$E$100"),B2))

As mentioned, once you keep adding more sheets, as long as it is structured the same per individual worksheet an you list these additional sheets to the reference sheets list which is then picked up in your range, hopefully this formula is what you need.