Ask me anything about Microsoft Excel - Page 62 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

Josem · 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.

General Tsao · 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.

CrazyEyez · 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.

Spurious · 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

General Tsao · 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

ShoreRoll · 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!!!

CrazyEyez · 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.

Justine Bieber · 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

Chaos_ult · 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.

zomg · 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

Justine Bieber · 07-13-2012, 12:15 PM

Quote:

Originally Posted by zomg

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

Thanks zomg, im not an expert at programing or vba but i will try and play around with it.

Also, i need to have the template as an excel file, because of the formulas that are attached to it. but would it be possible to save it in pdf format?

And to ensure that no one tries to go into the files and tries to change some numbers around i would have to come up with some security passcode for it. would just selecting to protect the sheet suffice, in your opinion? This info isnt TOPSECRET or anything, but as long as the numbers dont change, as it may mess up our records. we will be having hard copies however to compair.

Thanks agian!

zomg · 07-13-2012, 12:27 PM

Quote:

Originally Posted by Justine Bieber

Thanks zomg, im not an expert at programing or vba but i will try and play around with it.

Also, i need to have the template as an excel file, because of the formulas that are attached to it. but would it be possible to save it in pdf format?

And to ensure that no one tries to go into the files and tries to change some numbers around i would have to come up with some security passcode for it. would just selecting to protect the sheet suffice, in your opinion? This info isnt TOPSECRET or anything, but as long as the numbers dont change, as it may mess up our records. we will be having hard copies however to compair.

Thanks agian!

As part of the save event remove/disable the button that you added the save code too, then whenever they save it will say "use the button" but they can't use the button thus preventing them from making any changes

Your Mom · 07-18-2012, 12:17 AM

I have three column of data I am putting into a pivot table. The first column is a bunch of point spreads, the second column is a bunch of totals, the third column is either TRUE or FALSE. I want a pivot table that breaks TRUE/FALSE results down first by point spread and then by a range of totals.

So for example, it would be for point spreads of -3, then it would give me TRUE/FALSE results for totals below 45, totals between 45.5 and 60, and totals above 60. Any way to do that? Hopefully, this makes sense to someone.

TJay · 07-18-2012, 12:40 AM

I have 2 identical sheets that are currently sorted from 1 to 1000 in column A, with other info in column B.

On sheet A I put on an auto filter, and filter column B for "contains x" (this reduces my view to only seeing 400 lines.)

I add in a new Column of information for all 400 lines (column C) on sheet A.

I now sort sheet B so that it only shows the same 400 lines in the same order.

I copy column C from sheet A, and want to paste it into sheet B so that it obviously matches up with the 400 lines that contain X.

When you paste this, it will screw the sheet up because it will ignore the "contains" filter when you paste, and will end up getting the 400 lines of new info in all of the wrong rows.

How can you make it just paste to the 400 rows you can see? This is something I encounter about twice a day at work. I've obviously simplified it (usually the information I need to paste is from 2 completely different spreadsheets, so I can't simply filter them to be the same), and it would save me a ton of time if there was a shortcut for having to copy/paste line by line.

Thanks

Josem · 07-18-2012, 05:34 AM

Hi,

I have Column A (over 90,000 rows) with values that I want to graph in an ordinary line chart. Excel is limited to 32,000 values in a line chart.

Thus, I'd like Column B to reference every 500th value in Column A:

A1
A501
A1001
... and so on

Is there a way to do this automatically with a formula of some sort?