LinkBack URL
About LinkBacks
Hello, will i be able to use the clipboard function in Excel 2010 to copy and paste a pivot table as values yet retain its formatting? Is there any other work around this? Thanks
Hello, will i be able to use the clipboard function in Excel 2010 to copy and paste a pivot table as values yet retain its formatting? Is there any other work around this? Thanks
Yes there is indeed- http://www.contextures.com/excel-piv...pyformatvalues
Don
Please remember to mark your thread 'Solved' when appropriate.
Thanks for your response. The link you sent me doesnt work on Excel 2007 and beyond. I am on version 10. Do you know of any other way to do this? Thanks
That link only applies to versions 2007 and later.
It doesnt work, many users have posted comments saying that it doesnt work in version 10. I have tired this numerous times. The clipboard works but the data is pasted without any formatting. Thanks
In fact in 2010 all you need do is paste the values and then paste the formatting.
Does it matter if its over 5000 lines of data? For some reason i am not able to get the formatting . Thanks
I do not have a pivot table that size for testing but it certainly works with smaller ones.
Thanks, can i send you my file ?
You may attach it here. If you save it in .xlsb format you may attach files up to 9.7MB it seems.
Is there a way that we can turn off the data connection , its coming from an external source. once i paste it can i turn off the connection? Thanks
Attached is the file. Thanks
can you please try and copy and paste it to the next tab and see if it works with the formatting? and send it back to me? Thanks
I see what you mean. Fortunately the workaround is a simple one. Copy the main body of the table excluding the report filter area and paste that to the other sheet, first as values, then as formats. Then copy the page fields across as a second step.
Re: copying pivot format to another file
WOW that works!! I think when i was trying to copy even the filter area that wasnt working. Thanks for your help and persistently staying on top of this.
You're welcome.
HiI am sorry to bother you again. Do you know of a way to select and entire column in Excel and be able to go down to the last row containing data when there are empty cells in between. Currently i am doing Shift + End + Down Arrrow but it stops at the first empty cell and then i have to keep on repeating this till i get to the last row of data. Thanks
I would generally select the first cell, scroll down to the bottom and then Shift+Click the last cell in the column. There are workarounds if you have an adjacent column that is fully populated but I find the scroll method as easy as any.
Re: copying pivot format to another file
Thank you that works, just what i was looking for. Appreciate the prompt response.
You're welcome.
Thanks for the rep too and the glowing recommendation!
Hi there,
Could you please help me with this formula. I get the vlook up part but not sure why the count if is being used here? what is this formulat trying to do? Thanks
=IF(COUNTIF(Country!$A$5:$H$150,"XIA 3 Titanium"),(VLOOKUP("XIA 3 Titanium",Country!$A$5:$H$150,3,FALSE)/1000),0)
It would appear to be an attempt to handle the error if the VLookup value is not present. I would suggest simply
=IFERROR(VLOOKUP("XIA 3 Titanium",Country!$A$5:$H$150,3,FALSE)/1000,0)
Thank you again for your prompt response. Appreciate it.
Hi, are you aware of any issues with Office 365? We are currently installing it in our office and a lot of users complain of Excel and Outllook crashing. If you know anything about this would you mind sharing or pointing me in the right direction? Thanks
I'm sorry- I have no real experience of anything beyond Office 2010 as that is what our company uses.
Thank you . Appreciate your honest response.
Hi, I am back again. I hope you wont mind helping me with this. Is there a way to sort multiple columns high to low in a pivot table. Attached is my pivot table and i am trying to sort it high to low for the 3 columns that has values. CYTD Sales, YTD PTQ% and YTD Growth%
This isn't really related to the original question at all and ought, I suspect, to be a separate thread.
Anyway, I don't really follow what you want- you can't sort all three columns high to low simultaneously since their sort orders would contradict each other.
Thank you. Yes, this is not related to the previous questions. Is there a way to sort all three columns outside of a pivot by using conditions or if statements? In other words, what i am trying to do is get the rep who has $3M in sales and has reached 100% of his quota and or has more than 5% growth. Thanks
That sounds like a filter rather than a sort. If you select a cell to the right of the top of your pivot table, then expand your selection to the left to include the pivot table, you can apply an autofilter and filter as you wish.
Thank you. Would you mind showing me on the file i sent you how to do this? I am at a loss here. Thanks and sorry for the trouble.
Here is your file with the autofilter added. You can now click any of the dropdowns to filter that column by any criteria you wish.
Thank you so much. You just made my day . Appreciate your help. Have a great weekend.
Thank you- you too!
Hi, I am trying to extract data using multiple criteria. How can i do this using if and or statements.
Attached is the data set with the criteria in the next tab. I am able to do this in Acess but do not know how to get this done in Excel.
Would you be able to help me with this?
Thanks
Please ask it as a new question.
Thanks, i was able to get help on this one.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks