Hi guys - hope you can help me here. I'm moderately proficient at Exce
but this has stumped me so far...

I've just been posted to a new team in work who deal with sections o
the accounts, namely method of receipts of payments. The mai
spreadsheet is organised as follows:

- two seperate strands of the business each have their own sheet
- the two sheets are designed in the same format
- across the top we have column headers relating to time periods (mor
to follow), and then the different streams of business. The tim
periods consist of seperate columns for financial year, month, an
quarter
- for each month of the year, a 'template' is copied and pasted int
the spreadsheet from another worksheet, containing all the entry items
eg Direct Debit, BACS etc. These are pasted in the first blank ro
available. Then the data from invoices etc is entered.
- there is a unit charge and total cost at the end of the row

Now this copy and paste has evolved over the 3 years this sheet ha
been in operation. It was originally 6 rows big, at one point was a
large as 12, is currently 9. The variations come from changes in th
way payments are handled.

Anyway, this system works and so they've all resolved to keep it as th
inputters are heavily resistant to change :rolleyes:

They've asked me to develop a sort of reporting spreadsheet that wil
allow them to track YTD performance against budget, previous years etc
Previously, the way they did this was to use filters to get down to th
info they needed, and then create a new spreadsheet from that - ther
are hundreds of reports on the shared drive created in this way.

What I've set up so far is a series of tabs each relating to
financial year (Apr-Mar). Now I want to extract all this info from th
2 worksheets. I've set up a model template for each year, whic
contains all the items that exist, for every month.

Now obviously I could manually go through and extract this info, but
want to build a system robust enough to handle what happens in th
future also, i.e. future months' entries. The problem is, I don't kno
where these entries will be as the size of the pasted element changes

So far, I've been playing around with array entering the formula usin
SUM, IF and AND. I had some success in 'single row' entry, but once
try to get it working for an array, I hit problems.

Let me try to explain what I have tried to do.

Column A contains the names of the individual transactions. Columns B-
contain the data Month, Qtr and Financial Year. E-J contain the seperat
accounts. Rows 11-82 contain all the transaction types (12 sets o
identical data).

In my new sheet, in E11, I tried the following:

=Sum(If ( And ( A11:A82 = a11:a82, B11:B82 = b11:b82, C11:C82
c11:c82, D11:D82 = d11:d82), E11:E82,0))

This is array entered into e11:e82. (For ease of demonstration, I'v
used capital letters to refer to cells in the original data entr
sheet, and small letters for my own consolidation sheet)

I thought this would have the following effect - it would check whethe
the values in the data entry sheet match those in the consolidatio
sheet for each row in columns A:D. If it did, then it would post t
that row in column E, whatever is in that row in the original sheet. S
for example, if the row in the original sheet was for BACS entries, fro
April, of Quarter 1, of FY 05-06, then it would copy the value across
If not, it would enter 0.

However this comes up with an error (non-specific error).

So could anybody please suggest an alternative method of doing this.

I realise my explanation might be hard to follow so happy to provid
the sheet if required...

Thanks in advance! :-