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 -
The Economics of Lawn Care pfala I'm not an envious person. When someone experiences some good fortune, I'm genuinely happy for them. I compliment people if they have nice stuff, or if their houses look nice. But I think that my neighbor, The Lawn Freak, has finally gone overboard. What the heck is THAT...... -
Excel How-to: The GETPIVOTDATA Function In a previous post, we created a simple pivot table from a list of Excel data. Today, we'll link the pivot table data to a spreadsheet using the GETPIVOTDATA function. Note: You can follow along with the how-to by clicking on the following Excel file link: The GETPIVOTDATA Function.xls (In...... -
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......
Related Websites -
The Number ONE Thing Every Online Business Needs Can you guess what every Internet business needs more the most? Although I am sure we would get a variety of different answers many people probably could get this right. For example some people probably think the product is the most crucial thing. I would certainly agree that without a...... -
Cecilio CVN-200 4/4 Full Size Rosewood Violin w/ Case and Accessories User Reviews Send this to a friend Cecilio CVN-200 4/4 Full Size Rosewood Violin w/ Case and Accessories Manufacturer: Cecilio Instruments Customer Rating: List Price: $299.95 Sale Price: $90.00 Availibility: Usually ships in 24 hours Free Shipping Available Buy Now Product Description This violin is perfect for the student...... -
Fallene Cotz SPF 58 Water Resistant UVB/UVA Sunscreen for Sensitive Skin, 2.5-Ounce Tube User Reviews Send this to a friend Fallene Cotz SPF 58 Water Resistant UVB/UVA Sunscreen for Sensitive Skin, 2.5-Ounce Tube Manufacturer: Fallene Customer Rating: List Price: $26.00 Sale Price: $16.50 Availibility: Usually ships in 24 hours Free Shipping Available Buy Now Product Description Fallene Cotz SPF 58 is a......
