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))))

I would appreciate any assistance

thanks

--
JohnM

I'm having a little trouble picturing what is going on in your workbook. I
think I could help if I had the workbook to examine and help set up. So, if
no one else provides a solution for you and if you want for me to look at it,
send it as an email attachment to (remove spaces)
HelpFrom @ JLathamSite . com
and I'll do what I can with it for you.


"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))))

I would appreciate any assistance

thanks

--
JohnM

Hi John

What are your column headings?
If you have multiple headings which just hold the department name, and you
are allocating a value to that department column, you should consider
changing your layout.
Have a single column for Amount and a single column for Department, into
which you enter the department name (from a dropdown Data Validation list if
required).
Then summarise with a Pivot Table to give your Departmental totals, without
the need to transpose any data to any other sheet.

If you want more help on how to do this, post back with an example of your
data layout.
--
Regards
Roger Govier

"JohnM" wrote in message
...
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))))

I would appreciate any assistance

thanks

--
JohnM

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))))

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))))

Jerry,
Thank for your note. Glad that did it for the OP.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"JLatham" wrote:
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.