Yesterday we showed how named ranges can be used in a single worksheet. That can be good for making your formulas easy to follow. It also paves the way for doing real powerful work across multiple spreadsheets or workbooks.
This is covered in two files. The Names_Multiple_Exercise file and the Names_Multiple_Result file with the results of the exercises.
Here is one great way to use names. Set up worksheet assumptions on a separate sheet. Named ranges will make the calculation much easier.
Here is a shortcut tip on creating additional named ranges that are similar. In this example, we highlight the row for the name we just set up, then click on that name in the name box, then click the Ctrl + C keys to copy.
Then we highlight the area for our new range, put our cursor in the name box and click Ctrl + V to paste. We then edit the name to change it to q2 at the end.
Now we can switch to the Qtr1 sheet and change the cost of sales calculation. We can the cost of sales assumption name, even though it was on another sheet.
Suppose we had typed in the name wrong, here is the type of error you get using names.
The fix is simple. Fix the typo in the name in the formula (you could also paste in the name in the formula via the Insert menu as covered yesterday).
Now in our example, we have a qtr1 sheet. We can use names to help our setup of a qtr2 sheet. It may take a little longer with names to setup, but will payoff later as you will see. First we copy the Qtr1 sheet and rename the new sheet Qtr2. Then we go in and update the names, highlighting the named range and then changing the q1 to q2 at the end.
Now after we have set up the q2 names, we still notice that the q1 name appears in the name box on the Qtr2 sheet. No problem. That is because the q1 name is still on both sheets. All we need to do is go in and delete the q1 names in the Qtr2 sheet. The q1 names in the Qtr1 sheet will still be intact. We access this by going to the menus- Insert, then Name, then Define.
Notice that for the name consumerq1, it shows Qtr2 to the right. That is because we are in the Qtr2 worksheet and this name shows up in multiple worksheets. Meanwhile, notice that consumerq2 does not have any worksheet shown to the right. That is because this name is only contained in one worksheet. To fix the consumerq1 name so that it only shows up in the Qtr1 worksheet, click the Delete key. After we do that, the Qtr2 reference is deleted.
After deleting all the duplicate name references, we can quickly update the formulas in Qtr2 by using search and replace. First highlight the entire Qtr2 sheet, then change the q1 to q2.
Now for even more power, to reap more benefit from using names. Let's create a YTD sheet by copying the Qtr2 sheet.
Now we could create ytd formulas by using names for each row, such as salesq1+salesq2. But since the worksheet layout is exactly the same, there is a faster way.
First, create names for the entire worksheets, such as Qtr1 and Qtr2. (You need to highlight all the cells in the worksheet).
Then the formulas in the YTD sheet can be Qtr1+Qtr2.
That is a pretty straightforward consolidation of information. Here is another way to use it. Suppose someone wanted to see results by customer category. No problem. Here is what we would do. We set it up for the first group, Direct
Without even having to click back and forth between sheets, we can create this summary, because we have used names that we can easily remember.
Doing the rest of the customer groups is a snap. To set up Wholesale, we would copy the Direct worksheet, rename it, then do a search and replace to change direct to wholesale. Very quick. The investment in time to set up names pays off.
This can work as well for pulling in information across workbooks. It is a little more involved since you have to pick up the name of the workbook as well. One great beauty of using names across workbooks is that you can cut down on broken links. For example, suppose you have two workbooks linked and you only have one workbook open. If you decide to insert a row, you could really mess up formulas in the closed workbook. By using names, it might not matter. Without names, you would have to have all workbooks open. Using names can save you from serious damage.
That's all for this expanded lesson. Next we will cover how to maintain names.