One sheet is the combined total of the 2 other sheets

I mean there are ways to get around that but I cant really help much in this hypothetical space in the forum.

From a high level, what I do is build ONE standardized data source, then have TWO sheets for different reporting so you only have to maintain one list. This avoids the problem you are correctly concerned about.

You can get around this, but it just means at least double the work to maintain both lists and aggregate them to one list in the background (which I have also done, and felt it was a half measure haha).]

EDIT:

I guess you could whip up a macro that when run, clears the background list, then combines both lists into a new/refreshed background list.

Basics of spreadsheet design, is to have 1 sheet for all inputs, then have other sheets for reporting/analysis/summary etc. This allows for the use of easier/regular formulas and also opens up the possibility of using Pivot Tables.

I suggest you take another look at how you are doing things and see if you can adapt the suggestions made.

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like?

Thanks for the file

Assuming your column headings are inthe same sequence (and all are there), you can use this ARRAY formula in Members A2, copid down and across...
=IFERROR(INDEX('COMPLETE LIST'!A$2:A$10,SMALL(IF('COMPLETE LIST'!$A$2:$A$10="Member",ROW($A$2:$A$10)-1),ROWS($A$1:A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

What exactly does your formula look like? If you are using entire columns as ranges, change that to actual references.

There are a few ways to "hide" 0's, probably the simplest would be...
=IFERROR(IF(INDEX('COMPLETE LIST'!A$2:A$10,SMALL(IF('COMPLETE LIST'!$A$2:$A$10="Member",ROW($A$2:$A$10)-1),ROWS($A$1:A1)))=0,"",INDEX('COMPLETE LIST'!A$2:A$10,SMALL(IF('COMPLETE LIST'!$A$2:$A$10="Member",ROW($A$2:$A$10)-1),ROWS($A$1:A1))),""))

As far as CSE is concerned, others can explain it far better than me...

These are Array formulas - if you Google you will find plenty of documentation, eg:

http://www.cpearson.com/excel/ArrayFormulas.aspx

OK lets try a different approach, using a helper column in sheet1.

I put this in L2, copied down...
=A2&COUNTIF($A$2:A2,A2)

Then in Member sheet, use this, copied down and across...
=IFERROR(INDEX('COMPLETE LIST'!A$2:A$7,MATCH("Member"&ROW(A1),'COMPLETE LIST'!$L$2:$L$7,0)),"")

Change Member to Boosters for the next sheet.

This should speed things up for you