Moving data between multiple sheets

[herbie000]

When an item is marked as yes on the inventory form for deaths it needs to move to the deaths sheet
When an item is marked as yes on the inventory form for Live Subtraction it needs to move to the Live Subtraction sheet
When and item is marked on the Newborn sheet as died it needs to move to the deaths sheet
When and item is marked on the Newborn sheet as sold it needs to move to the live subtraction sheet

This all working...I have it set up to put the id's in the next "open" cell.

what I need help with is the following in addition to what is listed above, I cant seem to get this to work when looking at more than two sheets/cells.

When and item is marked on the Purchased Additions sheet as died it needs to move to the deaths sheet
When and item is marked on the Purchased Additions sheet as sold it needs to move to the live subtraction sheet


Thanks,

[Matt279139]

Just done a whole load of reply and explanation etc.... all got deleted when I went to edit the formula!!!!!!!!!!

So... This is set up for my own workbook - a transport management one. You'll just have to ignore my sheet names and ranges

=COUNTIF('DEFECT REPORTS'!$L$6:$L$44,B1)

"B1" is the cell you put your search criteria into - "Y" or "N" etc

=IF(ROWS(A$7:A7)>$E$1,"",INDEX('DEFECT REPORTS'!A$6:A$33,SMALL(IF('DEFECT REPORTS'!$L$6:$L$44=$B$1,ROW('DEFECT REPORTS'!$L$6:$L$44)-ROW('DEFECT REPORTS'!$L$6)+1),ROWS(A$7:A7))))

A7 is just the first cell on my destination sheet, the "a$7:a7" bit can be any range on your own sheet - doesn't matter.

$L$6:$L$44 is the column I want it to look in for said criteria.

When set up, this wil give live changes through any or all sheets like you have with the stuff you've set up already.

ALL SHEETS NEED TO BE IDENTICAL IN ORDER FOR THIS FORMULA TO WORK!!!!!!!

Hope this gives you the hours of torment it gave me!!! :-)

Alternatively, Search on google for "Dynamic Tables" video on youtube Magic Trick number 854 or something and he explains it in a very awesome way that makes me almost want to learn!!!!!

[Matt279139]

Forgot to add...…
E1 is where I put the first formula to count the number of entries - had all this covered in my first response. It read like a professor had written it, honest!

"DEFECT REPORTS!" and the following range is just where I want it to look - sheet name and then column in the table etc.