Formula to summarize text from different sheets into a summary sheet?

Hello,

I'm trying to figure out a way to create a workbook with several sheets of mainly text and dates (for scheduling purposes), but I'm trying to find a way to summarize the information from each sheet onto a summary sheet. My current issue is how to get excel to know that once these "IF" statemetnts are true (meaning the data from that cell is entered into a summary sheet, the next time the IF is true, I want it to move to the next cell down.

Here is my current formula: =IF(A6="Group1",(INDIRECT("Sheet1!$C2")),IF(A6="Group2",(INDIRECT("Sheet2!$C2")),IF(A6="Group3",(INDIRECT("Sheet3!$C2")),IF(A6="Group4",(INDIRECT("Sheet4!$C2")),"no"))))

If I type Group1 into A6 the first time and get "Sheet1!$C2", then when I have the formula set up in the next row down (A7, if I type Group 1 again, I want the result to be "Sheet1!$C3".

Any thoughts? Thanks in advance!

Try this:

=IF(OR(A6="Group"&{1;2;3;4}),INDIRECT("'"&SUBSTITUTE(A6,LEFT(A6,5),"Sheet")&"'!C"&ROWS(C$1:C2)),"No")

copy down

Thank you; that's really close!

Attached is an example of the result of using your formula. The only problem is that each time the test is true (any group is entered), excel is selecting the next cell down, regardless of which sheet it is referencing.
I need it to select the next cell down based on how many times that group has already been entered. For example, the as you see in the first two active rows, the first time I enter Group 1 it pulls from the correct cell, and the second time it works as well (because it is the second entry in general),
but when I enter "Group2" for the first time, excel is pulling the third cell from sheet2, rather than the first cell (as it is the first time Group2 has been entered).

Is there a way to do this?

Example of testing data in sheet1 also attached.