Shantanu's Blog

Database Consultant

March 31, 2005


Excel Startup problems

Unfortunately, many Excel users sometimes start experiencing startup problems with their Excel: File not found errors, compile errors, GPF's, Excel refusing to load etc.
In such a case, you may try opening Excel without any addins or hidden workbooks.

1) Use the Automation switch to start Excel without loading addins or workbooks from the XLSTART folder:

Click Start, Run,
"C:\Program Files\Microsoft Office\Office\Excel.exe" /Automation

Quotes mandatory, Note you may have to change the path. If your error disappears, either an add-in or a (hidden) workbook that is loaded upon Excel's start bothers you.

2) Open Excel in Safe mode

Start, Run,
"C:\Program Files\Microsoft Office\Office\Excel.exe" /Safe

The safe switch ensures Excel starts without opening any addins or using your menu customisations. If this works, chances are that your toolbar customisation file is corrupt. Locate all files with extension .xlb and rename the extension(s) to something like .old
Now try and start Excel again.

March 27, 2005


MySQL Keys explained

Maximum key length is 500 bytes!

March 26, 2005


Blogger Power

Do You Know Chris Pirillo? You Should ... :: AO: "People have to understand that every blogger is a PR agency. I've been saying this for at least a year. You have to treat the conversation as if it were with a major media organization."


The New Marcom Game

The New Marcom Game: "The World Wide Web's strange new economy is where Adam Smith and Karl Marx finally meet. For Smith the Internet provides a 'simple system of natural liberty [that] establishes itself of its own accord,' and for Marx it finally puts the means of production in the hands of the workers. In other words, it achieves the irony of capital-free capitalism: enterprise so free that it requires almost zero capital to start and run a new business."

March 25, 2005


Frequently Asked Questions About Microsoft Excel

Frequently Asked Questions About Microsoft Excel: "To increase a range of values (prices, for example) by 5 percent, enter 1.05 into any blank cell. Select the cell and choose Edit, Copy. Then select the range of values and choose Edit, Paste Special. Choose the Multiply option and click OK. Then delete the cell that contains the 1.05."

Excel Tip:
You can use Ctrl+Alt+Tab in excel to add left-indentation to a cell. And if you have enough fingers, Ctrl-Alt-Shift-Tab unindents.
More shortcuts here...

Excel shortcuts chart


Show Formulas in Cell Comments

Select the cells and then run this macro:

Sub CommentThem()
Dim cell As Range
On Error Resume Next
On Error GoTo 0
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell.Formula <> "" Then
cell.Comment.Visible = False
On Error Resume Next 'fails on invalid formula
cell.Comment.Text Text:=" Address: " & cell.Address(0, 0) & Chr(10) & _
" Value: " & cell.Value & Chr(10) & _
" Format: " & cell.NumberFormat & Chr(10) & _
" Formula: " & cell.Formula
On Error GoTo 0
End If
Next cell

End Sub

1) Start excel
2) Open the file that contains the formulas.
3) From Tools choose Macros - Visual Basic Editor.
4) Double click on "ThisWorkbook" and paste the macro.
5) From File choose "Close and return to excel"
6) Select the cells containing formulas.
7) Choose Tools - Macro - Macros.
8) Run the "comment them" Macro.
9) You will see a red rectange next to each cell.
10) Pass your mouse on any cell.
11) You will see the current formula in the yellow comment box.
12) If you want a static comment box, Right click on the cell and choose "Show Comment".
13) Resize the box if it overlaps on other comment boxes.

March 24, 2005


SAJAX - Simple Ajax Toolkit

SAJAX - Simple Ajax Toolkit by ModernMethod - XMLHTTPRequest Toolkit for PHP: "Sajax is an open source tool to make programming websites using the Ajax framework — also known as XMLHTTPRequest or remote scripting — as easy as possible. Sajax makes it easy to call PHP, Perl or Python functions from your webpages via JavaScript without performing a browser refresh."


adaptive path » ajax: a new approach to web applications

adaptive path » What is AJAX?: "Instead of loading a webpage, at the start of the session, the browser loads an Ajax engine — written in JavaScript and usually tucked away in a hidden frame. This engine is responsible for both rendering the interface the user sees and communicating with the server on the user’s behalf. The Ajax engine allows the user’s interaction with the application to happen asynchronously — independent of communication with the server. So the user is never staring at a blank browser window and an hourglass icon, waiting around for the server to do something."

March 15, 2005


Fast Company Now

