LinkBack URL
About LinkBacks
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! :-)
Register To Reply
Bookmarks