T
Terry Bennett
Hope I can explain this clearly enough .
I have c 50 branches of a retail outlet, each of which have a bank account
that is used on a daily basis. The accounts are, however, cleared to a
central account at the end of each month by way of an automatic transfer.
I need to set-up a monitoring system so that I can track the cumulative
balance at each outlet, even though some of this will no longer be showing
on the respective bank accounts (after 31/1/10 - the first clearance date).
I am advised each month of the amounts transferred to the central account
and then have to do weekly enquiries on each of the bank accounts, each
Friday, to track the 'intra month' cumulative balances.
I want the output here to be a simple table with the branch names down the
left hand side (Column A) and dates (each Friday) across the top (ie; Row
1).
So far, I have set up 2 tables on a separate sheet (Inputs): one for the
monthly data and one for the weekly data.
My problem is that, when trying to devise a formula for the summary table, I
seem to need a mixture of VLOOKUP and HLOOKUP to pick-up the figures.
Effectively, what I need the summary table to do each week is to add-in the
data from the Inputs sheet where the date in Row 1 of the summary table is
after the dates in rows 1 of the 2 Input tables (monthly & weekly data).
Obviously, the summary table needs to show the results for each of the 50
branches and all the columns with future dates need to be 'nil' until that
date has been passed.
Hope this makes some sense! Any guidance would be appreciated.
I have c 50 branches of a retail outlet, each of which have a bank account
that is used on a daily basis. The accounts are, however, cleared to a
central account at the end of each month by way of an automatic transfer.
I need to set-up a monitoring system so that I can track the cumulative
balance at each outlet, even though some of this will no longer be showing
on the respective bank accounts (after 31/1/10 - the first clearance date).
I am advised each month of the amounts transferred to the central account
and then have to do weekly enquiries on each of the bank accounts, each
Friday, to track the 'intra month' cumulative balances.
I want the output here to be a simple table with the branch names down the
left hand side (Column A) and dates (each Friday) across the top (ie; Row
1).
So far, I have set up 2 tables on a separate sheet (Inputs): one for the
monthly data and one for the weekly data.
My problem is that, when trying to devise a formula for the summary table, I
seem to need a mixture of VLOOKUP and HLOOKUP to pick-up the figures.
Effectively, what I need the summary table to do each week is to add-in the
data from the Inputs sheet where the date in Row 1 of the summary table is
after the dates in rows 1 of the 2 Input tables (monthly & weekly data).
Obviously, the summary table needs to show the results for each of the 50
branches and all the columns with future dates need to be 'nil' until that
date has been passed.
Hope this makes some sense! Any guidance would be appreciated.