Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
This is the way I organize my bank transactions.
I have a "transactions" tab with the following columns, left to right: Account name|Year|Month|Date|Description|Category|Dollar amount I run pivot tables off that (very long) table as needed. Since I have both the account name and Year & Month columns it is easy enough to isolate one account's transactions in June of 2006 by using that constraint in a pivot table. It's easy to navigate. The data I download from the banks is the Date, Description, and Dollar amount. The other data are automatically populated via formulas, i.e., =YEAR(D2) returns the year in the date data, etc. Dave -- Brevity is the soul of wit. " wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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 wrote in message ps.com... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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 Dave F wrote: This is the way I organize my bank transactions. I have a "transactions" tab with the following columns, left to right: Account name|Year|Month|Date|Description|Category|Dollar amount I run pivot tables off that (very long) table as needed. Since I have both the account name and Year & Month columns it is easy enough to isolate one account's transactions in June of 2006 by using that constraint in a pivot table. It's easy to navigate. The data I download from the banks is the Date, Description, and Dollar amount. The other data are automatically populated via formulas, i.e., =YEAR(D2) returns the year in the date data, etc. Dave -- Brevity is the soul of wit. " wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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".... :-( Arvi Laanemets wrote: 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 wrote in message ps.com... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
Well, as I said, stuff like the Year and Month can be extracted from the date
you download from the bank. If the date appears in A2, then =YEAR(A2) extracts the year and =MONTH(A2) extracts the month. Dave -- Brevity is the soul of wit. " wrote: 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 Dave F wrote: This is the way I organize my bank transactions. I have a "transactions" tab with the following columns, left to right: Account name|Year|Month|Date|Description|Category|Dollar amount I run pivot tables off that (very long) table as needed. Since I have both the account name and Year & Month columns it is easy enough to isolate one account's transactions in June of 2006 by using that constraint in a pivot table. It's easy to navigate. The data I download from the banks is the Date, Description, and Dollar amount. The other data are automatically populated via formulas, i.e., =YEAR(D2) returns the year in the date data, etc. Dave -- Brevity is the soul of wit. " wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
Done it! Great, simple and very clean.
Thank you all and happy new year. Ataru Dave F wrote: Well, as I said, stuff like the Year and Month can be extracted from the date you download from the bank. If the date appears in A2, then =YEAR(A2) extracts the year and =MONTH(A2) extracts the month. Dave -- Brevity is the soul of wit. " wrote: 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 Dave F wrote: This is the way I organize my bank transactions. I have a "transactions" tab with the following columns, left to right: Account name|Year|Month|Date|Description|Category|Dollar amount I run pivot tables off that (very long) table as needed. Since I have both the account name and Year & Month columns it is easy enough to isolate one account's transactions in June of 2006 by using that constraint in a pivot table. It's easy to navigate. The data I download from the banks is the Date, Description, and Dollar amount. The other data are automatically populated via formulas, i.e., =YEAR(D2) returns the year in the date data, etc. Dave -- Brevity is the soul of wit. " wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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 "Stan" wrote in message ups.com... 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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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 wrote: 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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 (InsertNameDefine) 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 wrote in message oups.com... 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 wrote: 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: 12 months+ 36 pivot = 48 sheets...can I simplify it?
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: 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 (InsertNameDefine) 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 wrote in message oups.com... 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table error when Opening an Xml Spreadsheet from MS excel Xp | Charts and Charting in Excel | |||
Excel 97 Question (Pivot tables) | Excel Discussion (Misc queries) | |||
Pivot Table Memory Issue - Excel crashes | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) |