Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic database extraction for reporting
Hi
I have a database that is to contain columns such as: A B C Cost Centre Code Amount 100000 100 50.00 100000 110 55.00 200000 150 100.00 300000 120 101.45 300000 140 210.00 etc (I would expect up to, say, 20-30 cost centres and a few hundred codes) The above table would then change each month (i.e. new codes and amounts may be added). I would then like to create further worksheets (one for each cost centre) that shows only the code and related amount for that cost centre. The only ways I have done this is previously is to either manually add the codes for the cost centre to that cost centre's worksheet or to add all codes to every worksheet and then filter the rows with no data. The first solution is laborious as a search for missing codes must be carried out each time; the second works reasonably well but I end up with a large amount of lookups which impacts on calculation perofrmance. Is there a way of doing this dynamically? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic database extraction for reporting
Hi, maybe a Pivot Table might be a solution to your problem BabyMc;324866 Wrote: Hi I have a database that is to contain columns such as: A B C Cost Centre Code Amount 100000 100 50.00 100000 110 55.00 200000 150 100.00 300000 120 101.45 300000 140 210.00 etc (I would expect up to, say, 20-30 cost centres and a few hundred codes) The above table would then change each month (i.e. new codes and amounts may be added). I would then like to create further worksheets (one for each cost centre) that shows only the code and related amount for that cost centre. The only ways I have done this is previously is to either manually add the codes for the cost centre to that cost centre's worksheet or to add all codes to every worksheet and then filter the rows with no data. The first solution is laborious as a search for missing codes must be carried out each time; the second works reasonably well but I end up with a large amount of lookups which impacts on calculation perofrmance. Is there a way of doing this dynamically? Thanks -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90779 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic database extraction for reporting
Thanks for your swift response.
I realised, after posting, that I could have mentioned Pivot Table. I have thought of this, and used them. The issue I have with Pivot Tables is the format of them. If I am to produce a report for management then it needs to be in a specific format - e.g. using subtotals, bold headers, colours etc. etc. I wouldn't know how to do this from a Pivot Table. "BabyMc" wrote: Hi I have a database that is to contain columns such as: A B C Cost Centre Code Amount 100000 100 50.00 100000 110 55.00 200000 150 100.00 300000 120 101.45 300000 140 210.00 etc (I would expect up to, say, 20-30 cost centres and a few hundred codes) The above table would then change each month (i.e. new codes and amounts may be added). I would then like to create further worksheets (one for each cost centre) that shows only the code and related amount for that cost centre. The only ways I have done this is previously is to either manually add the codes for the cost centre to that cost centre's worksheet or to add all codes to every worksheet and then filter the rows with no data. The first solution is laborious as a search for missing codes must be carried out each time; the second works reasonably well but I end up with a large amount of lookups which impacts on calculation perofrmance. Is there a way of doing this dynamically? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic database extraction for reporting
Does the GETPIVOTDATA formula provide any help ( see 'Excel -- Pivot Tables -- GetPivotData' (http://www.contextures.com/xlPivot06.html) for more info) BabyMc;324888 Wrote: Thanks for your swift response. I realised, after posting, that I could have mentioned Pivot Table. I have thought of this, and used them. The issue I have with Pivot Tables is the format of them. If I am to produce a report for management then it needs to be in a specific format - e.g. using subtotals, bold headers, colours etc. etc. I wouldn't know how to do this from a Pivot Table. "BabyMc" wrote: Hi I have a database that is to contain columns such as: A B C Cost Centre Code Amount 100000 100 50.00 100000 110 55.00 200000 150 100.00 300000 120 101.45 300000 140 210.00 etc (I would expect up to, say, 20-30 cost centres and a few hundred codes) The above table would then change each month (i.e. new codes and amounts may be added). I would then like to create further worksheets (one for each cost centre) that shows only the code and related amount for that cost centre. The only ways I have done this is previously is to either manually add the codes for the cost centre to that cost centre's worksheet or to add all codes to every worksheet and then filter the rows with no data. The first solution is laborious as a search for missing codes must be carried out each time; the second works reasonably well but I end up with a large amount of lookups which impacts on calculation perofrmance. Is there a way of doing this dynamically? Thanks -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90779 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic database extraction for reporting
Thanks again
I have looked at the formula before but I'm not sure whether it works for me (though I'm not familiar with it). I believe the issue with GETPIVOTDATA is still that I am requesting an extraction based on data that is specified elsewhere - however I won't be sure which codes I need in the report until they appear in the table, and then think I would still need to add them to the report to use them with GETPIVOTDATA. I hope that makes sense. However... as you've mentioned GETPIVOTDATA I will look into the function again. Thanks "Pecoflyer" wrote: Does the GETPIVOTDATA formula provide any help ( see 'Excel -- Pivot Tables -- GetPivotData' (http://www.contextures.com/xlPivot06.html) for more info) BabyMc;324888 Wrote: Thanks for your swift response. I realised, after posting, that I could have mentioned Pivot Table. I have thought of this, and used them. The issue I have with Pivot Tables is the format of them. If I am to produce a report for management then it needs to be in a specific format - e.g. using subtotals, bold headers, colours etc. etc. I wouldn't know how to do this from a Pivot Table. "BabyMc" wrote: Hi I have a database that is to contain columns such as: A B C Cost Centre Code Amount 100000 100 50.00 100000 110 55.00 200000 150 100.00 300000 120 101.45 300000 140 210.00 etc (I would expect up to, say, 20-30 cost centres and a few hundred codes) The above table would then change each month (i.e. new codes and amounts may be added). I would then like to create further worksheets (one for each cost centre) that shows only the code and related amount for that cost centre. The only ways I have done this is previously is to either manually add the codes for the cost centre to that cost centre's worksheet or to add all codes to every worksheet and then filter the rows with no data. The first solution is laborious as a search for missing codes must be carried out each time; the second works reasonably well but I end up with a large amount of lookups which impacts on calculation perofrmance. Is there a way of doing this dynamically? Thanks -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90779 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic database extraction for reporting
The easiest way would be to post an sample of your data and what you require. This procedure is possible on our forum BabyMc;324928 Wrote: Thanks again I have looked at the formula before but I'm not sure whether it works for me (though I'm not familiar with it). I believe the issue with GETPIVOTDATA is still that I am requesting an extraction based on data that is specified elsewhere - however I won't be sure which codes I need in the report until they appear in the table, and then think I would still need to add them to the report to use them with GETPIVOTDATA. I hope that makes sense. However... as you've mentioned GETPIVOTDATA I will look into the function again. Thanks "Pecoflyer" wrote: Does the GETPIVOTDATA formula provide any help ( see 'Excel -- Pivot Tables -- GetPivotData' ('Excel -- Pivot Tables -- GetPivotData' (http://www.contextures.com/xlPivot06.html)) for more info) BabyMc;324888 Wrote: Thanks for your swift response. I realised, after posting, that I could have mentioned Pivot Table. I have thought of this, and used them. The issue I have with Pivot Tables is the format of them. If I am to produce a report for management then it needs to be in a specific format - e.g. using subtotals, bold headers, colours etc. etc. I wouldn't know how to do this from a Pivot Table. "BabyMc" wrote: Hi I have a database that is to contain columns such as: A B C Cost Centre Code Amount 100000 100 50.00 100000 110 55.00 200000 150 100.00 300000 120 101.45 300000 140 210.00 etc (I would expect up to, say, 20-30 cost centres and a few hundred codes) The above table would then change each month (i.e. new codes and amounts may be added). I would then like to create further worksheets (one for each cost centre) that shows only the code and related amount for that cost centre. The only ways I have done this is previously is to either manually add the codes for the cost centre to that cost centre's worksheet or to add all codes to every worksheet and then filter the rows with no data. The first solution is laborious as a search for missing codes must be carried out each time; the second works reasonably well but I end up with a large amount of lookups which impacts on calculation perofrmance. Is there a way of doing this dynamically? Thanks -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'Dynamic database extraction for reporting - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=90779) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90779 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel reporting and database | Excel Discussion (Misc queries) | |||
Dynamic reporting rolling averages | Excel Worksheet Functions | |||
Problems searching a database and reporting all occurrences of a v | Excel Discussion (Misc queries) | |||
Reporting with Database function | Excel Discussion (Misc queries) | |||
Excel Worksheet Form / Database reporting | Excel Discussion (Misc queries) |