Spreadsheet Page Blog Comments

Comment on: Arranging Your Data (by Wendy!)

Wendy! — 2010-03-14T19:43:55-07:00

I see an unbelievable amount of spreadsheets like this in spite of years of tips, templates and sample spreadsheets. They always come to me like this except for one difference: The category titles take up to 3 rows. Sometimes row 1, sometimes 1 & 2, or 2 & 3, sometimes 1, 2 & 3. If 3 rows are not enough the space bar is used instead of word wrap. I am convinced it is a learning disorder.

Job security.

Comment on: Excel 2010 VBA Enhancements (by chefe)

chefe — 2010-03-14T16:18:15-07:00

this is a great solution if people are using the function wizard... still, this solution doesnt solve the problem that the argument description shall be shown when typing the function directly in a cell ... i.e. w/o the wizard
cheers

Comment on: Excel 2010 VBA Enhancements (by chefe)

chefe — 2010-03-14T15:05:51-07:00

thank you so much doug!

Comment on: Arranging Your Data (by Jon Peltier)

Jon Peltier — 2010-03-14T08:42:49-07:00

"But it's actually the most efficient way to store data."

... short of building a relational database.

Comment on: Arranging Your Data (by teylyn)

teylyn — 2010-03-14T00:44:12-07:00

I can confirm this unfortunate practice. I post in some of the Excel Help Forums on the web and often see questions where people want to consolidate data that has been arranged in a format that is more like a report than a data table. Over and over again, I find myself urging them to use a simple, one-row-per-record format to enter data, instead of using different columns for different months, for example.

It's amazing how simple life can be if you use a normalised table for data entry and then run a pivot table for summarizing.

On the contrary, you'd need array formulae and other complicated constructs to pull the data from a table that is already organised as a report.

I've often wondered whether it is OK to tell people that their data structure sucks. You don't want to put them off, do you? But then again, for the learning curve, there should be two simple rules:

1. Data entry on one sheet, simple table structure
2. Data analysis on subsequent sheets

Comment on: Arranging Your Data (by chip)

chip — 2010-03-13T19:59:45-07:00

Yeah, I see that kind of thing a lot. Less in my company since we've done some pretty good training (though people new to the company find the effort to create organization annoying sometimes since it's not how they always did it.) We see the most problems with junior people who skip the training because their boss says their too busy, so they throw things together however they think best. But clients occasionally send something over for "a little help" and I usually end up writing a macro to locate the appropriate info and arrange it correctly. At first they are annoyed and concerned, until they see the power of being able to pivot, filter, extract, graph, Gantt chart etc. anything they may want.

Comment on: New Shortcut Key Discovered (by John Waldner)

John Waldner — 2010-03-10T16:38:23-07:00

Ctrl+D is the duplicate shortcut for every drawing object in Word and PowerPoint as well...I use it all the time in creating diagrams.

Comment on: Your Most Recent Excel Task? (by Bob Phillips)

Bob Phillips — 2010-03-10T08:18:15-07:00

@K.D. Bryant

it would be simpler to tell them to use the right tool!

Comment on: Your Most Recent Excel Task? (by Bob Phillips)

Bob Phillips — 2010-03-10T08:17:35-07:00

John B, you can still count them. I have an example at http://xldynamic.com/source/xld.CFConditions.html which counts those cells that are coloured a given colour, but it can be adapted.

Comment on: Recycling Calendars (by Yard)

Yard — 2010-03-10T04:58:42-07:00

Love this as a money-saving tip. However, what about Easter?

Comment on: 44 Excel Blogs (by ross)

ross — 2010-03-09T10:56:35-07:00

Colo written one in Excel, and accounding to this post at automate Excel you linked to it! ha it's a small world!
http://www.automateexcel.com/2004/08/15/excel_mod_rss_reader_add_in/

Comment on: Your Most Recent Excel Task? (by K.D. Bryant)

K.D. Bryant — 2010-03-08T00:02:11-07:00

A work colleague just came by and asked if I knew how to put a bullet list in to one Excel cell. This is what I came up with:

Use Alt-Enter to add the second line of text in to the cell. Start each line of text with a lowercase t. Format the lowercase t with Wingdings and voila, you have a diamond shape bullet list.

Comment on: Your Most Recent Excel Task? (by John B)

John B — 2010-03-05T11:48:11-07:00

I was trying to write a custom function that would count the number of italicized cells in a range. I discovered that a text string italicized by conditional formatting is not that same as actually italicizing the text.

