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.

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!A170) ,ROWS($1:1),"")
Copy down to I201

Then, place
In A2:
=IF(ROWS($1:1)COUNT($I$2:$I$201),"",INDEX(Misc.Pa rts!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.Pa rts!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.Pa rts!D$17:D$216,SMALL($I$2:$I$201,ROWS($1:1))))
Copy down to H201

The above set-up should return what you seek
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tina Hane" wrote:
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:
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" wrote:

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!A170) ,ROWS($1:1),"")
Copy down to I201

Then, place
In A2:
=IF(ROWS($1:1)COUNT($I$2:$I$201),"",INDEX(Misc.Pa rts!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.Pa rts!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.Pa rts!D$17:D$216,SMALL($I$2:$I$201,ROWS($1:1))))
Copy down to H201

The above set-up should return what you seek
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tina Hane" wrote:
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.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tina Hane" wrote:
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