Reference another Worksheet and Conditionally Select Data

[Tina Hane]

I have a Summary worksheet that summarizes parts and labor entered on other
worksheets. One section should pull data from the Misc.Parts sheet. I would
like it to only pull data for lines that have a number >0 in cells A17:A216,
and populate the next blank line in the section. When the data populates, it
should pull from Col A on the old to Col A on the new, Col B to Col B, Col D
to Col H, and Col G to Col F. If there is not another blank line to use, it
should error or insert a blank line to be filled.

I have tried using some of the formulas in the posted questions, but nothing
even comes close. I would appreciate any assistance you can offer.

 

[Max]

Presume the source sheet is named: Misc.Parts

In the sheet: Summary,
Assume the various extracts are to be placed in row2 down

Let's use an empty col to the right, say col I, as the criteria col
Put in I2:
=IF(AND(ISNUMBER(Misc.Parts!A17),Misc.Parts!A17>0),ROWS($1:1),"")
Copy down to I201

Then, place
In A2:
=IF(ROWS($1:1)>COUNT($I$2:$I$201),"",INDEX(Misc.Parts!A$17:A$216,SMALL($I$2:$I$201,ROWS($1:1))))
Copy A2 to B2, fill down to B201

In F2:
=IF(ROWS($1:1)>COUNT($I$2:$I$201),"",INDEX(Misc.Parts!G$17:G$216,SMALL($I$2:$I$201,ROWS($1:1))))
Copy down to F201

In H2:
=IF(ROWS($1:1)>COUNT($I$2:$I$201),"",INDEX(Misc.Parts!D$17$216,SMALL($I$2:$I$201,ROWS($1:1))))
Copy down to H201

The above set-up should return what you seek

 

[Tina Hane]

Max:
Thank you for the formulas! I put the formaulas in and got them to work
for the first 13 lines. After that, I still get the quantity and part
number, but the description and cost show #REF! The app. tells me I have an
invalid cell reference error, though the cells are copied all the way down.
I can recopy and clear some, but there are some that will not clear doing
that. Any ideas on what might be wrong? Thanks again for helping with this!
Tina

 

[Max]

Do a quick check on the top-line formulas (those in I2,A2,B2,...) to ensure
that these are intact and exactly* as per my response. Re-fill all of them
down. Are the results okay now?
*check especially that its "ROWS($1:1)" everywhere, not "ROWS(1:1)" as Excel
sometimes has a habit of removing the dollar sign when you press ENTER to
confirm the formula (when it detects/suggests closure for missing parens)

Also, in general, do not *delete* rows in updating data. Deletion of rows
may mess up formulas elsewhere. Just clear the old data range (use the Delete
key to clear contents), then either input/paste special as values.