Broken formula + tweaking

[KymBradshaw]

Hi guys,

This should be a fairly straight forward one for you. Hopefully!

I've been making a Statement of Account, but I've kinda broke my formula with constant tinkering. It was working fine when I did a dummy run with it and it was all within one workbook, but doesn't work in practice when I modified it to work with outside files. As far as I can see it *should* be working fine, but just isn't! Can anyone shed some light on it for me please?

=IF(ISERROR(INDEX('[Sales 2013-2014 Kym Edit.xlsx]April 2013'!$B$3:$K$50,SMALL(IF('[Sales 2013-2014 Kym Edit.xlsx]April 2013'!$C$3:$C$50=$A$8,ROW('[Sales 2013-2014 Kym Edit.xlsx]April 2013'!$C$3:$C$50)),ROW(2:2)),5)),"",INDEX('[Sales 2013-2014 Kym Edit.xlsx]April 2013'!$B$3:$K$50,SMALL(IF('[Sales 2013-2014 Kym Edit.xlsx]April 2013'!$C$3:$C$50=$A$8,ROW('[Sales 2013-2014 Kym Edit.xlsx]April 2013'!$C$3:$C$50)),ROW(2:2)),5))

It's supposed to list all invoices for a given client, but it's being a pain where I thought it would be a relatively straight forward task.

Secondly, the Sales 2013-2014 workbook from which the data is harvested has a worksheet for each month and my formula is only good for one. At this rate I'll need to have 12 versions of the above; is there any way I can tweak it so it will aggregate all 12 months data?

Any advice is hugely appreciated.

Here are the files in question:

Statement of account
https://www.dropbox.com/s/nemcgbglpqxu78f/Statement of Account - dummy data.xlsx

Invoice lists
https://www.dropbox.com/s/vq3lpc6zgmbuqma/Sales 2013-2014 - Kym Edit.xlsx

 

[bewsh1987]

ive downloaded the tow workbooks and when it open it give me error two however the formula is fine

what i see
=IF(ISERROR(INDEX('[Sales 2013-2014 kim edit.xlsx]April 2013'!$B$3:$K$50,SMALL(IF('C:\Users\Sandra\Dropbox\InnerLondonCleaning Ltd\Accounts\Accounts 2013-2014\[Sales 2013-2014.xlsx]April 2013'!$C$3:$C$50=$A$8,ROW('C:\Users\Sandra\Dropbox\InnerLondonCleaning Ltd\Accounts\Accounts 2013-2014\[Sales 2013-2014.xlsx]April 2013'!$C$3:$C$50)),ROW(1:1)),5)),"",INDEX('C:\Users\Sandra\Dropbox\InnerLondonCleaning Ltd\Accounts\Accounts 2013-2014\[Sales 2013-2014.xlsx]April 2013'!$B$3:$K$50,SMALL(IF('C:\Users\Sandra\Dropbox\InnerLondonCleaning Ltd\Accounts\Accounts 2013-2014\[Sales 2013-2014.xlsx]April 2013'!$C$3:$C$50=$A$8,ROW('C:\Users\Sandra\Dropbox\InnerLondonCleaning Ltd\Accounts\Accounts 2013-2014\[Sales 2013-2014.xlsx]April 2013'!$C$3:$C$50)),ROW(1:1)),5))

what you should see
=IF(ISERROR(INDEX('[Sales 2013-2014 - Kym Edit.xlsx]April 2013'!$B$3:$K$50,SMALL(IF('[Sales 2013-2014 - Kym Edit.xlsx]April 2013'!$C$3:$C$50=$A$8,ROW('[Sales 2013-2014 - Kym Edit.xlsx]April 2013'!$C$3:$C$50)),ROW(1:1)),5)),"",INDEX('[Sales 2013-2014 - Kym Edit.xlsx]April 2013'!$B$3:$K$50,SMALL(IF('[Sales 2013-2014 - Kym Edit.xlsx]April 2013'!$C$3:$C$50=$A$8,ROW('[Sales 2013-2014 - Kym Edit.xlsx]April 2013'!$C$3:$C$50)),ROW(1:1)),5))

i hope this helps

 

[KymBradshaw]

It does help actually, with one slight drawback, it is returning the vales from two lines down! Any idea why this could be so? It works great other than that!

 

[KymBradshaw]

Problem resolved, all sorted now.

 

[shyy]

Great but you should still post your solution incase other people are experieincing the same problem.

 

[KymBradshaw]

Okay!

So the above formula worked fine except for the fact that it returned data two row down.

The fault lay with these references $B$3 & $C$3 which I simply needed to change to $B$1 & $C$1 respectively.

The thing I don't really understand is that the data range begins on $B$3 & $C$3, so to me it's somewhat odd. However, t works, and I'm a happy little bunny!

Thanks for all input.