Comment on: Annoying Window Behavior (by Fred C)

Fred C — 2010-03-01T21:05:09-07:00

I know I'm late here, but it has been snowing, one thing I have not noticed before is I have been using the sort the icon just to the right of the A-Z/Z-A icons. Do two or three or more sorts and the box starts moving each it gets bigger and bigger. I was using it with recording a macro the other day putting some survey results in ranking order. Knowing each day the results would change so instead of doing it manually I recorded a macro. The sort box kept getting bigger and biger I resized it about 6 times no way to lock it in place.

Comment on: Your Most Recent Excel Task? (by Daniel FR, Germany)

Daniel FR, Germany — 2010-02-26T09:50:41-07:00

I overlooked a movie script in an Excel file a few days ago, that was offered to me as a translation job (english-german). I declined because I had the choice to take this or another job, which I finally took. The other job was actually a Word file.

Comment on: Excel 2010 VBA Enhancements (by Doug jenkins)

Doug jenkins — 2010-02-25T15:47:02-07:00

chefe - This link:
http://newtonexcelbach.wordpress.com/2009/05/21/adding-function-categories-and-descriptions/

gives a simple, but limited, method of adding parameter descriptions in XL 2007 and earlier, and contains a link to another article giving a more complex, but more flexible method.

Comment on: Excel 2010 VBA Enhancements (by chefe)

chefe — 2010-02-25T06:56:59-07:00

is there really no way whatsoever, to achieve s.th. like that for xls07 users?

i assume your approach would also work for the small explanations under each argument when i begin typing a formula in a cell? (-> this is even more handy than having descriptions in the "function wizard")

cheers

Comment on: On This Day (by enrique antonio rosello coria)

enrique antonio rosello coria — 2010-02-24T21:46:00-07:00

