bringing data from multiple worksheets into summary....catch..cell reference changes!

[nimv1]

Please see attached for example.

So for example, cell B2 in the summary tab will pick up B7 on 25/03/2019, cell C7 on 26/03/2019 and D7 on 27/03/2019.

Is this possible?
Some banks have different currencies, so would pick balances from the closing balance as well.
At present, I have to change the cell reference in the summary tab everyday and was hoping it can be automated?

I hope this is clear?

Thanks again for your help.

Regards

[jason.b75]

Enter GBP into A3 of the Bank A sheet, then enter this formula into B2 of the summary, fill right and down as needed.

Please Login or Register  to view this content.

In the absence of the current date in any Bank sheet, the figure for the previous day will be shown.

[nimv1]

Jason,

This seems to have worked perfectly! Thankyou so much.
If you get a little time, I would appreciate a couple of sentences to understand the long formula!
Thanks again though, I will close the thread.

Regards

[jason.b75]

If you get a little time, I would appreciate a couple of sentences to understand the long formula!

I think that it might take more than a couple

I just started typing up a 'how it works' for you, but stopped when I realised that there is a big flaw in it.

Before I correct it, could you confirm, will there be any more than 3 currency types at the top?
Will there always be 3 rows between currency type and closing balance?
Could the last row of data in any of the bank sheets be farther down than the example? Beacuse the figures to be extracted are offset from the criteria (the currency type) we cannot use full columns, so we need to set a range that is edequate in size but not too excessive.

[nimv1]

You read my mind and trying to put this into the actual, it does not work
There are more than 3 currency types (and can even grow), and there are much more rows (which can be different for banks) between currency type and can closing balance!
And there will be more data on bottom of the closing balance of the sheets which I would not want to pick up!

Is it still possible?

[jason.b75]

I think that it is going to be difficult, the additional currency types are not too much of a problem, but the variation in rows between currency type and closing balance will be a pain.

I'm assuming from your last reply that you would have itemised transactions between opening and closing balance.

Leave it with me, I'll give it some thought.

[jason.b75]

One other thought that just came to mind?

Is is possible that any given currency type could be listed more than once in column A of any single bank sheet?

[jason.b75]

I've been tinkering around with a couple of things and came up with this.

Please Login or Register  to view this content.

I'm not even sure where to start trying to explain that one, with a formula like this, I tend to start building out from the middle, which makes it near impossible for anyone else to understand.

Give it a go and see if it works first, if it does then I'll attemp to do a 'how it works' for you over a crayon sandwich