Post the workbook, and manually enter a couple of correct results.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"The Cardinal" <The.Cardinal.2bg3se_1153737304.5872@excelforum-nospam.com>
wrote in message
news:The.Cardinal.2bg3se_1153737304.5872@excelforum-nospam.com...
>
> Hi guys - hope you can help me here. I'm moderately proficient at Excel
> but this has stumped me so far...
>
> I've just been posted to a new team in work who deal with sections of
> the accounts, namely method of receipts of payments. The main
> 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 (more
> to follow), and then the different streams of business. The time
> periods consist of seperate columns for financial year, month, and
> quarter
> - for each month of the year, a 'template' is copied and pasted into
> the spreadsheet from another worksheet, containing all the entry items,
> eg Direct Debit, BACS etc. These are pasted in the first blank row
> 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 has
> been in operation. It was originally 6 rows big, at one point was as
> large as 12, is currently 9. The variations come from changes in the
> way payments are handled.
>
> Anyway, this system works and so they've all resolved to keep it as the
> inputters are heavily resistant to change
>
> They've asked me to develop a sort of reporting spreadsheet that will
> 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 the
> info they needed, and then create a new spreadsheet from that - there
> 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 a
> financial year (Apr-Mar). Now I want to extract all this info from the
> 2 worksheets. I've set up a model template for each year, which
> contains all the items that exist, for every month.
>
> Now obviously I could manually go through and extract this info, but I
> want to build a system robust enough to handle what happens in the
> future also, i.e. future months' entries. The problem is, I don't know
> 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 using
> SUM, IF and AND. I had some success in 'single row' entry, but once I
> 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-D
> contain the data Month, Qtr and Financial Year. E-J contain the seperate
> accounts. Rows 11-82 contain all the transaction types (12 sets of
> 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've
> used capital letters to refer to cells in the original data entry
> sheet, and small letters for my own consolidation sheet)
>
> I thought this would have the following effect - it would check whether
> the values in the data entry sheet match those in the consolidation
> sheet for each row in columns A:D. If it did, then it would post to
> that row in column E, whatever is in that row in the original sheet. So
> for example, if the row in the original sheet was for BACS entries, from
> 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 provide
> the sheet if required...
>
> Thanks in advance! :-)
>
>
> --
> The Cardinal
> ------------------------------------------------------------------------
> The Cardinal's Profile:
http://www.excelforum.com/member.php...o&userid=36690
> View this thread: http://www.excelforum.com/showthread...hreadid=564225
>