It’s time for another edition of Stupid Excel Tricks!

One of my coworkers recently asked me an Excel question.  He had a spreadsheet with multiple tabs, and wanted to have the value in cell A1 on the respective worksheets to be the name of each tab.  I knew that it could be done, so I searched the deep recesses of my memory for the answer.  When it didn’t turn up there, I went to Plan B.  It’s a good thing that I save everything to my computer using my foolproof file naming system, otherwise this fool would have never found it.

This formula can save you the time of not having to type a heading on each tab.  I’m not sure exactly how this formula works, but I do know the basics.  First, rename your tab by right-clicking on the tab and then left-clicking on Rename (you can give it any name that you’d like; I called mine SALES).

Just right-click on the tab name, and left-click on Rename

Next, go to cell A1.  Type in this formula, then hit ENTER:

=MID(CELL(”filename”,A1),FIND(”]”,CELL(”filename”,A1))+1,256)

You should have something that looks like this:

Notice that while the name of the tab, SALES, appears in cell A1, the formula bar contains the formula for making this happen.

TA-DA! That’s all there is to it.

I should add that you’re not limited to having the tab name appear in cell A1.  Try dragging the contents of the cell anywhere on the spreadsheet, and it will show up there as well.  In this case, I’ve dragged it to cell D4:

Notice that the cell reference in the formula has changed from A1 to D4.

Now, try renaming the tab, and look at the result in cell D4:

The value in cell D4 changed from SALES to Revenue.  No action is needed for cell D4.

I hope that helps some of you out there.  I’d recommend bookmarking this page, just in case you forget how to set up the formula.  An even better idea would be to sign up for email updates by clicking here: Email Updates.  I’ll be posting more Excel tips in the future, so don’t miss them!

• Corporate Barbarian Links: Chainsaw Massacre Edition Photo by lancefisher No, that's not me in the picture.  But I did take the chainsaw out this week, and cleaned up the look of the website a bit.  I got rid of the big, ugly ad in the sidebar, and reduced the amount of categories.  Hopefully this will......
• Excel How-to: The SUMIF Function The SUMIF function is an easy way to get a subtotal of specific data that meets a certain condition, without going through the trouble of producing a Pivot Table.  It's most useful when working with long arrays of diverse data: Note: You can follow along with this how-to by opening......
• Unpaid Overtime: Not Just for Brown Nosers Anymore country_boy_shane I worked with a guy many years ago who lived for overtime.  Actually, I think he relied on it to live.  Even though he had a college degree, and worked in the finance department, he would work overtime in the kitting department (the stockroom for you non-defense contractor......
• Low Budget Circumstance has forced my hand, To be a cut price person in a low budget land - from Low Budget, by The Kinks, circa 1979 They say the more things change, the more they stay the same.  I remember when that song came out; we were embroiled in controversy with......
• Deming's Seven Deadly Diseases I've wrote about W. Edwards Deming and his 14 Points in a previous post.  You can read it here.  Today, I'd like to talk about his Seven Deadly Diseases.  These were the ailments that Deming saw as reason for the decline of Western Management: Lack of constancy of purpose to......

• Tips To Sell Affiliate Products Online Now-a-days, selling affiliate products in Internet is becoming more popular. The buying habit of Internet user gets increasing every year. The success of an affiliate program depends on how the products are promoted and how they are placed for sale ? We have to understand and implement the basic factors......
• Adwords Marketing Secrets and Yahoo Marketing - 10 Killer Ways To Multiply Your Sales. Helpful Things to Be Aware of Find out how to improve your sales with adwordsWhen you make your first sale, follow-up with the customer. You could follow-up with a "thank you" email and include an advertisement for other products you sell. You could follow-up every few months. Constant but not overbearing contact keeps you in the......
• Beauty Products - How To Save Money On Them Beauty products can be quite expensive. Have you seen the price of the specialty makeup or perfumes? Everyone wants to look their best all the time. You feel better about yourself and life in general when you look good. Unfortunately, things that are good for your appearance may not always......
• Green Hybrid Electric Cars and the Current Economy You might not think the current recession will have much effect on the production and sales of green hybrid electric cars, at least not any more than it affects sales of cars in general.  Car sales are at a low right now with manufacturers like GM closing plants for a......
• Stealth Startups, Get Over Yourselves: Nobody Cares About Your Secrets When Preetam Mukherjee started Marcellus.tv in March 2007, his company was one of the very few players in the professional online video hosting space. He believed he was building a killer product that would become a blockbuster and would compete handily with the one established player in the space,......