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!
Print This Post
|
|
|
|
|
 |
Related Posts -
An Anti-Marketing Shoe Store kikfoto How's this for a business plan to pitch to investors: a storefront of frosted glass which obscures the products for sale, an almost-invisible door, and no sign. That's right, NO SIGN! How did they get the funding? The owners pitched something different. Click here for the original article...... -
Excel How-to: A Macro to Hide Blank Rows on a Spreadsheet Today, a coworker asked me how to hide a row on his Excel spreadsheet. This was a fairly large spreadsheet, with about 150 rows and 30 columns. After showing him, he started to hide every other row, which would be an incredible waste of time for someone in his salary...... -
Three Excel How-Tos: Now With Linked Files I've gone back and revised three previous Excel how-to posts. I've added links to live Excel files, which will make it easier to follow along with my ramblings. Here are links to the new and improved posts: Excel How-to: The SUMIF Function Excel How-to: Creating a Pivot Table Excel How-to:......
Related Websites -
What Ever Happened To All The Good Vinyl Siding? Today's guest post is from none other than Greg La Vardera of LaMi Design. As I wrote about before, Greg has some magnificent architecture work over at his site that you should check out. On with the post... What ever happened to all the Good Vinyl Siding? Huh? What kind...... - 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......
- Editing Formulas After you’ve entered a formula, you can (of course) edit that formula. You may need to edit a formula if you make some changes to your worksheet and then have to adjust the formula to accommodate the changes. Or the formula may return an error value, in which case you......
