Open Side Menu Go to the Top
Register
Ask me anything about Microsoft Excel Ask me anything about Microsoft Excel

07-01-2012 , 02:58 PM
Do you have the pivot table built/summarized in the format you're looking for? e.g. State in column A, this year's sales in column B and this year's sales in column C.

My only real tip to provide would be to avoid using the GETPIVOTDATA function, it is a mess from my experience, especially if you ever need to refresh the pivot table. If you're creating a fomula in column D, don't just hit "=" then click on a cell in the pivot table. Instead, type in the formula manually... "=B3/C3-1".
Ask me anything about Microsoft Excel Quote
07-01-2012 , 03:16 PM
you can turn getpivotdata off (would recommend you do). it's an option in the top left somewhere on the pivot table ribbon.
Ask me anything about Microsoft Excel Quote
07-01-2012 , 04:46 PM
I do have the table oriented the way I want it to.

Ideally it would be best to get the percent difference in the pivot table itself, so that it can pivot and such with it.

However, that doesn't seem to be possible. I guess best thing to do is find the format of the pivot table I like and then make some extra formulas outside of it to show what I want. Maybe even copy the pivot table data into a normal table first once I like what I see.

Thoughts, suggestions?

Thanks for replies, too.
Ask me anything about Microsoft Excel Quote
07-01-2012 , 05:59 PM
Why is this not working with calculating fields?

I just tried it and it worked, if I understood you correctly.
Ask me anything about Microsoft Excel Quote
07-01-2012 , 08:02 PM
In the source data each row has the number of units sold in a given month in a given state. So one column says month, one state, one is quarter, one is year, etc.

Created a pivot table from the data, comparing one year with the other. When I originally made a calculated item with the formula for percent difference (=(present year-past year)/past year), it did return the percent difference, but it also created some formatting errors (like doubling the month columns in the pivot table) that couldn't be fixed manually due to the fact that the table is a pivot table. The new pivot table was not fit for presentation.

However, I have been messing around some more with the calculated item solution. While some orientations of the table produce errant table segments, if you orient the table in other ways it looks just fine. I think I may play around with groupings, too.

Thanks for all the help.
Ask me anything about Microsoft Excel Quote
07-03-2012 , 11:26 AM
Is it possible to make a cell 2 different colors?
Ask me anything about Microsoft Excel Quote
07-03-2012 , 12:33 PM
my boss wants me to get a better tracking system for my quotes as I am a sales manager for a company that sells equipment in the wastewater industry.

right now I am using excel but my CRM is basically just a list with some key data and a small space for some notes...

is there any better way to expand this list to be more usable and track projects better or am I better off signing up for online CRM program that has more features and is probably more the type of stuff that my boss wants to see?

any suggestions
Ask me anything about Microsoft Excel Quote
07-04-2012 , 05:51 AM
Quote:
Originally Posted by LondonBroil
Is it possible to make a cell 2 different colors?
AFAIK no.

Quote:
Originally Posted by durango155
my boss wants me to get a better tracking system for my quotes as I am a sales manager for a company that sells equipment in the wastewater industry.

right now I am using excel but my CRM is basically just a list with some key data and a small space for some notes...

is there any better way to expand this list to be more usable and track projects better or am I better off signing up for online CRM program that has more features and is probably more the type of stuff that my boss wants to see?

any suggestions
I am not sure what you are asking here.
But I do think it's possible to expand the thing with macros for example.
Pivot table makes the table easier to analyze.

The question is a bit broad or maybe I misunderstand.
Ask me anything about Microsoft Excel Quote
07-04-2012 , 06:18 AM
Just wanted to say I enjoy your thread. As a master degree student, for a long time I didnt really find a lot I liked to do, or that even made much sense for that matter. It wasnt until I studied logistics for a year that I realized how awesome of a program Excel is in all its simplicity. Since then I´ve picked up advanced courses and intend to be an Excel guru.

