Hi,

How do I stop a pivot table doing this:

call date call time VAT Total
27/01/2007 12:06:00 standard rate 0
12:08:00 standard rate 0.015
13:00:00 standard rate 0
15:01:00 standard rate 0.002
* 15:05:00 standard rate 0.002
* standard rate 0.004
* standard rate 0.001


* These 3 calls were made at the same time, but I need the "call time" to be against all 3 rows and not just the top one. Even when I choose "merge cells" Excel can still only read the call time against one of the records.

How do I fix this?

Cheers
ALi.

Hi Ali

Thanks for your question.

I'm not quite clear on what it is that you are wanting the pivot table to show, as the idea of a pivot table is that it groups/summarises data rather than showing individual items.

You are welcome to email the relevant Excel file through to forum AT microsofttraining.net to my attention if this will make it easier for me to see what you are wanting to achieve.

Please put the following address into the body of the email as well so your email can be matched to your forum post:
http://www.microsofttraining.net/post-2798-how-do-i-stop.html

Amanda

Hi Ali

Thanks for sending the spreadsheet through.

Would you be able to select the cell showing the time (15:05), unmerge it by clicking on the merge button again, then copy the time into the two cells underneath?

Otherwise if this information is being extracted from a list of everyone's calls, where there is a column recording the caller's name, you could just do a filter on the list so it shows just the particular user's calls that you are interested in.

To do this, go to Data - Filter - AutoFilter, then select the person's name from the dropdown arrow above the name column.

I hope either one of these suggestions helps - please get back to me if you require further assistance.

Amanda

Hi Amanda,

The first suggestion is too labour intensive as I have to do this for 100 bills.

Your second is fine except the pivot table instantly presents the information, how I want it, without the need to shuffle around the raw data (it contains a lot of stuff not needed).

Ultimately I would like to have a list of employees and have a macro that takes the first name in the list, places it as the name in the pivot table (or selects it as the filter if using your 2nd suggestion), then copy paste values (as the individuals should not have access to all the data), and email that sheet to that person. Then have the macro move (automatically) to the next name on the list and do the same. Then the next name and so on.

It definitely seems do-able. There are several ways in which to do the different sections. But the formulas and macro commands have different limitations (which I'm not aware of until I've played with them for 2hours!!) and don't always work with other formulas, macros, or formatting. I am doing this by trial and error and it's taking a very long time.

The main problem, I have now, is emailing the sheets from Excel.

Cheers
ALi.

OK,

Now I remembered the "show pages" bit about pivot tables so can creat the bills very efficiently.

So a macro to copy/paste/values (so they can only see their data) is easy,

So it's just the email of all those sheets to the correct people that I have the problem with..... see:

http://www.microsofttraining.net/post-2821-a-macro-email-spreadsheet.html#2821

Any help with that would be great. Remember the list of people will change from month to month so it has to be able to get the names from the spreadsheets themsleves.

Cheers
ALi.

Hi Ali

Thanks for your subsequent posts, hopefully I will be able to refer this onto a colleague to have a look at tomorrow and see if he has any suggestions on the VBA side of things.

cheers
Amanda