My "experience" into Lotus 123 (1.0; 2.0;2.01;2.4;lotus smart suite -vw5- and lotus milenium....(memories) :
This program in all above versions, I think, was made to me... yes!
I´m inloved when learn thgh tutorials like that named "tutor123.bas"(affotunatelly I hold two copies yet...in low density floopy disks ! and works both yet!!fascinating tutorial!! like a jewell of those "freewares")all the literature (i did learn lotus by myself, helping with books , disks etc.)
lotus helpme to mantain in my job... help me in my life! and with Lotus i can discover the other sight of this life..."¡¡??" .....,(esotherism ... is the right way to write this word -metaphisics-).
So and much more : Thanks Lotus 123..!!
Best regards, enrique a. roselló coria.- acapulco, guerrero, mexico
Oh!; And sorry to my very bad english...please!

Comment on: New Shortcut Key Discovered (by Jon Peltier)

Jon Peltier — 2010-02-23T10:38:28-07:00

Nothing new under the sun:

Select a chart in Classic Excel using Ctrl+Click or Shift+Click, so the chart object is selected, then press Ctrl+D: Duplicate Chart!

Oh, and Ctrl+Drag copies a chart for me in 2007.

Comment on: New Shortcut Key Discovered (by Hui...)

Hui... — 2010-02-23T03:06:28-07:00

Bill, I'd skip 2007 and go straight to 2010

Comment on: New Shortcut Key Discovered (by John Walkenbach)

John Walkenbach — 2010-02-22T11:43:38-07:00

Ctrl+D still works for copying cells, Bill. But if a chart is selected, then Ctrl+D copies the chart.

Comment on: New Shortcut Key Discovered (by Bill)

Bill — 2010-02-22T11:21:56-07:00

Sorry to hear this. I use that shortcut to quickly duplicate the contents of a cell to the cell below w/o having to copy it.

We are due to upgrade to 2007 at work (yes, 2010 is on the horizon and we're just getting around to 2007) So thanks for the information.

Comment on: New Shortcut Key Discovered (by SB)

SB — 2010-02-22T10:33:22-07:00

first person in the world "outside the microsoft dev team" maybe? It is entirely possible that they themselves don't know about it
Useful shortcut!

Comment on: Greg Likes The Book (by Jeff Weir)

Jeff Weir — 2010-02-19T19:07:45-07:00

Must say that when I purchase a book on say Sql Server, then I'm often muttering to myself within minutes that the Author is "no Walkenbach"

Comment on: Excel 2010 VBA Enhancements (by steffan)

steffan — 2010-02-18T10:39:49-07:00

Great, now how do I get it to bring in Macros stored on the old macro sheet?

Comment on: Excel 2010 VBA Enhancements (by John Walkenbach)

John Walkenbach — 2010-02-17T09:31:28-07:00

Yes, you can do that. The MacroOptions method lets you specify help file and the topic ID as arguments. There's an example in my Power Programming With VBA book that uses a CHM help file.

Comment on: Excel 2010 VBA Enhancements (by SB)

SB — 2010-02-17T09:18:48-07:00

Can you add a link to help file with a context id when you click on "Help on this function" in the insert function wizard?

Comment on: 14 Years Old (by Jon von der Heyden)

Jon von der Heyden — 2010-02-17T02:16:46-07:00

Happy 14th birthday Jon!

Comment on: 14 Years Old (by Rog)

Rog — 2010-02-16T14:27:57-07:00

It's like going "Back to the future" - Happy birthday keep on going!

Comment on: 14 Years Old (by Debra Dalgleish)

Debra Dalgleish — 2010-02-16T08:44:36-07:00

Happy birthday, John, to you and your blog!

Comment on: 14 Years Old (by Phil)

Phil — 2010-02-16T08:20:29-07:00

This is nice, John *lol*

The little animated gif ad for the PUP (as a German it's quite difficult for me to write this down, because in my language it's a word for "fart" ) is lovely

Also that there is some marketing for a German version of your book.

Lovely, really lovely *ggg*

Phil

Comment on: Chess Game Viewer (by John Walkenbach)

John Walkenbach — 2010-02-15T17:21:28-07:00

It's an Excel 2007 files, but it might work if you've installed Microsoft's Office Compatibility Pack (which adds support for the new xlsx file format).

Comment on: Chess Game Viewer (by L. Quezada)

L. Quezada — 2010-02-15T16:05:18-07:00

It works on Excel 2003?

Comment on: Chess Game Viewer (by Daniel Ferry)

Daniel Ferry — 2010-02-15T10:15:33-07:00

Oscar-

You would use boolean logic with SUMPRODUCT.

I have a post on my blog that details how:

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

Regards,

Daniel Ferry
excelhero.com/blog

Comment on: Posted: Light Box Demo (by Ken Warthen)

Ken Warthen — 2010-02-15T08:59:55-07:00

John,

What's the "ThunderDFrame" reference in
hWnd = FindWindow("ThunderDFrame", ufcap)
about?

Ken

Comment on: Your Most Recent Excel Task? (by Phil)

Phil — 2010-02-15T07:40:09-07:00

I am checking contracts entered into an Excel list. To be honest, it's not that nice, because the data quality is very poor.

Hope to do some dashboarding in the evening (sounds like a new extreme sport, related to snowboarding or so, right? )

Comment on: Microsoft Office Is Obsolete? (by Phil)

Phil — 2010-02-15T07:37:20-07:00

Well, Joe seems to be in the lucky position not to do any operational work at all. Looks like all his business correspondence goes through email.

I use MS Word for preparing fax, letters and documentations. (maybe OO Writer is an option here)

I use MS Excel for tracking lists, project calculations, general project management etc. (I don't see an alternative in OO Calc. Oh, and by the way Joe, I don't want my sensitive business data stored on Google's servers utilizing Google Docs)

I use MS Outlook (because it comes with the Office Suite, Thunderbird would work the same for me to be honest)

I use MS Powerpoint to prepare management presentations or introduce our services to new customers (No, I don't want to touch LaTex although it may be capable of reproducing Powerpoint's results).

So this is my usage of the Office environment.

Comment on: Joe's A Fan (by Chris Rieckenberg)

Chris Rieckenberg — 2010-02-15T07:35:36-07:00

I also started with your Dummies book. It was exactly what I needed.
I work in Biotech. My first macro reduced a 4 hour manual data
manipulation process to 20 seconds. I have found a very nice niche
for myself. My fellow lab people do not understand VBA and do not
want to. The database guys are to busy to mess with it. I love it
and have a very valuable skill set. Thanks John.

Comment on: Chess Game Viewer (by Henry Rios)

Henry Rios — 2010-02-15T06:36:23-07:00

Wow!! espectacular! :O

Comment on: Your Most Recent Excel Task? (by Stephen)

Stephen — 2010-02-15T06:08:51-07:00

Further to #17: my Excel-generated numbers won the Eurolottery! Unfortunately it was only 6.70 UKP = 61 pence per syndicate member.

Comment on: Buzzword Bingo Card Generator Posted (by Jeff)

Jeff — 2010-02-15T06:04:57-07:00

The rest are awful too :-( why do people have to talk like this?!

Bingo

Comment on: Buzzword Bingo Card Generator Posted (by Bingo)

Bingo — 2010-02-15T06:03:54-07:00

Haha touch base, I HATE that phrase!! You're not climbing Everest - you're out getting a coffee and a donut.

Comment on: Chess Game Viewer (by Oscar)

Oscar — 2010-02-15T03:55:20-07:00

David Ferry,

Thanks for your answer! Your blog is outstanding! Very interesting.

How do you convert Countif(A1:A2,C3:F6) + CSE to boolean logic?

/Oscar

Comment on: Joe's A Fan (by Jason Sanchez)

Jason Sanchez — 2010-02-14T16:52:10-07:00

Hi John,

I am sure that Joe speaks for a lot of people. Your Excel books and your website are the best. Keep up the good work!!!

Jason

Comment on: Chess Game Viewer (by Daniel Ferry)

Daniel Ferry — 2010-02-14T16:28:00-07:00

@John-
Thank you very much for the kind words, and for showcasing the project on your blog!

@Oscar-
I try to avoid IF() functions as much as possible. They can lead to convoluted formulas. I prefer to reserve them for when there is no other alternative. Excel versions prior to 2007 only allowed the nesting of IF() functions to seven levels deep. While this is no longer a problem, I still feel it is better to reserve their use.

The REPT is only required if the output of a formula is text. The boolean logic is used in both text and numeric output. These approaches enable the calculation engine to march straight through the formula with no branching. They are easier to read; often shorter, especially the numeric output variety. I believe (but have not tested) that they are quicker as well.

I've have a post on my blog that goes into much more detail:

http://www.excelhero.com/blog/2010/01/i-heart-if.html

Regards,

Daniel Ferry
excelhero.com/blog

Comment on: Chess Game Viewer (by Oscar)

Oscar — 2010-02-14T12:34:04-07:00

Interesting! What are the advantages of using boolean logic and REPT() instead of IF()?

Comment on: Your Most Recent Excel Task? (by simlaoui elhassan)

simlaoui elhassan — 2010-02-14T10:38:03-07:00

right now!
i am creating an example for a friend to explain why index and equiv are more useful than vlookup.
long life to your books and to your style of writing.
by the way John ; is it difficult to write a book about excel?
what is your story about your first book ?
i plan to beggin writing in excel field.
yours

Comment on: Your Most Recent Excel Task? (by Anibal Fraquelli)

Anibal Fraquelli — 2010-02-14T09:20:21-07:00

Was programming (re-doing) some modules to use a different connection string to a server (we moved from SQL server to Oracle databases for some payroll reporting) 1 hour ago

And (on a side, we usually dont do this staff at work time updated my personal workbook of tango related to class students.

Comment on: Your Most Recent Excel Task? (by Ken)

Ken — 2010-02-13T15:43:23-07:00

I had a friend that had a mailing list, and wanted to print envelopes and postcards from the list, and keep track of what had been printed and when.

I created a sheet based Excel program that would allow him to print a single envelope or card, or a batch.

He can now print a range of records say 1-21 or 1,015 to 1,175, etc, or 1-21, 83-95 and it keeps track on the mailing list sheet when and what was printed.

I also made him a program that would create financial scenarios and then create a Power Point Presentation from the entries in Excel.

What use to take him four hours to complete now takes a whopping 15 seconds.

My friends say I'm amazing, I just tell them it's because I have bought and read all of John Walkenbacks books. It's so much easier when you learn from the master!

Thanks John!!

Comment on: Your Most Recent Excel Task? (by John Walkenbach)

John Walkenbach — 2010-02-13T10:09:04-07:00

OmarF and Dave, can't you create groups in the pivot table? Or maybe use a calculated field?

Comment on: Your Most Recent Excel Task? (by OmarF)

OmarF — 2010-02-13T08:30:40-07:00

Dave Roberts, #23. Your issue with business year is what singlehandedly stops me from using pivot tables for many of the things I do.

Comment on: Your Most Recent Excel Task? (by Michel)

Michel — 2010-02-13T07:55:17-07:00

10 minutes ago I updated my exercise log and learned from the various charts that my weight loss program is on pace for me to win my bet.

Last night I managed various lists of user stories/requirements and story points from an important client. For this, I used my favorite PUP function (alternate row shading), filtering, pivot tables and charting to validate the team’s burn-down and burn rates. The client is very happy!

Comment on: Your Most Recent Excel Task? (by Tom Gleeson)

Tom Gleeson — 2010-02-13T05:47:34-07:00

Two days ago; spent the afternoon trying to better understand a problem I've encountered with the Application.Caller feature in Excel 2010 and wrote a quick blog post detailing the problem http://blog.gobansaor.com/2010/02/11/excel-2010-application-caller-bug/

Tom

Comment on: Your Most Recent Excel Task? (by Murray)

Murray — 2010-02-12T14:39:48-07:00

Tabulated applications (rows) for a vacant position in my MD group against criteria (columns). Filtered on criteria. Used the workaround for saving a custom filtered view: View>Custom>Add View.

Gripe: This is OK until you add a new row to the sheet, then the
custom view fails to include the new rows in the filtered view.

Any fix for this limitation of Excel 2007?
Murray

Comment on: Your Most Recent Excel Task? (by Halford)

Halford — 2010-02-12T14:37:17-07:00

Recreated a set of pilot points for a groundwater flow model, estimated likely transmissivity between two streams, and plotted a bar chart of operating periods of evapotranspiration sites for a poster.

Comment on: Your Most Recent Excel Task? (by JIm)

JIm — 2010-02-12T12:02:55-07:00

I created a lottery analysis worksheet based on conversations at lunch - it uses web queries to update the jackpot information from the Illinois lotter website, and web queries from Bloomberg.com for current US Treasury quotes used to calculate lump-sum payouts. Added a simple form control button with a macro that executes the 'refresh all' when clicked.

Comment on: Your Most Recent Excel Task? (by m-b)

m-b — 2010-02-12T10:31:39-07:00

A couple of hours ago I used it to convert monthly budgets for 2010 to weekly budgets at work.

Comment on: Your Most Recent Excel Task? (by Jon Peltier)

Jon Peltier — 2010-02-12T10:31:18-07:00

I took the "Microsoft PowerPivot Excelerators Quiz" and it claimed I got one wrong.

Q: What's the shortcut for Inserting a Row.

Well, everyone knows that it's Alt-I-R, but the "official" answer is Alt-H-I-R. Turns out, they were asking specifically about Excel 2007, and everything in 2007 takes more effort, either more clicks, or moving the mouse further, or in this case, typing an extra character.

Alt-I-R still works in 2007, so it's actually the correct answer, since it's the shorter shortcut.

Comment on: Your Most Recent Excel Task? (by Bill)

Bill — 2010-02-12T09:42:59-07:00

John, unfortunately I don't know how to set up a web query but I'll look into it. Thanks.

Comment on: Your Most Recent Excel Task? (by Gregory)

Gregory — 2010-02-12T09:40:12-07:00

Yesterday I finished requested changes to client workbook by making VBA programming revisions and additions. (3.2 hours)

Comment on: Your Most Recent Excel Task? (by Omar)

Omar — 2010-02-12T09:16:51-07:00

I had a spreadsheet refreshing its data (pulling sales data out of our business system so I can do the monthly compensation reports for our sales people) so I dropped into my news reader and read this post. Earlier today, I reviewed a new spreadsheet with my boss for his input on changes to make (some call that critique) before going live with it.

Comment on: Your Most Recent Excel Task? (by John Walkenbach)

John Walkenbach — 2010-02-12T08:41:34-07:00

Bill, have you tried using a refreshable Web query to get those rates? It could save you 30 seconds every day!

Comment on: Your Most Recent Excel Task? (by Bill)

Bill — 2010-02-12T08:31:22-07:00

Just a few minutes ago at work. I copy and pasted today's foreign exchange rates (from on internet site) into a spreadsheet, which I then manually key into our computer system. The spreadsheet is kept for a record.

I use Excel all day at work for various tasks. Sure beats a 10 key calculator and column paper that I used when I first started in accounting 30yrs ago. It could take half a day to do what I do now in minutes, ei. getting a spreadsheet to balance.

Comment on: Your Most Recent Excel Task? (by Ben Welman)

Ben Welman — 2010-02-12T08:18:17-07:00

I worked at my VBA module "Sudoku helper" in Excel. I made a lot of progress today.

Comment on: Your Most Recent Excel Task? (by Hui...)

Hui... — 2010-02-12T08:00:43-07:00

I answer Question on Chandoo's Pointy Haired Dilbert Forums.
3 hrs ago, The last thing I did was a quick check of
=+SUMPRODUCT(1*(A2:A11>=E3)*(A2:A11<=E1)*(C2:C11="Closed"))
against the data set that had been put forward
I check each answer I write just in case.

Comment on: Your Most Recent Excel Task? (by Dave Roberts)

Dave Roberts — 2010-02-12T07:55:36-07:00

I converted weekly sales into quarterly sales for the last six quarters and created a pivot table chart. I wish it was easier to convert the data to fiscal quarters versus calendar quarters. Or maybe my boss could convert the fourth quarter of 2009 into the first quarter of 2010 in his head obviating the need for conversion.

Comment on: Your Most Recent Excel Task? (by Mike Alexander)

Mike Alexander — 2010-02-12T07:52:17-07:00

1 Day ago. To Figure out how many pages and screenshots on a word template equals a 350 page book.

Comment on: Your Most Recent Excel Task? (by SilverSTreak)

SilverSTreak — 2010-02-12T07:27:26-07:00

I used Excel yesterday to record service tasks done to my motorcycle and record refuel information for mileage tracking.

Comment on: Your Most Recent Excel Task? (by Dick Kusleika)

Dick Kusleika — 2010-02-12T07:06:05-07:00

30 minutes ago I updated Results.xls for my Thursday night four-point pitch league. Still tied for first

http://dailydoseofexcel.com/excel/Results.pdf

Comment on: Your Most Recent Excel Task? (by Andy)

Andy — 2010-02-12T06:57:37-07:00

Just update my weight tracking spreadsheet to include time spent on my new elliptical machine. I had to update my chart from a pivot chart to a regular chart with dynamic series to be able to plot my weight and exercise time on the same chart but two axes.

Comment on: Your Most Recent Excel Task? (by Glenn)

Glenn — 2010-02-12T06:38:27-07:00

I used it 2 days ago to do check digit schemas with modular arithmetic to calculate ISBNs and UPC codes.

That same file also does Caesar ciphers, random Caesar ciphers, rail fence encoding, linear shift encoding and I am trying (and failing) to get it to do matrix encoding.

Yes I am a teacher who uses it in the classroom!

Comment on: Your Most Recent Excel Task? (by Stephen)

Stephen — 2010-02-12T06:07:50-07:00

3 hours ago I updated my daily weight log - same as yesterday, down on week start, but up on year start. Oh well.

Yesterday I updated my list of CDs (remember them?) and LPs (remember them?). Lifetime spend is in five figures (UKP). Ouch!

2 days ago I generated (pseudo-)random lottery numbers for the Eurolottery. At well over 100 million UKP this weekend, you'll know if I win by the absence of future comments!

Comment on: Your Most Recent Excel Task? (by Doug jenkins)

Doug jenkins — 2010-02-12T03:49:54-07:00

Finished an application to calculate creep and shrinkage effects over time in a reinforced concrete section, and posted it to my blog.

5 minutes ago.

Comment on: Your Most Recent Excel Task? (by Raymond Allan)

Raymond Allan — 2010-02-12T01:27:02-07:00

Just finished writing a query / program to track sales forecast changes on a day to day basis, the program also acts as a safety net in the event the demand analyst has made an error.

Comment on: Your Most Recent Excel Task? (by Mike Woodhouse)

Mike Woodhouse — 2010-02-12T01:26:08-07:00

I just started off a task that downloads a pile of daily files from S&P relating to a set of stock indices that we created. The workbook wil load the data into Oracle, then recalculate the indices one at t a time so I know what S&P got wrong this time...

Comment on: Your Most Recent Excel Task? (by Jeff Weir)

Jeff Weir — 2010-02-12T00:38:33-07:00

I just crashed it. It's the only thing I drive while drunk.

Comment on: Your Most Recent Excel Task? (by Joe)

Joe — 2010-02-12T00:25:52-07:00

I'm using Excel right now - I'm trying to figure out a way to show, on a scatter chart, not only the X & Y co-ordinates, when I hover my mouse over the data points, but the locations next to the data as well . . .

Comment on: Your Most Recent Excel Task? (by Gordon)

Gordon — 2010-02-11T23:28:01-07:00

I'm just up, so the last time I used Excel was about 13 hours ago. I was using PerfMon (supplied with PED) to tweak an existing application's database exchanges. Worked well too, got a report that was taking about 57 seconds down to under 4

Comment on: Your Most Recent Excel Task? (by MikeD)

MikeD — 2010-02-11T23:14:02-07:00

Putting a friendlier interface on an existing sheet so my son can analyse Lat/Lon data from GPS. The sheet already worked, but I couldn't explain it to anyone else.

- Scale 1:1 scatter chart of the track line
- Gradient-colour the track line with speed (red - fast, blue-slow)
- Pick segments of track with mouse to average data
- Save data as labels, if req'd
- Copying formulae to match input data rows

Comment on: Your Most Recent Excel Task? (by Patrick)

Patrick — 2010-02-11T22:01:47-07:00

My last Excel task was rewriting a VLOOKUP formula to use a MATCH expression for the return column in the lookup table instead of COLUMN so I could avoid physically reordering some data.

Comment on: Your Most Recent Excel Task? (by Mathias)

Mathias — 2010-02-11T21:47:51-07:00

Just finished writing an Excel VSTO app which forecasts the uptake of products soon to be introduced on the market, under various scenarios - sent it out yesterday evening, taking a break from Excel today - and tracking the time spent on the project in a worksheet, of course!
In parallel I am reading the Carter and Lippert VSTO book, and playing with a VSTO Excel add-in idea which hopefully will end up being a tutorial...

Comment on: Your Most Recent Excel Task? (by Jack Freeman)

Jack Freeman — 2010-02-11T21:40:17-07:00

To continue, the last thing I did in Excel was to build in error-trapping routines for a spreadsheet system that is used by some 1,000 users in my company, to perform their project management and accounting functions. This required multiple nested if statements to identify and report the errors locally within the spreadsheet itself to avoid failure of the submitted data to Oracle Projects. I have worked with updating, enhancing, and maintaining for two years now. I also support the users via a company-wide help desk. When the systems are updated, it requires automatically pushing the spreadhseet package via internet to some 4,000 computers.

Comment on: Your Most Recent Excel Task? (by Jack Freeman)

Jack Freeman — 2010-02-11T21:33:20-07:00

I just purchased your book Excel 2007 Power Programming with VBA. At the bottom of page xiii, there's a reference to an URL with a list of errors in the book: http://j-walk.com/ss/w. This link is apparently no longer in existenc, and search as I might, I can't find it on your new site.

I have found what I think are some typos, and just wanted to get a complete listing of those that others may have found.

As an aside, I'm new to VBA programming, but am an experienced programmer and Excel user from way back. I have some older spreadsheet tools I've developed and want to anhance their functionality. I am in the process of developing some training materials for civil engineers. I want to include these enhanced Excel spreadsheet tools, so that the student can focus on the concepts rather than the software, as many many versions of the proprietary software exist. The concepts learned can then be implemented by the student using the proprietary software that their employers use.

Comment on: Your Most Recent Excel Task? (by Charles Urban)

Charles Urban — 2010-02-11T20:40:31-07:00

just now I used it as a data storage for information of a game I am playing. And I use it every day a work for data mining and organizing. Just five hours ago organizing information for a project I am working on

Comment on: Your Most Recent Excel Task? (by chip)

chip — 2010-02-11T20:34:34-07:00

I used Excel about 4 hours ago for the most mundane of tasks. I opened an attachment to an email. The Excel doc has our company's senior employees vacation schedule for the current week, and I was trying to see if the reason a colleague was not replying to emails, calls, and cell phone calls was because he was out of town or on vacation. He wasn't. Never found him.

And actually I wasn't looking for him myself. I was birddogging for an employee working from home who had IMed me wondering if I knew where the guy was.

Comment on: Your Most Recent Excel Task? (by Debra Dalgleish)

Debra Dalgleish — 2010-02-11T20:04:27-07:00

An hour ago, I updated a client's price lists. I click a few buttons that run macros, to create customized versions of the list for different regions.

Comment on: Your Most Recent Excel Task? (by John Keegan)

John Keegan — 2010-02-11T19:54:10-07:00

The last thing I did was solve a problem I had in the back of my mind for a long time. I created a mega formula that had several nested IF statements and concatenated text.

I am an accountant and one of the key financial statements I have to create is a Statement of Cash Flows. The text used as row labels changes depending on the sign of the values on that row. For example, the row that shows the change in the ending balance of Trade Accounts Receivable over a two year period could be any one of the follow:
(Increase) decrease in trade accounts receivable
(Increase) in trade accounts receivable
Decrease in trade accounts receivable

I created a formula that compares the values on the row to see if the current year's change was an increase, decrease, or stayed the same, to see if the prior year's change was an increase, decrease, or stayed the same, and concatenate the remaining required text.

Previously I edited the labels manually as the values changed. Now it is automatic.

Comment on: Your Most Recent Excel Task? (by gkeramidas)

gkeramidas — 2010-02-11T19:28:58-07:00

ran an app i wrote for a client to see his daily sales in a pivot table and chart

Comment on: Posted: Unlinking A Pivot Table (by John A)

John A — 2010-02-09T10:04:53-07:00

I've tried pasting special formats as described on a number of spreadsheets, and it never works for me. Is there some other setting I need to check within options?

Comment on: 44 Excel Blogs (by John Walkenbach)

John Walkenbach — 2010-02-08T07:51:03-07:00

Rick, I can't think of a single reason why anyone would want an RSS reader in Excel.

Comment on: Fan Mail From Texas (by Rick)

Rick — 2010-02-08T07:23:00-07:00

Power Programming is one of my favourites, but sometimes when I need some quick reference I'll check into Excel VBA PRogramming for Dummies.

I do really like the expansion in Power Programming though... and great examples. I was impressed with the sorting examples.

I'm a computer instructor, sorting excites me!

Actually not really, but still.. great sorting examples.

Comment on: 44 Excel Blogs (by Rick)

Rick — 2010-02-08T07:19:58-07:00

Hi John (er... Mr. Walk),

I'm becoming one of your new fans. Any chance you'll come up with an RSS reader in Excel? Or would that be written in Word?

All the best, looking forward to your books for Excel 2010.

Comment on: Annoying Window Behavior (by Hui...)

Hui... — 2010-02-06T21:40:20-07:00

Thanx John
All these years of frustration are now history.

Comment on: Annoying Window Behavior (by Jared)

Jared — 2010-02-05T10:00:44-07:00

John,

I like the idea of your Bookmark feature. Since I wasn't aware of it at the time, I wrote a proc that adds a "Favorites" to the menu bar. It lets you add files and/or folders, and it even lets you group them into categories. Its main weakness is that to edit the list of favorites, you have to unhide Personal.xls, manually change the list, save and re-hide Personal.xls, then either restart Excel or run the Workbook_Open procedure for Personal.xls. Ugh. But I only change it once a month, so no big deal.

But getting back to Excel annoyances, what's the deal with the "Cannot empty the clipboard" message that appears sometimes after a Paste operation?

Comment on: Annoying Window Behavior (by John Walkenbach)

John Walkenbach — 2010-02-05T09:01:59-07:00

Hui, in the Name Manger, press F2 exit point mode and get into edit mode. Since you mention the Name Manger, I assume you're using Excel 2007. That dialog box is resizable.

Comment on: Annoying Window Behavior (by Hui...)

Hui... — 2010-02-05T08:10:37-07:00

For me its the use of the Arrow Keys in the Name Manager
Pressing the arrow keys in the formula edit line should move the cursor in the Formula edit line, not move the current cell and then put the Cell Reference into the formula.

I'd also like to be able to resize the Name Manager window to make it better suit long named formulas.

I know there are lots of add-ins that fix some of the above, but you don't always have access to these.

Comment on: Annoying Window Behavior (by John Walkenbach)

John Walkenbach — 2010-02-04T12:00:19-07:00

Jared, you'd probably love the Bookmark feature in my PUP add-in. It's my favorite feature, and it has completely replaced the recent file list for me.

Press Ctrl+Shift+B, and the list opens at the active cell. You can scroll through the list, or use a hot key that you assign to a specific file.

Comment on: Annoying Window Behavior (by Jared)

Jared — 2010-02-04T10:57:59-07:00

The fact that if a file on the "recent files" menu gets moved or deleted, and you click on the file in the menu, all you get is an error message; it doesn't ask you if you'd like to remove the file from the list, as it does with add-ins.

Since I'm feeling grumpy, I'll also mention that I don't care for the way that "pinning" recently used files in Excel 2007 works. I'm not in front of my home PC w/Excel 2007 now, so I can't say for sure--but doesn't it re-order the pinned items as they're opened? If I like a file enough to use it really frequently, I'd like to grab it from the same position in the list each time. And also to be able to manually move the items in the list by clicking/dragging, as you can with Favorites in IE.

Comment on: Annoying Window Behavior (by Joe)

Joe — 2010-02-04T08:40:09-07:00

What I find annoying is that in Excel 2007, when I zoom out (let's say to 80% )and then use the "page break preview" and switch back to "normal" it forgets that it was on 80% and goes back to 100%. Maybe I just don't know how to fix it . . . but I find it annoying

Comment on: Annoying Window Behavior (by chrisham)

chrisham — 2010-02-04T08:01:53-07:00

What's very annoying, is the Formula Evaluator window, which is not expandable. Also I wish they had a BACK button command on it, that would enable you to go back, rather than start the formula evaluator from the begining again.