Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?

[atarumorooka]

Hello!
I have one sheet per month collecting data from different bank accounts
and that equals to 12 sheets.
For each month I have 3 pivot collecting and showing data each of them
in a different sheet equal to 36 sheets in total.
The problem is that it is hard to navigate those pages using the tabs
under the sheet.

I tried to simplify it reducing the number of pages: I have put the 3
pivot directly on the month page, just right of the bank account data.
In this case I have a problem because when I select a bank row to copy
it I also select the pivot data and that is not what I want.

So, the question is: how to make it easy? I'd like to reduce the number
of pages having each month data and pivots on the same page or....?
I saw people having a universe in a single page but how to navigate it?
Tks,
Ataru

 

[Arvi Laanemets]

Hi

How much entries do you have for every month? Thousands? When not, then I
advice a single data entry sheet (when you have some date column, then it'll
be simply, otherwise you have to add some date column p.e. with 1st of month
in it) + several report sheets, where reports are calculated accordingly
selected conditions. How exactly, depends on your data structure, and on
what you want to get.


Arvi Laanemets

 

[atarumorooka]

Thank you Dave.
If I understand correctly I should have all the data in a single page
instead of having them divided on 12 pages/months.
Then I can select the month isolating it in the pivot.

That is clean and nice...do you think my secretary will argue that she
has to write again and again the year and the month for each
transaction? Is there a way around it?
Ataru

 

[atarumorooka]

Arvi, tks for the answer....I have about 400 transactions every
month...as I said in another post it would be ok to have them in a
single page...about 5000 entries a year...my secretary will say she
doesn't like to write again and again for example "July".... :-(

 

[Stan]

I agree with the advice to download the info to one sheet and then use
the pivot tables to sumarize the data. To add the month and year
should not be so difficult you can write it in one celland then COPY
and PASTE down. That should not take much more than a minute to do!
The Formula is also a good solution one column for year and anoter for
month..
=year(cell)
=month(cell)
Copy and Paste.
Good luck with your secretary and have a Happy New Year!

 

[atarumorooka]

Done it! Great, simple and very clean.
Thank you all and happy new year.
Ataru

 

[Arvi Laanemets]

Hi

Another way will be using autofilter feature together with SUBTOTAL
function.

Somewhere into 1st row (but not into Amount column - this column must
contain only header and data) enter the formula
=SUBTOTAL(Amount,9)
, where Amount is the reference to amouint column, like F:F

Leave 2nd row empty (so 1st row isn't filtered later)

Into 3rd column enter column headers - data start from 4th column.
Select any data or header cell, and set autofilter on.

Freeze rows 1:3

Whenever you use filter on some or several columns, subtotal sums amounts
from all visible rows. Freezing top rows keeps sum and headers always
visible.
Additionally, when entering data, you can use autofilter, to display only
empty rows, or p.e. only empty rows and entries from last month (use custom
autofilter for this) etc.

And another advice: When you have in your table data from several years,
then instead of separate year and month columns, use month in format
yyyy.mm. P.e. with date in A4, you calculate month as
=TEXT(A4,"yyyy.mm")
or, to get it as number
=--TEXT(A4,"yyyy.mm")


Arvi Laanemets

 

[Arvi Laanemets]

Sorry!

Into 3rd column enter column headers - data start from 4th column.

Must be

Into 3rd row enter column headers - data start from 4th row


Arvi Laanemets

 

[atarumorooka]

Hi Arvi, thank you to still be here suggesting for my problem.
I am still working on that and the suggested solution to have all the
reports on one page has a problem....sigh!

Everything looks fine and sweet....add a column for month and have a
month selection in the pivot page...the problem is that the pivot will
only show the month transaction total and not the amout I have in the
bank accounts....let me explain:
in january I write all my in and outs and the total is +$5000 then in
february I have only outs for $3000 dollars.
Having the pivot showing only february it will say that I am under with
3000 debit while it shoud say plus 2000.
When I was having everythig divided by month in different pages I was
copying the total of january to february page...starting all over
again...
Am I a little bit "stupid" or tired or what?
Tks,
Ataru

 

[Arvi Laanemets]

Hi

My advice was to design report sheets, not to use pivot tables. An example:

Transactions!A3="Date"
Transactions!B3="Account"
Transactions!C3="Amount"
Transactions!D3="Month"

B4 and down - apply data validation list with allowed values "In" and "Out"
D4=IF(A4="";"";--TEXT(A4;"yyyy.mm"))
, and copy down.
Define a named range (Insert>Name>Define) Transact
=OFFSET(Transactions!$A$3,1,,COUNT(Transactions!$A:$A),4)

Lists!A1="Months"
Lists!A2=DATE(YEAR(MIN(INDEX(Transact,,1))),MONTH(MIN(INDEX(Transact,,1))),1
)
Lists!A3=IF(DATE(YEAR($A$2),MONTH($A$2)+ROW()-2,1)>TODAY(),"",DATE(YEAR($A$2
),MONTH($A$2)+ROW()-2,1))
, and copy Lists!A3 down. Format Lists!A2:A? as Custom "yyyy.mm"
Define a named range Months
=OFFSET(Lists!$A$1,1,,COUNT(Lists!$A:$A),1)

Balance!A1="Month:"
Apply to Balance!B1 data validation list with source
=Months
, and format as Custom "yyyy.mm"

Balance!A3="Start balance:"
Balance!A4="Income:"
Balance!A5="Spending:"
Balance!A6="End balance:"

Balance!B3=SUMPRODUCT(--(INDEX(Transact,,4)<--TEXT($B$1,"yyyy.mm")),(INDEX(T
ransact,,2)="In")-(INDEX(Transact,,2)="Out"),INDEX(Transact,,3))
Balance!B4=SUMPRODUCT(--(INDEX(Transact,,4)=--TEXT($B$1,"yyyy.mm")),--(INDEX
(Transact,,2)="In"),INDEX(Transact,,3))
Balance!B5=SUMPRODUCT(--(INDEX(Transact,,4)=--TEXT($B$1,"yyyy.mm")),--(INDEX
(Transact,,2)="Out"),INDEX(Transact,,3))
Balance!B6=SUM(B3:B4,-B5)


Arvi Laanemets

 

[atarumorooka]

Wow Arvi, that is a POST!
Just give me time to implements it and understand all the treasures of
such a solution.
For the moment really thanks.
I'll post again later.
Ataru


Arvi Laanemets ha scritto: