Extract multiple rows from multiple worksheets with Index formula

Hello all,
I could really use your help in figuring out a formula to pull each Color entry (see sample below, also attached) from 14+ worksheets into one spreadsheet. I don't think Vlookup will work in this situation so I tried using the Index formula but I think I am doing something wrong because it didn't work. I saw an answer posted by "NBVC" to a very similar question in the forum. This is the formula that worked for the other person...

[If(ROWS($A$1:$A1)>COUNTIF(Main!$C$2:$C$28,"Mike"),””,INDEX(Main!$B$2:$B$28,SMALL(IF(Main!$C$2:$C$28="Mike",ROW(Main!$C$2:$C$28)-ROW(Main!$C$2)+1),ROWS($A$1:$A1))))]

I couldn't get it to work in my worksheet (after changing the worksheet and cell references). If my workbook name is "TestBook” and worksheets are called "Lamp, Book, Fruit, and Sky" then how would I change this formula to work for me assuming I can use this formula for my question. Any help would be appreciated.

Sample data table:
A B C D
1 Color Code Quantity Price
2 Red 1 1 $5.00
3 Green 2 0 $-
4 Purple 3 1 $15.00
5 Red 1 1 $5.00
6 Blue 4 5 $30.00
7 Red 1 1 $5.00
8 Blue 4 5 $30.00
9 Black 5 6 $60.00
10 Green 2 0 $-
11 Violet 6 10 $100.00
12 Blue 4 5 $30.00
13 Pink 7 0 $-
14 Orange 8 12 $90.00

Please let me know if I can provide any further information that may be helpful.

Many thanks,
RedApple

The question itself is very confusing.. the sheetnames you mention are not even in your sample workbook. You should show what you are expecting as a result and the logic for it.

Probably going to require some VBA... which is not my strength... but I still don't think you have provided enough... Like how do we determine which rows go to the summary? Is it every row of every sheet, is it only one specific colour?