Pulling data from different sheets

[G. Beard]

On sheet1 I have an overview of sheet2:sheet10. Sheet2 has a different
number of data entries than sheet3 and so on. What is the best way of
pulling that data from each sheet but only from the rows that have data?
Here is the formula in A4:

=IF(Topps!$H3<1,Topps!A3,"")

I've copied that down my sheet, but there are only 30 entries on sheet
Topps. How can I start pulling data from the next sheet once the 30 rows
are pulled from sheet Topps?
I've done a COUNTIF for each sheet to determine how many rows have data but
I don't know how to write the formula to work the way I want it to. Do I
need to do it in VB coding?

Please help,
Gary

 

[Myrna Larson]

A formula would be very complex, if possible at all: you would have to
determine whether the row number on sheet1 was <= the numbers of entries on
sheet2. If so, pull data from appropriate row of sheet2. If not, is it less
than the sum of the entries on sheets2 and 3? If so, pull data from the right
row on sheet3, else from right row on sheet4, and so on. With 9 sheets, I
think you will exceed the limitation on nested formulas and IF statements.

Have you considered setting up formulas that will handle the maximum number of
entries on each sheet, then using Data/Filter/AutoFilter to hide the rows
where your formula returns "" ?

If that isn't workable, then I think you will need a VBA solution.