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

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, D1182 = 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. 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! :-

--
The Cardina
-----------------------------------------------------------------------
The Cardinal's Profile: http://www.excelforum.com/member.php...fo&userid=3669
View this thread: http://www.excelforum.com/showthread.php?threadid=56422

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" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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, D1182 = 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. 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
>

Bob, Thanks for looking at this for me...

Ok I tried to upload one of the sheets but it's too big (187KB). Can I
email it to you instead?

Anyway, just in case the upload did work, firstly the Macros aren't
necessary for this sheet so no problem in disabling them. On sheets
Stream 1 and Stream 2 is the actual live data for the last few years.
The remaining sheets (FY2005-06 and FY 2006-07) are the sheets I've
created to bring this data together. From there, I'm going to export it
all to another management sheet, but that's another issue! :-)

Ok, for FY 2005-06, row 7, I've copied and pasted the info from Stream
1 and Stream 2, BACS Input (Credit), for April 05, Qtr1 FY2005-06 as
you can see. You'll notice that for stream 1, there are 6 rows, and
stream 2, only 2 for this month. Stream 1 becomes 7 later on, and
stream 2 becomes 3 later on, which is part of my problem. You'll see
that each month for FY2005-06 has room for the 7 entries, even though
for most of the year, only 5 are needed. (This is simpler than the
other sheet I mentioned where the number of required rows for each
month varies from 6-12). I don't mind 0 values/blanks because this
sheet is only being used to generate data for another sheet.

So hopefully this should make it clearer what my problem is? When I put
FY2007-08 in, I'll simply have no idea which rows that info will occupy,
so I'll need to match Transaction Type, Month, Quarter and Financial
Year from the potential 6000 rows that exist there.

All help gratefully received...

MR


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

No, it didn't make it. Can you zip it?

--
HTH

Bob Phillips

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

"The Cardinal" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> Bob, Thanks for looking at this for me...
>
> Ok I tried to upload one of the sheets but it's too big (187KB). Can I
> email it to you instead?
>
> Anyway, just in case the upload did work, firstly the Macros aren't
> necessary for this sheet so no problem in disabling them. On sheets
> Stream 1 and Stream 2 is the actual live data for the last few years.
> The remaining sheets (FY2005-06 and FY 2006-07) are the sheets I've
> created to bring this data together. From there, I'm going to export it
> all to another management sheet, but that's another issue! :-)
>
> Ok, for FY 2005-06, row 7, I've copied and pasted the info from Stream
> 1 and Stream 2, BACS Input (Credit), for April 05, Qtr1 FY2005-06 as
> you can see. You'll notice that for stream 1, there are 6 rows, and
> stream 2, only 2 for this month. Stream 1 becomes 7 later on, and
> stream 2 becomes 3 later on, which is part of my problem. You'll see
> that each month for FY2005-06 has room for the 7 entries, even though
> for most of the year, only 5 are needed. (This is simpler than the
> other sheet I mentioned where the number of required rows for each
> month varies from 6-12). I don't mind 0 values/blanks because this
> sheet is only being used to generate data for another sheet.
>
> So hopefully this should make it clearer what my problem is? When I put
> FY2007-08 in, I'll simply have no idea which rows that info will occupy,
> so I'll need to match Transaction Type, Month, Quarter and Financial
> Year from the potential 6000 rows that exist there.
>
> All help gratefully received...
>
> MR
>
>
> --
> 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
>

Good idea - attached (assuming I worked WinZip correctly!)


+-------------------------------------------------------------------+
|Filename: BoE.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5088 |
+-------------------------------------------------------------------+

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

Looking at the data, you seem to have misaligned the data (part of your
problem that you want to solve I guess), as you have aligned the BACS Output
(Debit) for Stream 1 against BACS Input (Credit).

What I am not yet clear on is whether categories per month is fixed or
variable. If variable, I guess that the easiest way will be VBA, formulae
would be horrendous. Can the FY2005-06 data be rebuilt each month? If so,
how would you want to trigger the rebuild?

--
HTH

Bob Phillips

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

"The Cardinal" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> Good idea - attached (assuming I worked WinZip correctly!)
>
>
> +-------------------------------------------------------------------+
> |Filename: BoE.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5088 |
> +-------------------------------------------------------------------+
>
> --
> 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
>

Bill,
Thanks for looking again.

The alignment problem you mention is actually just a copy and paste
error from me due to the freeze panes thing I had on. I've now copied
the right data and attach again! Sorry! But, it does highlight my point
that it would be impossible to map where any particular month's BACS
Input would be, which is why I turned to this forum

I'm not entirely sure what you mean by the rebuild thing. If you're
thinking the same as me (that this could be done so much easier another
way) then you're absolutely right... however resistance to change here
is very strong, and they'd just as much prefer to be doing it with pen
and paper than this new fangled PC thing!

So anyway, what did you mean by the rebuild?


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

What I meant is to ditch the current year spreadsheet and build it anew each
month. If the year summary holds all categories that could be possible,
formulae could work, otherwise rebuilding is probably better.

--
HTH

Bob Phillips

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

"The Cardinal" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> Bill,
> Thanks for looking again.
>
> The alignment problem you mention is actually just a copy and paste
> error from me due to the freeze panes thing I had on. I've now copied
> the right data and attach again! Sorry! But, it does highlight my point
> that it would be impossible to map where any particular month's BACS
> Input would be, which is why I turned to this forum
>
> I'm not entirely sure what you mean by the rebuild thing. If you're
> thinking the same as me (that this could be done so much easier another
> way) then you're absolutely right... however resistance to change here
> is very strong, and they'd just as much prefer to be doing it with pen
> and paper than this new fangled PC thing!
>
> So anyway, what did you mean by the rebuild?
>
>
> --
> 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
>

It's possible for this sheet with these 2 streams to have every possibl
category in the year summary. In fact I've toyed with the idea for ever
month of introducing a 'Miscellaneous' field in the event that ne
categories do come up (very unlikely).

The 7 you see now per month are the only 7 that are foreseeabl
possible (which would become 8 if you allow for the miscellaneou
option mentioned above..)

Is that good news?

Either way, I'm happy to rebuild the yearly summaries if that will hel
- so long as the 2 input sheets remain as close to the original a
possible, I have pretty much carte blanche to do whatever else I se
fit

--
The Cardina
-----------------------------------------------------------------------
The Cardinal's Profile: http://www.excelforum.com/member.php...fo&userid=3669
View this thread: http://www.excelforum.com/showthread.php?threadid=56422

I have posted a proposed solution at http://cjoint.com/?hAlQ5ptf4a

I have only done the 2006-06 year, you should be able to apply easily to
another year if it is what you want.

--
HTH

Bob Phillips

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

"The Cardinal" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> It's possible for this sheet with these 2 streams to have every possible
> category in the year summary. In fact I've toyed with the idea for every
> month of introducing a 'Miscellaneous' field in the event that new
> categories do come up (very unlikely).
>
> The 7 you see now per month are the only 7 that are foreseeably
> possible (which would become 8 if you allow for the miscellaneous
> option mentioned above..)
>
> Is that good news?
>
> Either way, I'm happy to rebuild the yearly summaries if that will help
> - so long as the 2 input sheets remain as close to the original as
> possible, I have pretty much carte blanche to do whatever else I see
> fit.
>
>
> --
> 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
>