Pivot Table Question

T

thornomad

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
 
K

Ken Wright

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 here:-

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 :)
------------------------------­------------------------------­----------------
 
T

thornomad

I don't understand how any of this works, but it is effing genius. I love
it. Thank you!

D
 
K

Ken Wright

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 :)
------------------------------­------------------------------­----------------
 
Top