Alistair Darling issues his most important budget today.
I would love to think he addresses the anomaly of an acute housing shortage and decimated construction firms with no work.
The UK has had a massive drop off in demand for the purchase of houses due to the credit crunch in the mortgage market, but still has a massive demand for housing (unlike the US, which has an oversupply of housing).
Surely the answer is to pay the construction companies to build council houses/social housing!
Good luck Alistair - you.re going to need it!
What does everyone else want to see in the budget?
Wednesday, 22 April 2009
Tuesday, 21 April 2009
Excel Tip: Using GETPIVOTDATA (part 2)
This post continues from where we left in Excel Tip: Using GETPIVOTDATA (part 1) . We had explained the construction of the GETPIVOTDATA formula and how to use it to report a particular figure from the pivot table.
In this post we will look at the flexibility that can be achieved by using formulae to populate some of the arguments in the GETPIVOTDATA function.
To recap, the GETPIVOTDATA function has the following format:
=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")
but each of the text fields (in inverted commas) can be replaced by formulae which, used intelligently, allow you to produce management accounts and reports in any format.
Examples:
You could replace "Name of data field to return" with a reference to the cell at the top of the column in your final report, which can hold the name of the data field you wish to return. By using [F4] to insert a '$' in front of the row but not the column in this reference, you can copy the formula across multiple columns using different data fields from the pivot table in each column on your final report. This is useful for reporting, say, month and year-to-date figures in management accounts.
Where the field item refers to the name of the raw data column containing a code that determines where the values go in your final report (e.g. a management accounts code) - if you replace the corresponding "item" field with a formula referring to the first column of the spreadsheet, this can be used to populate the rows of your final report. By using [F4] to insert a '$' before the column and not the row, you can now copy the same formula into every cell in your management accounts. Each row will show the value for that row, based on the code in the first column and each column will include the data field to use for the value, based on the first row.
How you get theses management accounts codes into the raw data is covered in an earlier post:
Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?
If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.
In this post we will look at the flexibility that can be achieved by using formulae to populate some of the arguments in the GETPIVOTDATA function.
To recap, the GETPIVOTDATA function has the following format:
=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")
but each of the text fields (in inverted commas) can be replaced by formulae which, used intelligently, allow you to produce management accounts and reports in any format.
Examples:
You could replace "Name of data field to return" with a reference to the cell at the top of the column in your final report, which can hold the name of the data field you wish to return. By using [F4] to insert a '$' in front of the row but not the column in this reference, you can copy the formula across multiple columns using different data fields from the pivot table in each column on your final report. This is useful for reporting, say, month and year-to-date figures in management accounts.
Where the field item refers to the name of the raw data column containing a code that determines where the values go in your final report (e.g. a management accounts code) - if you replace the corresponding "item" field with a formula referring to the first column of the spreadsheet, this can be used to populate the rows of your final report. By using [F4] to insert a '$' before the column and not the row, you can now copy the same formula into every cell in your management accounts. Each row will show the value for that row, based on the code in the first column and each column will include the data field to use for the value, based on the first row.
How you get theses management accounts codes into the raw data is covered in an earlier post:
Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?
If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.
Golden Cells: The ExcelZone online video awards
It is always nice to get recognition for what you are doing and I was pleased to see our Introduction to Pivot Tables video course featured in Accountingweb's (a respected UK Accounting website) Excelzone online video awards. The video was described thus:
You can see the free video here.
"Many of the Excel video pioneers are American, but a new generation of UK
producers has emerged in recent months led by Carlisle-based Emily Coltman. Her
four-minute Introduction to pivot table course video for
Feechan Consulting truly is a state-of-the art production, featuring shiny pink
introductory graphics, slick page-folding transitions and the now obligatory
enlarged yellow spotlight cursor technique. The content and pacing are well
planned and the narration crisp and authoritative.
You can see the free video here.
Subscribe to:
Posts (Atom)