Fast Company Now: "Every generation thinks the way their generation did things is better, she said, and it's ignorant not to want to adapt with the times. Of course I know that, I replied; it wasn't that I was arguing against progress - I guess what I was really arguing for was nostalgia: It's not that our way was better, but there seems to be a purity lost."


Consulting - Nothing but being there!

Consulting - Beyond the Bunny Slippers: Wrap-up: "There's an old Woody Allen quote that says that '90% of life is just showing up.' After years of watching many consultants come and go, I can tell you that it's easy to remain competitive when so many consultants can't even muster for that 90%.
You can be the most technically skilled, creative, artistic, wonderful consultant that ever lived, but if you don't answer e-mails, return phone calls, or show up for meetings, soon you will have no customers. They will find a less talented, but more conscientious person to do their work."


How to Add Pop-up Lists to Your Word Documents

Do you re-use some of your documents over and over again, making slight changes just before you print, fax, or email it each time? For example, do you send the same basic letter to each new customer, but edit the letter each time so that it refers to the specific product purchased by that customer?
How about pointing at key words or phrases in your documents and simply right-click the mouse to switch to some other word or phrase?
How to Add Pop-up Lists to Your Word Documents

Word fields tips:
To make it easier to see the fields embedded in a document, set Field Codes to be shaded Always. To do this, choose Tools|Options|View|FieldCodes|Always.

To navigate from field to field with the keyboard press F11. To navigate backward from field to field, press Shift+F11.

March 13, 2005


Microsoft knows how to ship software - or does it really?

Markl's Thoughts: Shipping Software: "I would argue that Microsoft used to know how to ship software, but the world has changed... The companies that 'know how to ship software' are the ones to watch. They have embraced the network, deeply understand the concept of 'software as a service', and know how to deliver incredible value to their customers efficiently and quickly."

Does Microsoft really know how to ship the software?
Does it know how to name a product?
I recently read an entry on Joho the Blog about "Read view" that will be helpful for viewing the word document.
On further investigation I found that "Read view" is supported only by Word 2003. I had to open word to check the version. It was Office XP! How many versions of Word has microsoft "shipped" in the last 5 years? Which one is the latest? What does the number next to "word" mean? If a fairly regular user like me can't figure it all out, how does the end user going to know and learn all the advance features?

March 11, 2005


Customizing the Google News Display

Google's added a new feature to its news page that allows you to customize the layout. Check out the page at , over on the right. Also check out the excellent "News Alerts" service.

March 10, 2005


Fast Company Now

Fast Company Now: "Solutions to tough challenges start with powerful questions. Powerful questions have the ability to shake people out of stale ways of thinking and critically reflect on faulty assumptions. Wise leaders know this. And as a result, they don't give people answers. Rather, they challenge them to reflect on evocative questions so that they can find the best answers on their own."

March 07, 2005


Quickly Typing Dates into Cells

Typing a large amount of data into cells can be tiring, especially if it includes a series of dates. Type the day (serial number only) into cell A1 and add the following formula to insert the month and year into cell B1:


Type a full number in the cell and change it to a date by using the following formula:


For example, type 122203, the result is 12/22/03.

Is there a function that returns the name of the worksheet?
The following formula displays the workbook's full path along with the worksheet name:


For example, this formula might return something like:
Returning only the sheet name requires a more complex formula:

=MID(CELL("filename"), FIND("]",CELL("filename"))+1, LEN(CELL("filename"))-FIND("]", CELL("filename")))

March 05, 2005


3 Excel tips

How to convert Text to number?

If you have a set of numbers that are really text, you can quickly convert those numbers into a number format. To do so:
1. Activate any empty cell or group of empty cells if you have more than one number to convert.
2. Go to the standard toolbar and hit Edit -> Copy.
3. Select the range of numbers that need to be converted.
4. Go back to the standard toolbar and hit Edit -> Paste Specal -> Add -> OK.

How to add rounded corners and shadow to an Excel chart?
1. Right click near the edge of a chart and select Format Chart Area...
2. On the Patterns tab, place a checkmark in the boxes next to Shadow and Round Corners.

How do I remove Characters from Left or Right Side of Variable Length String?

If you always know the length of a string, it's easy to remove characters from it. Example: If you have a string that is 10 characters in the cell A1 and you want to remove 1 character from the Left side, simply return the right 9 characters:

=Right(a1, 9)

This doesn't work for a variable length string, or one which you don't know beforehand it's length. In this case you can use the formula (Length - N) to designate how many characters to extract:

=Right(a1, Len(a1) - 1)

