SteveT
Member of DD Central
Posts: 6,875 
Likes: 7,924
|
Post by SteveT on Dec 10, 2015 22:51:43 GMT
Hi SteveT, Thanks for the revised file. The .xls version behaves in the same way. I will have to investigate my excel set up - looks like it is blocking pivot tables somehow. I've checked that macros are enabled if that might be relevant.. Your help is much appreciated LW I tried Googling "pivot table refresh greyed out" and it sounds like older versions of Excel can struggle to cope with pivots created in newer versions (even when they should). Best suggestion I can make is to create a new pivot table from scratch in your version of Excel: a) highlight the entire columns A to I on the Txns Extract sheet b) start the pivot table creation function (in Office 2016 the command is Insert - Pivot Table) and tell it to put it on a new sheet c) tick to include the fields Date, Type, Loan and Amount d) drag Date and Type to the Filters area e) drag Loan to the Rows area f) drag Amount to the Values area and right-click to set the value field setting to "Sum of Amount" (rather than "Count of Amount") g) at the top of the pivot table itself, click on the filter button to the right of Type and change it from "All" to pick up just "Capital repayment", "Loan part fund" and "Loan part sale" Basically you can play around with the table settings until it's doing what you want it to.
|
|
|
|
Post by ladywhitenap on Dec 11, 2015 12:29:16 GMT
Thanks again SteveT,
It appears that my prehistoric version of Excel 2002 has less pivot table features than the modern one.
A number of things in your .XLS version tell me to open it in the newer version in which it was created.
My pivot table creation wizard is very clearly different to the one you describe instructions for although I have got something near a table like yours now
The sum of amount appears in column A rather than column B in your version and more significantly (I think) following your instruction g, I cannot have multiple selections for the TYPE filter button.
I'm very grateful for your help and persistence with me but I think I will have to give up. The thought of changing to a newer version of Excel fills me with dread with the possibility of my many many other spreadsheets breaking.
I might set up a sandbox on another sacrificial PC and try a newer version for my own satisfaction during the tedium of the forthcoming holiday or shall I just get out the Monopoly set.......
Best Wishes
LW
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Dec 11, 2015 12:40:05 GMT
Thanks again SteveT, It appears that my prehistoric version of Excel 2002 has less pivot table features than the modern one. A number of things in your .XLS version tell me to open it in the newer version in which it was created. My pivot table creation wizard is very clearly different to the one you describe instructions for although I have got something near a table like yours now The sum of amount appears in column A rather than column B in your version and more significantly (I think) following your instruction g, I cannot have multiple selections for the TYPE filter button. I'm very grateful for your help and persistence with me but I think I will have to give up. The thought of changing to a newer version of Excel fills me with dread with the possibility of my many many other spreadsheets breaking. I might set up a sandbox on another sacrificial PC and try a newer version for my own satisfaction during the tedium of the forthcoming holiday or shall I just get out the Monopoly set....... Best Wishes LW In my experience, backwards compatibility in Excel is fine (I can't remember ever having a problem) but it's the new features that get introduced in later versions that cause problems when you try to save it as .xls for use by an older version. I was sceptical at first about moving to Microsoft's subscription model for Office but, having recently signed up for another year, I'm now a fan. I buy a (discounted) new Office 365 licence each year on Amazon for about £60, which then keeps all 4 laptops in the household fully updated for Outlook / Word / Excel / Powerpoint / Access and a couple of others I forget, plus limitless automated back-up storage on OneDrive (£60 gets the 5 user licence version; I think a 1 user licence is about £40).
|
|
mikes1531
Member of DD Central
Posts: 6,453
Likes: 2,320
|
Post by mikes1531 on Dec 11, 2015 13:12:33 GMT
I was sceptical at first about moving to Microsoft's subscription model for Office but, having recently signed up for another year, I'm now a fan. I buy a (discounted) new Office 365 licence each year on Amazon for about £60, which then keeps all 4 laptops in the household fully updated for Outlook / Word / Excel / Powerpoint / Access and a couple of others I forget, plus limitless automated back-up storage on OneDrive (£60 gets the 5 user licence version; I think a 1 user licence is about £40). I'm in a similar position. Microsoft's new subscription model is quite attractive to me, and it's probably helping them as well. But I don't think the OneDrive cloud backup is limitless -- Microsoft announced changes last month. If you have lots of digital photos and videos, you may find you hit the limit, though the space limit still looks quite generous to me. There's one more potential limit that doesn't get a lot of publicity, and that's on the number of files you can backup. I came across one mention of a 20,000 file limit on the Business version of the service that was written about a year ago. I don't know if there's now a limit on the Consumer version of the service and, if so, what that limit is, but I'd probably find a file count limit would be the one I'd hit before I'd hit a size limit.
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Dec 11, 2015 13:21:14 GMT
I was sceptical at first about moving to Microsoft's subscription model for Office but, having recently signed up for another year, I'm now a fan. I buy a (discounted) new Office 365 licence each year on Amazon for about £60, which then keeps all 4 laptops in the household fully updated for Outlook / Word / Excel / Powerpoint / Access and a couple of others I forget, plus limitless automated back-up storage on OneDrive (£60 gets the 5 user licence version; I think a 1 user licence is about £40). I'm in a similar position. Microsoft's new subscription model is quite attractive to me, and it's probably helping them as well. But I don't think the OneDrive cloud backup is limitless -- Microsoft announced changes last month. If you have lots of digital photos and videos, you may find you hit the limit, though the space limit still looks quite generous to me. There's one more potential limit that doesn't get a lot of publicity, and that's on the number of files you can backup. I came across one mention of a 20,000 file limit on the Business version of the service that was written about a year ago. I don't know if there's now a limit on the Consumer version of the service and, if so, what that limit is, but I'd probably find a file count limit would be the one I'd hit before I'd hit a size limit. I don't think their new 1TB limit is going to pose me any problems (I believe it's 1TB per user, so the kids' photos will be their problem!)
|
|
mikes1531
Member of DD Central
Posts: 6,453
Likes: 2,320
|
Post by mikes1531 on Dec 11, 2015 13:36:44 GMT
I'm in a similar position. Microsoft's new subscription model is quite attractive to me, and it's probably helping them as well. But I don't think the OneDrive cloud backup is limitless -- Microsoft announced changes last month. If you have lots of digital photos and videos, you may find you hit the limit, though the space limit still looks quite generous to me. There's one more potential limit that doesn't get a lot of publicity, and that's on the number of files you can backup. I came across one mention of a 20,000 file limit on the Business version of the service that was written about a year ago. I don't know if there's now a limit on the Consumer version of the service and, if so, what that limit is, but I'd probably find a file count limit would be the one I'd hit before I'd hit a size limit. I don't think their new 1TB limit is going to pose me any problems (I believe it's 1TB per user, so the kids' photos will be their problem!) I have a folder that's used to save certain web pages. The way they're stored means all of the various icons, buttons, etc. on the page are saved as individual small GIFs or JPGs. The total space taken by that folder is 737MB -- i.e. less than 1GB -- but the folder contains 51,666 files. If there's a limit on the number of files, I expect I'd have a problem with OneDrive. (I've not tried it. I signed up with Carbonite a number of years ago and I've been happy with their service so I've stayed with them.)
|
|
adrianc
Member of DD Central
Posts: 10,019
Likes: 5,147
|
Post by adrianc on Dec 11, 2015 16:28:52 GMT
I have a folder that's used to save certain web pages. The way they're stored means all of the various icons, buttons, etc. on the page are saved as individual small GIFs or JPGs. The total space taken by that folder is 737MB -- i.e. less than 1GB -- but the folder contains 51,666 files. Isn't it just easier to print the pages as a PDF?
|
|
ablender
Member of DD Central
Posts: 2,204
Likes: 555
|
Post by ablender on Dec 11, 2015 16:34:00 GMT
Thanks again SteveT, It appears that my prehistoric version of Excel 2002 has less pivot table features than the modern one.A number of things in your .XLS version tell me to open it in the newer version in which it was created. My pivot table creation wizard is very clearly different to the one you describe instructions for although I have got something near a table like yours now Hi, I am using openoffice and it works fine for me. It is free software so just download it and use it.
|
|
adrianc
Member of DD Central
Posts: 10,019
Likes: 5,147
|
Post by adrianc on Dec 11, 2015 16:50:54 GMT
Hi, I am using openoffice and it works fine for me. It is free software so just download it and use it. LibreOffice is vastly preferable to OpenOffice.org these days. Some political reasons, some technical reasons.
|
|
|
|
Post by GSV3MIaC on Dec 11, 2015 21:35:18 GMT
Thanks for the spreadsheet SteveT, however it gets its knickers in a twist when my transaction report lacks 'Withdrawal' or 'Cashback' entries (I get #Ref! errors on the last sheet, since it can't find any entries). Not a big deal, just a tad inelegant.
Oh wait .. the fix .. yep replace the GETPIVOTDATA("Amount",$A$5,"Type","Withdrawal") (etc) with 'iferror(GETPIVOTDATA("Amount",$A$5,"Type","Withdrawal"),0) (every case where there might be no data)
|
|
|
|
Post by ladywhitenap on Dec 11, 2015 21:52:13 GMT
Hi, I am using openoffice and it works fine for me. It is free software so just download it and use it. LibreOffice is vastly preferable to OpenOffice.org these days. Some political reasons, some technical reasons. I've just installed LibreOffice and the original spreadsheet works well. As an aside, an option during installation will prevent LibreOffice from automatic association with Microsloth documents so that whilst you are evaluating LO you can control which application opens which document - very nice! LW
|
|
mikes1531
Member of DD Central
Posts: 6,453
Likes: 2,320
|
Post by mikes1531 on Dec 11, 2015 21:57:56 GMT
I have a folder that's used to save certain web pages. The way they're stored means all of the various icons, buttons, etc. on the page are saved as individual small GIFs or JPGs. The total space taken by that folder is 737MB -- i.e. less than 1GB -- but the folder contains 51,666 files. Isn't it just easier to print the pages as a PDF? Possibly. But I don't know whether the files might get huge because they contain graphics, or whether I might lose the embedded links. It's certainly something to consider the next time I have to save one of those pages. Thanks for the suggestion, adrianc.
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Dec 12, 2015 7:21:14 GMT
Thanks for the spreadsheet SteveT, however it gets its knickers in a twist when my transaction report lacks 'Withdrawal' or 'Cashback' entries (I get #Ref! errors on the last sheet, since it can't find any entries). Not a big deal, just a tad inelegant. Oh wait .. the fix .. yep replace the GETPIVOTDATA("Amount",$A$5,"Type","Withdrawal") (etc) with 'iferror(GETPIVOTDATA("Amount",$A$5,"Type","Withdrawal"),0) (every case where there might be no data) Neat solution. I've been with SS long enough to have every transaction type going, so I hadn't considered that eventuality.
|
|
adrianc
Member of DD Central
Posts: 10,019
Likes: 5,147
|
Post by adrianc on Dec 12, 2015 9:52:15 GMT
Isn't it just easier to print the pages as a PDF? Possibly. But I don't know whether the files might get huge because they contain graphics, or whether I might lose the embedded links. It's certainly something to consider the next time I have to save one of those pages. Thanks for the suggestion, adrianc . Yes, you'd lose links - but you could always just bookmark the relevant ones...
|
|
gt94sss2
Member of DD Central
Posts: 281
Likes: 137
|
Post by gt94sss2 on Dec 12, 2015 16:47:51 GMT
Microsoft have backtracked a bit on the changes announced last month though their unlimited service is still going - though OneDrive users need to click on a special link to benefit from their change of heart.
|
|