With 61 pages im afraid to ask a question though, as its probably answered already.
Ask me anything about Microsoft Excel Quote
07-04-2012 , 06:33 AM
a) Only 10 pages
b) I genuinely believe that every Excel problem is a bit different in its own sense. Kinda like a pokerhand. So ask away.
c) This is not my thread. So zomg might feel differently.
Ask me anything about Microsoft Excel Quote
07-06-2012 , 02:32 PM
I have an Excel sheet that contains both a pie chart and line chart. Both of these charts need to dynamically update based on a user setting. The difficulty here is that the user setting can actually change how many rows of data there are for the chart (either 1 or 4). I used the following page to figure out how to dynamically update the pie chart - http://chandoo.org/wp/2009/10/15/dyn...t-data-series/. However, updating the line chart is more difficult. In this case, updating the setting can actually change how many series of data there are (in this case, each row is a series as each data point represents a month). As I know this a bit confusing, I will give an example below.

User Setting 1 - Pie Chart
A 50

User Setting 2 - Pie Chart
A 50
B 30
C 10
D 10

User Setting 1 - Line Chart

A 100.5 75 45

User Setting 2 - Line Chart
A 34 56 45
B 34 231 345
C 32 23 123
D 123 34 123

What I cannot figure out is how to dynamically include only the first row in the line chart when user setting 2 is selected. If this is still unclear, please let me know and I will try and explain it differently.
Ask me anything about Microsoft Excel Quote
07-06-2012 , 04:05 PM
=If("User Setting"=1,#N/A,30)

If you give a cell the value "#N/A" (no quotes) then it won't display in a graph.
Ask me anything about Microsoft Excel Quote
07-06-2012 , 04:13 PM
Let me see if I understand what you're saying. I have four series (under Select Data Source, they are listed under Legend Entries (Series).). If all of cells in a particular series say N/A, the corresponding line in the chart will not display? What about the actual legend? Won't it leave a blank square that is the colour of the data points that would appear if there was data?
Ask me anything about Microsoft Excel Quote
07-06-2012 , 04:25 PM
I'm trying to wrap my head around a pie chart that has only one value.
Ask me anything about Microsoft Excel Quote
07-06-2012 , 04:30 PM
It's a special case that just needs to display properly in case it is selected. The pie chart with four values is typically used but there has to be something shown when the single value option is selected.
Ask me anything about Microsoft Excel Quote
07-06-2012 , 04:43 PM
Quote:
Originally Posted by zgall1
...If all of cells in a particular series say N/A, the corresponding line in the chart will not display?
No, not "N/A" it needs to be "#N/A" (again, no quotes).

Basically, Excel treats #N/A as a number that doesn't exist.
Quote:
What about the actual legend? Won't it leave a blank square that is the colour of the data points that would appear if there was data?
I can only suggest that you try it.
Ask me anything about Microsoft Excel Quote
07-07-2012 , 11:13 AM
Let's see if you wizards can help me with this

I have a template in excel that I use as a basis for data entry for a side business I've been running.

What I've been doing is this:

Right Click on Sheet 1 (named, but whatever)
Click "Move or Copy"
To Book: New Book
Check "Create Copy"
Press OK

Then I type the data into the copied file etc.

Is there a faster way of doing this? A macro perhaps (whatever that means) to avoid this 5 step process and 'streamline' it? I'm always looking for ways to improve efficiency.
Ask me anything about Microsoft Excel Quote
07-07-2012 , 12:27 PM
Are you saving your template as an actual template, ie .xlt? Then every time you open it you just save as a new xlsx with whatever changes you want and it won't save over your original xlt.
Ask me anything about Microsoft Excel Quote
07-07-2012 , 01:20 PM
Have you recorded a macro of this already?

Anyways, this should do what you want:
Quote:
Sub CreateNewWBVersion()
Dim ws As Worksheet
Dim wb As Workbook
Set ws = ThisWorkbook.Sheets(1) 'Sheet number might be different to what you want to copy, you can replace 1 with the worksheet name put it in "" though, i.e. Sheets("TemplateSheet")
Set wb = Workbooks.Add
ws.Copy before:=wb.Sheets(1)
End Sub
Ask me anything about Microsoft Excel Quote
07-07-2012 , 04:22 PM
You know what dudes - I didn't realize how easy it is to record a macro, I recorded myself doing the steps I mentioned just now and made it "cntrl N" to activate and that took care of it.

sorry for asking such a dumb question and wasting your time
Ask me anything about Microsoft Excel Quote
07-12-2012 , 06:08 PM
This is probably an exceedingly easy thing to do, but I'm a total Excel newb.

I have a targeted list of names, addresses, phone numbers, all in varying cities. I would like to sort this data in the most efficient way possible to go door to door selling a product.

How can I sort this data first by city, then by street name? City and address are already pulled apart into separate columns. Part of the problem I envision is that the address starts with a number, so I'd somehow have to get the filter to ignore that number and alphabetize just based on the words.

Bonus points if anyone has a tip on how to further sort the street names to a most efficient walking route. I realize Excel can't do this, but there must be some sort of mapping program that for instance a mailman would use to go to a list of houses in the most efficient manner possible.

Thank you so much!!!
Ask me anything about Microsoft Excel Quote
07-12-2012 , 06:14 PM
If your address is in A1 and there's a space between the numbers and the street name, use this to get just the street name:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

There's a space between the quotes.

Then sort the whole table by the city column and then this new column.
Ask me anything about Microsoft Excel Quote
07-13-2012 , 11:11 AM
I have a similar question to the template. It involves a cheque requisition form for accounts payable. basically i have a template set up, which you fill out and then make a payment to the account payable you owe. My boss wants to to save each form in a folder, (kind of like hand histories from HEM) by month, or quarter, and by company.

My problem is that everytime i fill out a new form, and go to save it in its specific folder. it says, "this file already exists". is there anyway that i can set up the excel document to automatically save into its specific folder by name and date?

For example: we have monthly phone bill payments to "Telephone company"

I open the Accounts payable excel template and fill out what i need in order to make the bill payment. to keep as an electronic record on the computer, i would save it under folder AP>Telephone Company>July and it stores it there. Is there anyway to automate the saving? as in creting the folder for itself each month for each vendor? the reason i ask this is that we have a lot of vendors and doing it manually will be a huge pain and hastle. Not to mention, if we get the same bill payment in the same month (ie. Courier fee) i would save template in AP>Courier company>July but a file previously exists in there already with the same name. Actually, I guess i can name the files by their date? like DD/MM/YYYY...does anyone have any suggestions?

Thanks
Ask me anything about Microsoft Excel Quote
07-13-2012 , 11:18 AM
What is it about update KB2597166 that makes excel sorting such a pain in the ass? Any ideas?


I've had to remove it from several PC's at work here.
Ask me anything about Microsoft Excel Quote
07-13-2012 , 11:57 AM
Quote:
Originally Posted by Justine Bieber
I have a similar question to the template. It involves a cheque requisition form for accounts payable. basically i have a template set up, which you fill out and then make a payment to the account payable you owe. My boss wants to to save each form in a folder, (kind of like hand histories from HEM) by month, or quarter, and by company.

My problem is that everytime i fill out a new form, and go to save it in its specific folder. it says, "this file already exists". is there anyway that i can set up the excel document to automatically save into its specific folder by name and date?

For example: we have monthly phone bill payments to "Telephone company"

I open the Accounts payable excel template and fill out what i need in order to make the bill payment. to keep as an electronic record on the computer, i would save it under folder AP>Telephone Company>July and it stores it there. Is there anyway to automate the saving? as in creting the folder for itself each month for each vendor? the reason i ask this is that we have a lot of vendors and doing it manually will be a huge pain and hastle. Not to mention, if we get the same bill payment in the same month (ie. Courier fee) i would save template in AP>Courier company>July but a file previously exists in there already with the same name. Actually, I guess i can name the files by their date? like DD/MM/YYYY...does anyone have any suggestions?

Thanks
Yeh it's definitely do-able something like, adding a save button with the following code (you will need to modify it but should put you on the right path

Code:
    Dim fileName As String
    Dim filePath As String
    Dim fileDate As String
    Dim fullPath As String
    Dim vendorName As String
    
    fileDate = Format(Date, "YYYYMMDD")
    filePath = "C:\"
    vendorName = ThisWorkbook.Sheets("Sheet1").Range("A1")
    
    filePath = filePath & vendorName & "\"
    fileName = "NewWorkbook " & fileDate

    If Len(Dir(filePath, vbDirectory)) = 0 Then
        MkDir filePath
    End If

    fullPath = filePath & fileName & ".xlsx"

    ThisWorkbook.saveas fullPath
you would also need to disable the usual method of saving, somthing like

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "Please use the button provided to save this template"
    Cancel = True
End Sub
There's probably a way to override the beforesave event and force the save to that location but im not 100% sure of the top of my head
Ask me anything about Microsoft Excel Quote

      
m