Where 1 is the number of characters to remove from the left side of the string. This will return the string minus the left most character.
To remove characters from the right side of a string, replace Right with Left.'s passed 100,000 meetings's just passed 100,000 meetings!
Not only that, they're evolving tools for organizer, building a services-based business model, and tweaking the back end a bit, says Myles Weissleder, Communications VP.
Oh, and they've added RSS feeds for local events...mapped to zip codes.

March 04, 2005


NYPL Digital Gallery

NYPL Digital Gallery: "NYPL Digital Gallery provides access to over 275,000 images digitized from primary sources and printed rarities in the collections of The New York Public Library, including illuminated manuscripts, historical maps, vintage posters, rare prints and photographs, illustrated books, printed ephemera, and more."


Yahoo! Directory RSS Feeds

Yahoo! Directory RSS Feeds: "The Yahoo! Directory is now offering the following feeds in RSS format. The feeds are free of charge to individuals and nonprofit organizations for non-commercial use. "

March 03, 2005


Wired review wikipedia

Four years ago, a wealthy options trader named Jimmy Wales set out to build a massive online encyclopedia ambitious in purpose and unique in design. This encyclopedia would be freely available to anyone. And it would be created not by paid experts and editors, but by whoever wanted to contribute. With software called Wiki - which allows anybody with Web access to go to a site and edit, delete, or add to what's there - Wales and his volunteer crew would construct a repository of knowledge to rival the ancient library of Alexandria.

Wikipedia represents a belief in the supremacy of reason and the goodness of others.

Con­sidering how easy it is to make changes on Wiki­pedia, you'd imagine these ne'er-do-wells could potentially overwhelm the site. But they haven't - at least not yet - because defenses against them are built into the structure. Add Wiki software and some helping hands and you get something self-repairing and almost alive. A different production model creates a product that's fluid, fast, fixable, and free.

It turns out that Wikipedia has an innate capacity to heal itself. As a result, woefully outnumbered vandals often give up and leave. (To paraphrase Linus Torvalds, given enough eyeballs, all thugs are callow.) What's more, making changes is so simple that who prevails often comes down to who cares more. And hardcore Wikipedians care. A lot.


June 2001   July 2001   January 2003   May 2003   September 2003   October 2003   December 2003   January 2004   February 2004   March 2004   April 2004   May 2004   June 2004   July 2004   August 2004   September 2004   October 2004   November 2004   December 2004   January 2005   February 2005   March 2005   April 2005   May 2005   June 2005   July 2005   August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006   January 2007   February 2007   March 2007   April 2007   June 2007   July 2007   August 2007   September 2007   October 2007   November 2007   December 2007   January 2008   February 2008   March 2008   April 2008   July 2008   August 2008   September 2008   October 2008   November 2008   December 2008   January 2009   February 2009   March 2009   April 2009   May 2009   June 2009   July 2009   August 2009   September 2009   October 2009   November 2009   December 2009   January 2010   February 2010   March 2010   April 2010   May 2010   June 2010   July 2010   August 2010   September 2010   October 2010   November 2010   December 2010   January 2011   February 2011   March 2011   April 2011   May 2011   June 2011   July 2011   August 2011   September 2011   October 2011   November 2011   December 2011   January 2012   February 2012   March 2012   April 2012   May 2012   June 2012   July 2012   August 2012   October 2012   November 2012   December 2012   January 2013   February 2013   March 2013   April 2013   May 2013   June 2013   July 2013   September 2013   October 2013   January 2014   March 2014   April 2014   May 2014   July 2014   August 2014   September 2014   October 2014   November 2014   December 2014   January 2015   February 2015   March 2015   April 2015   May 2015   June 2015   July 2015   August 2015   September 2015   January 2016   February 2016   March 2016   April 2016   May 2016   June 2016   July 2016   August 2016   September 2016   October 2016   November 2016   December 2016   January 2017   February 2017   April 2017   May 2017   June 2017   July 2017   August 2017   September 2017   October 2017   November 2017   December 2017   February 2018   March 2018   April 2018   May 2018   June 2018   July 2018   August 2018   September 2018   October 2018   November 2018   December 2018   January 2019   February 2019   March 2019   April 2019   May 2019   July 2019   August 2019   September 2019   October 2019   November 2019   December 2019   January 2020   February 2020   March 2020   April 2020   May 2020   July 2020   August 2020   September 2020   October 2020   December 2020   January 2021   April 2021   May 2021   July 2021   September 2021   March 2022   October 2022   November 2022   March 2023   April 2023   July 2023   September 2023   October 2023   November 2023   April 2024   May 2024   June 2024  

This page is powered by Blogger. Isn't yours?