Bringing together whole rafts of info...

T

The Cardinal

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! :-
 
B

Bob Phillips

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" <[email protected]>
wrote in message
 
T

The Cardinal

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
 
B

Bob Phillips

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" <[email protected]>
wrote in message
news:[email protected]...
 
T

The Cardinal

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! :rolleyes:

So anyway, what did you mean by the rebuild?
 
B

Bob Phillips

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" <[email protected]>
wrote in message
news:[email protected]...
 
T

The Cardinal

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
 
T

The Cardinal

Bob that looks absolutely perfect. Could I ask a question or tw
though?

- I've never seen that function ISNA before - what does it do?
- If you remember the tariff costings bit, because stream 2 has only
entries, and stream 1 has the 7, the tariff costs for those in stream
but not in 2 don't appear because I think the formula is only checkin
stream 2. If I change the references in the tariff costs column t
Stream 1 instead of 2, will it pick up the tariff costs for all th
items? (I only ask because I don't want to mess around with the formul
for fear of breaking it!)
-As a novice with array formulae, how do I edit little bits of it? Do
need to select the whole array, and then edit, and ctrl-shift enter a
the end again?
- Just out of curiosity, why are some cells 0, and others blank?

I really appreciate your help!
Mik
 
B

Bob Phillips

"The Cardinal" <[email protected]>
wrote in message
Bob that looks absolutely perfect. Could I ask a question or two
though?

- I've never seen that function ISNA before - what does it do?

ISNA is used to check whether functions like MATCH find a match. If they do
they return a number, which I pass to INDEX to get the value. If they don't
they return #N/A, which is nasty in this case, so I use ISNA to test for no
match, and if no match I output "", else I output the result. I could also
have used ISNUMBER, same effect.
- If you remember the tariff costings bit, because stream 2 has only 2
entries, and stream 1 has the 7, the tariff costs for those in stream 1
but not in 2 don't appear because I think the formula is only checking
stream 2. If I change the references in the tariff costs column to
Stream 1 instead of 2, will it pick up the tariff costs for all the
items? (I only ask because I don't want to mess around with the formula
for fear of breaking it!)

Excellent point, I should have thought of that.

The solution is to test for a match with one, and then the other

=IF(ISNA(MATCH(1,(Stream1!$A$4:$A$1000='FY2005-06'!$A8)*(Stream1!$B$4:$B$100
0='FY2005-06'!$B8),0)),
IF(ISNA(MATCH(1,(Stream2!$A$4:$A$1000='FY2005-06'!$A8)*(Stream2!$B$4:$B$1000
='FY2005-06'!$B8),0)),"",
INDEX(Stream2!J$4:J$1000,MATCH(1,(Stream2!$A$4:$A$1000='FY2005-06'!$A8)*(Str
eam2!$B$4:$B$1000='FY2005-06'!$B8),0))),
INDEX(Stream1!L$4:L1001,MATCH(1,(Stream1!$A$4:$A$1000='FY2005-06'!$A8)*(Stre
am1!$B$4:$B$1000='FY2005-06'!$B8),0)))

which again is an array formula.
-As a novice with array formulae, how do I edit little bits of it? Do I
need to select the whole array, and then edit, and ctrl-shift enter at
the end again?

You can go into edit mode in the cell, F2, and then just place the cursor
over the bit to edit, and change it. You must do Ctrl-Shift-Enter at then
for array formulae though.
- Just out of curiosity, why are some cells 0, and others blank?

The blank cells are where I code it to put blabk, as per the #N/A discussion
above.

The zeroes are caused by referring to cells that have no data, either by a
direct reference, or by summing empty cells. AGain, you could test for 0 and
output "", but the big problem with this stuff is knowing when it contained
0 and when it contains "", they are different.
I really appreciate your help!
Mike

And now I know the Cardinal's name <g>. I assume that from BoE Charges, that
means you are UK based?
 
T

The Cardinal

Thanks again Bob - I've put the formula in for the tariff columns and
it's working perfectly.
Yes I'm in England and responsible for paying lots of money to the
BoE!
And as for the name, well a bit of mystique never hurt anybody!

Thanks again for the help, and no doubt I'll call on you again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top