In Defence of Excel! This important function from database's I use for reporting basis, especailly from sage in relation to a month by month P&L and B/S reports for cleints.
I always use the third option as this clears out all the data on refreshing or opening and then re populates form the database. This ensures the inforamtion being pulled into Excel is never conflicted or merged with existing infomration on the S/sheet.
I agree that information pulled thorugh to Excel is to be used for reportin puroses and gives no actuall link of editing capablilities back to the source data.
If you are linking to a live regularly updated source i.e Sage for KPI's etc and use the automatic update function in your import data proporties. Your KPI's update through the day and with key levels of alrams set up for monitoring responsible individulas can gain greater control over day to day operations.
I'd personally would use the "if" formulae and the "auto filter" I take it you have a column list for bank payments and one for bank receipts going down through your spreadsheet.
Add another two column next to these two headed "Reconciled Y/N" and "Reconciled amount £".
(Abbreviations:- Bank Payments = BP; Bank Receipts = BR; Reconciled Y/N= RYN; Reconciled Amount £ = RA£ below).
Beneath the heading in RA£ type the formulae =if(RYN="","",sum(BR)-sum(BP))
Obviously substitute the abbreviations for you cell references.
Highlight you heading labels and click on the menu of "Data; Auto Filter".
Now once you have your list of bank receipts and bank payments listed on your spreadsheet you can check them off against the bank s/mnt and put a Y in the RYN column. Provided you have a sum total of the RA£ column and add this to your B/fwd balance you will find your bank balance will agree to the bank s/mnt.
You will soon enough find items on the Bank S/mnt that have not been listed in your BR or BP columns in which case you just add them in.
Now to find the items that have been recorded but have not gone through the bank account on the heading label of RYN there will be a small drop down arrow box, click on this and choose show non blanks. This will give you all the items that have not been checked off against the bank s/mnt and you will be able to print this of as viewed.
Thanks Paul Thanks for the info it has enabled me to bring in the info direct form Sage and attach the relevant COA category automatically.
This will save me some time and gives me a great pro forma set up for getting some meaningfull KPI's without worrying if the client has setup new NC's.
My answers
In Defence of Excel!
This important function from database's I use for reporting basis, especailly from sage in relation to a month by month P&L and B/S reports for cleints.
I always use the third option as this clears out all the data on refreshing or opening and then re populates form the database. This ensures the inforamtion being pulled into Excel is never conflicted or merged with existing infomration on the S/sheet.
I agree that information pulled thorugh to Excel is to be used for reportin puroses and gives no actuall link of editing capablilities back to the source data.
If you are linking to a live regularly updated source i.e Sage for KPI's etc and use the automatic update function in your import data proporties. Your KPI's update through the day and with key levels of alrams set up for monitoring responsible individulas can gain greater control over day to day operations.
I'd personally would use the "if" formulae and the "auto filter"
I take it you have a column list for bank payments and one for bank receipts going down through your spreadsheet.
Add another two column next to these two headed "Reconciled Y/N" and "Reconciled amount £".
(Abbreviations:- Bank Payments = BP; Bank Receipts = BR; Reconciled Y/N= RYN; Reconciled Amount £ = RA£ below).
Beneath the heading in RA£ type the formulae =if(RYN="","",sum(BR)-sum(BP))
Obviously substitute the abbreviations for you cell references.
Highlight you heading labels and click on the menu of "Data; Auto Filter".
Now once you have your list of bank receipts and bank payments listed on your spreadsheet you can check them off against the bank s/mnt and put a Y in the RYN column. Provided you have a sum total of the RA£ column and add this to your B/fwd balance you will find your bank balance will agree to the bank s/mnt.
You will soon enough find items on the Bank S/mnt that have not been listed in your BR or BP columns in which case you just add them in.
Now to find the items that have been recorded but have not gone through the bank account on the heading label of RYN there will be a small drop down arrow box, click on this and choose show non blanks. This will give you all the items that have not been checked off against the bank s/mnt and you will be able to print this of as viewed.
Duplicated entries for floating Sage nominals
Paul,
Your help has been great although now I have the floating NC in Sage (ie VAT, Bank etc) being duplicated.
Do you have any ideas about remmedying this through a microsoft query.
Cheers
Thanks Paul
Thanks for the info it has enabled me to bring in the info direct form Sage and attach the relevant COA category automatically.
This will save me some time and gives me a great pro forma set up for getting some meaningfull KPI's without worrying if the client has setup new NC's.
Many thanks