If you code much in VBA, you have probably had to manipulate worksheets at some point. There are some common ways to work with worksheets through VBA. You have the Range object, where you can write in a string of the address you need to access. And you have the Cells object, where you use the [Row,Column] notation to access cells (and you can [...]
Using Enum to Keep Track of Worksheet...
If you code much in VBA, you have probably had to manipulate worksheets at some point. There are some common ways to work with worksheets through VBA. You have the Range object, where you can write in a string of the address you need to access. And you have the Cells object, where you use the [Row,Column] notation to access cells (and you can [...]
Alternative to Multiple IF Statements
Have you ever had to create a multiple IF statement? Ever notice how crazy it looks with all those parentheses and it’s difficult to follow? Well, I was wondering if there was another way I could create a multiple IF statements that would be cleaner and easier to edit if need be.
And I have.
What I have discovered isn’t exactly as [...]
Arrange Windows for the Obsessive Com...
I’m sure you can find how to Arrange Windows in Excel all over the web; it’s been covered many times. However, if you’re anything like me, you would like to know how to control the actual arrangement is done by Excel. Maybe you’d like the window on the left to be the “before” worksheet and the worksheet on [...]
View multiple worksheets at the same ...
Sometimes you would like to view multiple worksheets at once. Maybe you have two worksheets to compare side-by-side or you are working on one worksheet where you need to reference another section at the same time. I’ve been in these situations before and it’s nice to know that Excel offers ways to show multiple worksheet / workboo [...]
Merge Cells In Depth
Excel’s Merge Cells feature offers a simple way to organize expand the space a single cell contains. If you have a summary page, for example, you may want to create a title for the display. Or on your data sheet, maybe you want to show that two columns belong to a specific group of data. Either way, Merge Cells in Excel can help you cre [...]
Array Formulas for Beginners
Excel Array formulas are one of the handiest tools to have under your Excel tool belt. You can expand regular Excel formulas to provide a lot more power to regular functions. Take the function AVERAGEIF() as an example. Before Excel 2007, you had to use array formulas to provide the same functionality. And while array formulas are extremely p [...]
Hyperlinks are lost after sorting ...
After I wrote my post about creating hyperlinks with external data, a subscriber told me about a problem they were having and was wondering if I could help. The issue was that when they sorted hyperlinks after they were recently copied from one range to another, some hyperlinks would be removed from the range while others remained intact. I [...]
Creating Hyperlinks With External Dat...
Pretty often at my job I’m asked to create an ad-hoc report in Excel that grabs some info from our website’s database and presents it with whatever metric my manager asks for. The data will have information that can be linked to a specific page on the website and more often than not I’m asked to link the info to the page so [...]
How to Put Quotation Marks in Excel F...
When you add text in a formula in Excel, you have to enclose the text with quotation marks (“…”). This is useful for when you want to create a dynamic string that changes based on some conditions. But what happens when you want to put quotation marks as part of the formula’s result? In this post, I’ll be talki [...]
How to use Index with Match to lookup...
A little while ago, I wrote a post about how to use Vlookup in Excel. While the Vlookup function can be very useful for grabbing related data based on a search, there are some limitations. For example, Vlookup wants you to have the first column of your table be the column that you’re searching against. Well, what if you don’t w [...]
