Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table Question
Hi,
I had a wonderful suggestion from this group to use a pivot table to group my transactions by month in a chart ... awesome. now, however, i can't figure out an easy way to create a pivot table that will allow me to continually add data at the end of my list ... as more transactions come in. I appreciate that I have to use the "refresh data" button ... however, I am finding that if I create a pivot table selecting my entire data worksheet as the source (even the empty cells) it won't let me group my DATE field by month ... I believe it is because I have "empty cells". If I go ahead and select only the cells with data in them, however, each time I add a new transaction, I have to go back into the wizard and reestablish my data fields. Is there any easy way around this ? Thanks. D |
#2
|
|||
|
|||
Yes :-)
You are correct in that empty cells (or text cells) in a date field will prevent you from grouping dates. The way round what is hapopening is to use a dynamic range that will automatically piclk up the extra data. Instructions can be found he- http://www.contextures.com/xlPivot01.html -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "thornomad" wrote in message ... Hi, I had a wonderful suggestion from this group to use a pivot table to group my transactions by month in a chart ... awesome. now, however, i can't figure out an easy way to create a pivot table that will allow me to continually add data at the end of my list ... as more transactions come in. I appreciate that I have to use the "refresh data" button ... however, I am finding that if I create a pivot table selecting my entire data worksheet as the source (even the empty cells) it won't let me group my DATE field by month ... I believe it is because I have "empty cells". If I go ahead and select only the cells with data in them, however, each time I add a new transaction, I have to go back into the wizard and reestablish my data fields. Is there any easy way around this ? Thanks. D |
#3
|
|||
|
|||
I don't understand how any of this works, but it is effing genius. I love
it. Thank you! D "Ken Wright" wrote: Yes :-) You are correct in that empty cells (or text cells) in a date field will prevent you from grouping dates. The way round what is hapopening is to use a dynamic range that will automatically piclk up the extra data. Instructions can be found he- http://www.contextures.com/xlPivot01.html -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "thornomad" wrote in message ... Hi, I had a wonderful suggestion from this group to use a pivot table to group my transactions by month in a chart ... awesome. now, however, i can't figure out an easy way to create a pivot table that will allow me to continually add data at the end of my list ... as more transactions come in. I appreciate that I have to use the "refresh data" button ... however, I am finding that if I create a pivot table selecting my entire data worksheet as the source (even the empty cells) it won't let me group my DATE field by month ... I believe it is because I have "empty cells". If I go ahead and select only the cells with data in them, however, each time I add a new transaction, I have to go back into the wizard and reestablish my data fields. Is there any easy way around this ? Thanks. D |
#4
|
|||
|
|||
It's easier than you think :-)
Take the function OFFSET. By using the last two arguments in the function, you can create a range for Excel to work with, eg:- =OFFSET(reference,rows,cols,height,width) =OFFSET(A1,0,0,4,6) starts at A1, moves down 0 rows, moves across 0 columns (so the range still starts at A1), and then expands the range so that it is 4 rows high and 6 columns wide. This range will be A1:F4 (4 rows x 6 cols) =OFFSET(A1,0,0,12,7) starts at A1, moves down 0 rows, moves across 0 columns (so the range still starts at A1), and then expands the range so that it is 12 rows high and 7 columns wide. This range will be A1:G12 (12 rows x 7 cols) Now that's great but you don't want to have to specify how many rows are in your data each time, so you simply use another function to count how many rows have data in and then pass that back to the OFFSET function, eg:- Assuming you could count on Col B having data in every cell in your column where there was data in the row, you could use =COUNTA(B:B) to get a count of these cells. If that was 50 then your data would be down as far as row 50. If you added another row of data then that function would automatically return 51 and so on. You then just substitute the ROWS argument with this function and it all becomes automatic:- =OFFSET(A1,COUNTA(B:B),0,4,6) starts at A1, moves down 0 rows, moves across 0 columns (so the range still starts at A1), and then expands the range so that it is however many rows high your function returns and 6 columns wide. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "thornomad" wrote in message ... I don't understand how any of this works, but it is effing genius. I love it. Thank you! D "Ken Wright" wrote: Yes :-) You are correct in that empty cells (or text cells) in a date field will prevent you from grouping dates. The way round what is hapopening is to use a dynamic range that will automatically piclk up the extra data. Instructions can be found he- http://www.contextures.com/xlPivot01.html -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "thornomad" wrote in message ... Hi, I had a wonderful suggestion from this group to use a pivot table to group my transactions by month in a chart ... awesome. now, however, i can't figure out an easy way to create a pivot table that will allow me to continually add data at the end of my list ... as more transactions come in. I appreciate that I have to use the "refresh data" button ... however, I am finding that if I create a pivot table selecting my entire data worksheet as the source (even the empty cells) it won't let me group my DATE field by month ... I believe it is because I have "empty cells". If I go ahead and select only the cells with data in them, however, each time I add a new transaction, I have to go back into the wizard and reestablish my data fields. Is there any easy way around this ? Thanks. D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Question | Excel Worksheet Functions | |||
Excel Pivot Table Refresh Question | Excel Discussion (Misc queries) | |||
Excel Pivot Table Question | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
pivot table question, sum fields? | Excel Worksheet Functions |