Ask the EXCEL guy ...

Miscellaneous and Personal Stuff I Must Share
81

[QUOTE=dwc1970]

…

For my purposes, it would be a lot easier if I could output the data to a single line and not have line breaks occur after every 240 characters.
[/QUOTE]

dwc, I can’t reproduce the problem. When I try your examples, they come out exactly the same way that I saved them.

I’m running the latest version of Excel for the Mac, and I remember that one or two versions back they removed the 256 character limit in a single cell. I used to hate that limit, is why I remember. That may have something to do with what you are getting. I don’t know when (or if) they’ve removed the limit in the PC version.

Can you type more than 256 characters into a single cell? If so, can you combine the cells of interest into one long sentence in a single cell (or column of cells) using either of the techniques I detailed in my last post, and then just copy them and paste them into a word processor (e.g. Word, NotePad, TextEdit) for further use?

w.

82

I have a spreadsheet in which I have employed the “New Web Query” function. I import the price of the stocks I own. I have a “refresh” macro written.

The problem is that I have to have a Crazy Browser browser open to the site it queries when I refresh the query. Since I constructed the page, I have switched browsers.

  1. Is there a way to set up a web query that doesn’t require having a browser open at all?

  2. If no, is there a way to have it look for the page in the browser i currently use (Firefox)?

83

[QUOTE=intention]
I’ve been driving Excel since it first came out (1986?), and have created enormously complex spreadsheets, VBA functions and subs, and charts out the wazoo. I’ll answer questions until my brain gets tired …

I’m working on the Mac (for which Excel was first written), so a bit of translation may be necessary. I’ve driven Excel a lot on PCs as well, but I don’t have one handy to verify everything. They are almost identical, though, so it shouldn’t be a problem.

My best to everyone, give back to the Web.

w.
[/QUOTE]

I am creating a scatterplot chart. I want to add error bars to one of the data series. These are the instructions in the online help:

Add error bars to a data series

  1. Click the data series to which you want to add error bars.
    2 On the Format menu. Click Selected Data Series.
  2. On the X Error Bars tab or on the Y Error Bars tab, select the options you want.

My problem is that I can find Selected Data Series in the Format menu. I am using Excel X on the Mac.

I realize that the menu items change as different parts of the spreadsheet are selected, so I have tried to carefully follow the above instructions-still can’t see anything.

Can you step me through the process of adding error bars? I have a column with the values for the error bars, but don’t think I am to select that column yet. I have selected the data column that is being plotted in an effort to see the Selected Data Series format option, but nothing like that appears.

84

I found it. You have to select the line in the graph, not the data series in the underlying table. Sigh.
Works now.

[QUOTE=rbroome]
I am creating a scatterplot chart. I want to add error bars to one of the data series. These are the instructions in the online help:

Add error bars to a data series

  1. Click the data series to which you want to add error bars.
    2 On the Format menu. Click Selected Data Series.
  2. On the X Error Bars tab or on the Y Error Bars tab, select the options you want.

My problem is that I can find Selected Data Series in the Format menu. I am using Excel X on the Mac.

I realize that the menu items change as different parts of the spreadsheet are selected, so I have tried to carefully follow the above instructions-still can’t see anything.

Can you step me through the process of adding error bars? I have a column with the values for the error bars, but don’t think I am to select that column yet. I have selected the data column that is being plotted in an effort to see the Selected Data Series format option, but nothing like that appears.
[/QUOTE]

85

[QUOTE=rbroome]
I found it. You have to select the line in the graph, not the data series in the underlying table. Sigh.
Works now.
[/QUOTE]

Yes. You can also get to the dialog by double clicking on the line in the graph, or by right-clicking on the line, or by selecting the line in the Chart Toolbar.

w.

86

[QUOTE=Trunk]
I have a spreadsheet in which I have employed the “New Web Query” function. I import the price of the stocks I own. I have a “refresh” macro written.

The problem is that I have to have a Crazy Browser browser open to the site it queries when I refresh the query. Since I constructed the page, I have switched browsers.

  1. Is there a way to set up a web query that doesn’t require having a browser open at all?

  2. If no, is there a way to have it look for the page in the browser i currently use (Firefox)?
    [/QUOTE]

Trunk, take a look at the example queries in the Microsoft Office : Office : Queries folder. You can open them and edit them with any text editor.

At least on my machine (Mac OSX, Office 2004) they do not require having a browser open. They just go and get the data (values, links, etc). However, if I click on one of the links the web query has retrieved, then it opens the browser.

If it is opening the wrong browser at that point, you need to reset your default browser. This is done from Safari, using the Safari : Preferences : General.

If this doesn’t help, then take two aspirins and call me in the morning … just kidding, let me know.

w.

87

[QUOTE=dwc1970]
For my purposes, it would be a lot easier if I could output the data to a single line and not have line breaks occur after every 240 characters.
[/QUOTE]

I don’t have a solution, but I do have a fairly easy workaround that I use a lot.

Add a column at the end of your sheet and enter some wonky character like a tilde (~) in each row of that column. Then export in the prn format.

