|
Automatically Updating Summary Grid
would like to create a summary grid based on multiple rows of data below I want the summary grid to update automatically - I will then use SUMIF to total the data looks like this: Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 is there any way I can dynamically create the summary grid in the event of adding a new 'name' to the data i.e. Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 Jesse 1 any help appreciated thanks Rob -- gwatcheater ------------------------------------------------------------------------ gwatcheater's Profile: http://www.thecodecage.com/forumz/member.php?userid=235 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158817 Microsoft Office Help |
Automatically Updating Summary Grid
Simplest solution would be to make a pivot table with the data source defined
as the entire columns where your names and numbers are stored. Then use the worksheet_change event to trigger a refresh of the pivot table. "gwatcheater" wrote: would like to create a summary grid based on multiple rows of data below I want the summary grid to update automatically - I will then use SUMIF to total the data looks like this: Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 is there any way I can dynamically create the summary grid in the event of adding a new 'name' to the data i.e. Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 Jesse 1 any help appreciated thanks Rob -- gwatcheater ------------------------------------------------------------------------ gwatcheater's Profile: http://www.thecodecage.com/forumz/member.php?userid=235 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158817 Microsoft Office Help . |
Automatically Updating Summary Grid
sorry - am not sure how to do this? can you step me through it? B Lynn B;574984 Wrote: Simplest solution would be to make a pivot table with the data source defined as the entire columns where your names and numbers are stored. Then use the worksheet_change event to trigger a refresh of the pivot table. "gwatcheater" wrote: would like to create a summary grid based on multiple rows of data below I want the summary grid to update automatically - I will then use SUMIF to total the data looks like this: Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 is there any way I can dynamically create the summary grid in the event of adding a new 'name' to the data i.e. Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 Jesse 1 any help appreciated thanks Rob -- gwatcheater ------------------------------------------------------------------------ gwatcheater's Profile: 'The Code Cage Forums - View Profile: gwatcheater' (http://www.thecodecage.com/forumz/member.php?userid=235) View this thread: 'Automatically Updating Summary Grid - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=158817) 'Microsoft Office Help' (http://www.thecodecage.com) . -- gwatcheater ------------------------------------------------------------------------ gwatcheater's Profile: http://www.thecodecage.com/forumz/member.php?userid=235 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158817 Microsoft Office Help |
Automatically Updating Summary Grid
See Excel help files or an Excel manual for instruction on creating pivot
tables. Once your pivot table is created, you can record a macro to capture a line of code with correct syntax to refresh it. Then put that code in your worksheet_change event by right-clicking the sheet tab and selecting "view code". At the top of the code window are two dropdowns. With the left dropdown, select "Worksheet". With the right one select "Change". Paste your pivot table refresh command into the subprocedure those selections have automatically generated. In the worksheet_change event, "target" is the range to which a change is being made and can be either a single cell (e.g. if you were manually entering data) or a range of cells if you were performing a paste action with multiple cells. You may want to add some if/then logic using the address of "target" so the pivot is refreshed only when you change something in the columns that provide the pivot data. "gwatcheater" wrote: sorry - am not sure how to do this? can you step me through it? B Lynn B;574984 Wrote: Simplest solution would be to make a pivot table with the data source defined as the entire columns where your names and numbers are stored. Then use the worksheet_change event to trigger a refresh of the pivot table. "gwatcheater" wrote: would like to create a summary grid based on multiple rows of data below I want the summary grid to update automatically - I will then use SUMIF to total the data looks like this: Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 is there any way I can dynamically create the summary grid in the event of adding a new 'name' to the data i.e. Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 Jesse 1 any help appreciated thanks Rob -- gwatcheater ------------------------------------------------------------------------ gwatcheater's Profile: 'The Code Cage Forums - View Profile: gwatcheater' (http://www.thecodecage.com/forumz/member.php?userid=235) View this thread: 'Automatically Updating Summary Grid - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=158817) 'Microsoft Office Help' (http://www.thecodecage.com) . -- gwatcheater ------------------------------------------------------------------------ gwatcheater's Profile: http://www.thecodecage.com/forumz/member.php?userid=235 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158817 Microsoft Office Help . |
Automatically Updating Summary Grid
an excellent intro for pivot tables can be found here 'Using Pivot Tables and Pivot Charts in Microsoft Excel' (http://peltiertech.com/Excel/Pivots/pivotstart.htm) hth B Lynn B;575186 Wrote: See Excel help files or an Excel manual for instruction on creating pivot tables. Once your pivot table is created, you can record a macro to capture a line of code with correct syntax to refresh it. Then put that code in your worksheet_change event by right-clicking the sheet tab and selecting "view code". At the top of the code window are two dropdowns. With the left dropdown, select "Worksheet". With the right one select "Change". Paste your pivot table refresh command into the subprocedure those selections have automatically generated. In the worksheet_change event, "target" is the range to which a change is being made and can be either a single cell (e.g. if you were manually entering data) or a range of cells if you were performing a paste action with multiple cells. You may want to add some if/then logic using the address of "target" so the pivot is refreshed only when you change something in the columns that provide the pivot data. "gwatcheater" wrote: sorry - am not sure how to do this? can you step me through it? B Lynn B;574984 Wrote: Simplest solution would be to make a pivot table with the data source defined as the entire columns where your names and numbers are stored. Then use the worksheet_change event to trigger a refresh of the pivot table. "gwatcheater" wrote: would like to create a summary grid based on multiple rows of data below I want the summary grid to update automatically - I will then use SUMIF to total the data looks like this: Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 is there any way I can dynamically create the summary grid in the event of adding a new 'name' to the data i.e. Col A Col B Rob 1 Steve 1 Rob 1 Rob 1 Jared 2 Steve 5 Jesse 1 any help appreciated thanks Rob -- gwatcheater ------------------------------------------------------------------------ gwatcheater's Profile: 'The Code Cage Forums - View Profile: gwatcheater' ('The Code Cage Forums - View Profile: gwatcheater' (http://www.thecodecage.com/forumz/me...hp?userid=235)) View this thread: 'Automatically Updating Summary Grid - The Code Cage Forums' ('Automatically Updating Summary Grid - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh....php?t=158817)) 'Microsoft Office Help' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) . -- gwatcheater ------------------------------------------------------------------------ gwatcheater's Profile: 'The Code Cage Forums - View Profile: gwatcheater' (http://www.thecodecage.com/forumz/member.php?userid=235) View this thread: 'Automatically Updating Summary Grid - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=158817) 'Microsoft Office Help' (http://www.thecodecage.com) . -- teylyn Telyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com) ------------------------------------------------------------------------ teylyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=983 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158817 Microsoft Office Help |
| All times are GMT +1. The time now is 03:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com