Max,
I do believe your fix is THE fix. He sent me the workbook, I applied your
fix and the existing results remain as they were plus the additional entries
that were missing have now shown up. He had one other issue with a change in
the row numbers farther down the worksheet in question, but by filling the
formulas down that has also been taken care of.

Thanks for handing me the answer on that silver platter.

"Max" wrote:

You should use always ROWS($1:1) for the top extract formula in C6
Try this in C6, copied across/fill down (together with your formula in B6):
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(Summary!A:A,S MALL($B:$B,ROWS($1:1))))

Note that B1:B5 should be left empty
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"JohnM" wrote:
I am setting up a simple accounts workbook

I have one sheet with 11 column headings and as each transaction is
allocated to a department

I then have a worksheet for each department -

I am trying to get it so that when an entry is made in the main sheet it
also appears in the department sheet also

I have used the following formulas but now the main sheet has passed Row 31
the data is not being picked up on the dpartment sheets

In Cell B6 on the dept sheet
=IF($A$6="","",IF(Summary!B6=A$6,ROW(),""))

In Cell C6 and then copied across the Columns
=IF(ROWS($5:5)COUNT($B:$B),"",INDEX(Summary!A:A,S MALL($B:$B,ROWS($5:5))))