In a text-editor (I use UltraEdit; you can do the same thing in Word or another text-editor, but I don’t think Notepad or Wordpad will do it), do a mass find&replace to remove all the carriage returns. Then do another to replace all the tildes with carriage returns. Voila.

88

Riddle me this, EXCEL man…

I have my default set to “precision as displayed.” So why the hell does EXCEL not actually display “precision as displayed” answers sometimes? I’ve learned to go into tools/options/calculation and uncheck the box, then go back and re-check the box, and that fixes the problem. It drives me crazy since my work relies on correct precision as displayed values. It must be a bug, right?

89

This will be simple for you, I’m thinking.
I’m working on a time sheet for work. Time in (B3), lunch out(B4), lunch in(B5), time out(B6). Total for the day is cell B8 “=(B4<B3)+B4-B3+(B6<B5)+B6-B5”
Monday through Friday. Total weekly hours should equals 40. I made the program to catch any overtime or undertime.

So I come in at 8.15am, take lunch from 12 to 12.30, and leave at 4.45pm. Using the above formula, I get 8 hours total for the day. But, i have to type in 4.45 AM in cell B6 to make the total hours work. If I type in 4.45 PM, excel converts the cell to 16.45 and changes my total hours to read “8:00 AM” and changes my weekly total from 100:00:00 to 88:00:00. (see below)

Cell B8 is formatted as “custom: h:mm AM/PM” - that’s the only way I’ve been able to get it to read 8 hours.

Cells B3 through B6 are formatted “custom: h:mm”

Cell G8 (weekly total) is formatted as “[h]:mm:ss”

Total weekly hours can only be read as 100:00:00, but I want them to read 40:00 (or at least 40), and I want to type in 4.45 PM rather than AM. I’ve played around with the various format types and sent the program to my grandmother, who’s pretty knowledgeable in Excel, yet she couldn’t do what I wanted.
See anything I need to change?

90

Here’s a very simple question – it’s probably already been asked in one form or another, but I’m new to spreadsheets so bear with me.

Say I have a some data like this



acct#     order#      date
10        2342        12/06/06
10        2456        12/06/06
13        3421        12/06/06
14        3562        12/06/06
14        3568        12/06/06
14        3578        12/06/06
14        4121        12/06/06
19        4222        12/06/06
19        4861        12/06/06
23        4862        12/06/06

Let’s say I don’t really care about the order # – I just want to see a list of accounts that ordered today. So I basically want to delete rows with matching account numbers:



acct#     order#      date
10        2342        12/06/06
13        3421        12/06/06
14        3562        12/06/06
19        4222        12/06/06
23        4862        12/06/06

I can sort of kludge this with the subtotal function, but I’m hoping there’s a simpler way that I can just delete the rows I don’t want.

Thanks.

91

[QUOTE=Ghanima]
Riddle me this, EXCEL man…

I have my default set to “precision as displayed.” So why the hell does EXCEL not actually display “precision as displayed” answers sometimes? I’ve learned to go into tools/options/calculation and uncheck the box, then go back and re-check the box, and that fixes the problem. It drives me crazy since my work relies on correct precision as displayed values. It must be a bug, right?
[/QUOTE]

Not a clue, Ghanima, I’ve never seen this before. I’d report it to Microsoft as a bug.

Having said that, EXCEL sometimes does go off the rails and starts doing weird things. My solution has always been to quit EXCEL and restart.

All the best,

w.

92

[QUOTE=Phantom Dennis]
Here’s a very simple question – it’s probably already been asked in one form or another, but I’m new to spreadsheets so bear with me.

Say I have a some data like this



acct#     order#      date
10        2342        12/06/06
10        2456        12/06/06
13        3421        12/06/06
14        3562        12/06/06
14        3568        12/06/06
14        3578        12/06/06
14        4121        12/06/06
19        4222        12/06/06
19        4861        12/06/06
23        4862        12/06/06

Let’s say I don’t really care about the order # – I just want to see a list of accounts that ordered today. So I basically want to delete rows with matching account numbers:



acct#     order#      date
10        2342        12/06/06
13        3421        12/06/06
14        3562        12/06/06
19        4222        12/06/06
23        4862        12/06/06

I can sort of kludge this with the subtotal function, but I’m hoping there’s a simpler way that I can just delete the rows I don’t want.

Thanks.
[/QUOTE]

Your question is quite similar to a question I answered above for cometothedarksidewehavecookies. I made a spreadsheet for cookies to experiment with here.

See if that solution helps, if not, come back and ask again.

w.

93

[QUOTE=LunaV]
This will be simple for you, I’m thinking.
I’m working on a time sheet for work. Time in (B3), lunch out(B4), lunch in(B5), time out(B6). Total for the day is cell B8 “=(B4<B3)+B4-B3+(B6<B5)+B6-B5”
Monday through Friday. Total weekly hours should equals 40. I made the program to catch any overtime or undertime.

So I come in at 8.15am, take lunch from 12 to 12.30, and leave at 4.45pm. Using the above formula, I get 8 hours total for the day. But, i have to type in 4.45 AM in cell B6 to make the total hours work. If I type in 4.45 PM, excel converts the cell to 16.45 and changes my total hours to read “8:00 AM” and changes my weekly total from 100:00:00 to 88:00:00. (see below)

Cell B8 is formatted as “custom: h:mm AM/PM” - that’s the only way I’ve been able to get it to read 8 hours.

Cells B3 through B6 are formatted “custom: h:mm”

Cell G8 (weekly total) is formatted as “[h]:mm:ss”

Total weekly hours can only be read as 100:00:00, but I want them to read 40:00 (or at least 40), and I want to type in 4.45 PM rather than AM. I’ve played around with the various format types and sent the program to my grandmother, who’s pretty knowledgeable in Excel, yet she couldn’t do what I wanted.
See anything I need to change?
[/QUOTE]

The key is understanding the way that EXCEL handles time. Both dates and times are stored as fractional days. This can be a problem, as you have seen.

The key is to change the total in B8 from fractional days to hours, by multiplying the total by 24. That way, you don’t need to deal with the peculiarities of time formatting. The formula should read:

=24*(B4-B3+B6-B5)

Once you change the formatting of cell B8 back to decimal formatting from time formatting, it will give you the correct answer.

My best to you,

w.

94

[QUOTE=intention]
I’ve been driving Excel since it first came out (1986?), and have created enormously complex spreadsheets, VBA functions and subs, and charts out the wazoo. I’ll answer questions until my brain gets tired …

I’m working on the Mac (for which Excel was first written), so a bit of translation may be necessary. I’ve driven Excel a lot on PCs as well, but I don’t have one handy to verify everything. They are almost identical, though, so it shouldn’t be a problem.

My best to everyone, give back to the Web.

w.
[/QUOTE]

I really hope this thread is still active!

I have another question. I am plotting a line graph with x axis a date-month. The dates in the spreadsheet have the day (1 or 30 I have tried both. ie 1/1/06 or 1/30/06). Excel puts the data point in the middle of the month. I want to move it to the begining of the month on the chart. Any ideas?
BTW, if you plot 2/30/06 Excel crashes. Not immediately, but reliably. Took me a while…

95

[QUOTE=ParentalAdvisory]
How do I copy raw data into a merged cell without getting the errors:
Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway?

OK Cancel

<thinking Excel is clever enough to resolve it, I click OK.>

…and then:

Cannot change part of a merged cell. (Seriously, why give me the option if it cannot be done MS?)

I get this annoying error with anything I try to copy and paste into a merged cell.
[/QUOTE]

My 2ç on this and it will or will not work depending on which version of excel you have:

if you just mark the stuff you want to copy, ctrl+c, go to where you want to paste and ctrl+v, you get this error.

Try with an additional step: mark the cell you want to copy and now its value is displayed at the top of the screen, right below the buttons… copy it from there and then you may be able to paste it.

96

Okay, this should be a simple one. Is there any way to jump between workbooks without have to click on one below the other, or going up to “Window” and using the drop down? I can jump between different windows (such as an Excel spreadsheet and a Word doc and the Dope) using Alt-Tab. Is there any keyboard shortcut that will allow me to do that within one Excel tab?

StG

97

Is there a thread like this, but for Word?

98

This seems like it should be pretty easy.

For some reason, someone in my office wants to convert a document that is in Word Perfect into Excel. The document is mostly (but not totally) tables, usually with three columns.

I’ve been asked how easy this would be. I thought it would be pretty easy, copy and paste. And in fact if I do it column by column it works, except (1) if there are two lines in a given column it comes out in two separate rows in Excel, and there are a lot of these; and (2) it’s going to be pretty tedious to do this for 200-some pages. (Possible, but tedious.)

Is there a way to do this for the whole document, or at least a whole page rather than separate columns?

Thanks.

99

[QUOTE=Hilarity N. Suze]
This seems like it should be pretty easy.

For some reason, someone in my office wants to convert a document that is in Word Perfect into Excel. The document is mostly (but not totally) tables, usually with three columns.

I’ve been asked how easy this would be. I thought it would be pretty easy, copy and paste. And in fact if I do it column by column it works, except (1) if there are two lines in a given column it comes out in two separate rows in Excel, and there are a lot of these; and (2) it’s going to be pretty tedious to do this for 200-some pages. (Possible, but tedious.)

Is there a way to do this for the whole document, or at least a whole page rather than separate columns?

Thanks.
[/QUOTE]

Oops, I need an edit function, let this one go too quickly. When I said “two lines in a given column” above, I meant simply that some of the rows in the original table have more than one line, but they come out as two rows in Excel rather than one row with two lines. Sorry.

100

[QUOTE=StGermain]
Okay, this should be a simple one. Is there any way to jump between workbooks without have to click on one below the other, or going up to “Window” and using the drop down? I can jump between different windows (such as an Excel spreadsheet and a Word doc and the Dope) using Alt-Tab. Is there any keyboard shortcut that will allow me to do that within one Excel tab?

StG
[/QUOTE]

CTRL + TAB

This will cycle between all of your open Spreadsheets.

CTRL + PgUp or PgDwn

This will move you up or down through the Worksheets in a single Spreadsheet.