Intersection between two range names:

Also at William’s request, I will show you how to display different range names at the intersection of the contents of columns and rows.

What we do in this tip is, we display the intersection between the column headings and row titles, in a cell.
In other words, you select the validation list in cell C1 for a column of vegetables.
In cell C2, you use a validation list for months.
Excel gives us the result of this intersection in cell F1.

We start by entering a range name.
To do this, select the entire table and use the shortcut, Ctrl+’*’.
Asterisk is the alternate character on your numerical keypad.

This dialog box opens where the “Top Row” and “Left Column” options are selected.
Click the OK button.

This gives all our columns and rows, a range name.
I don’t think it can be any quicker.

Then, we need to add a validation list to cell C1, so we do not type fruits but only vegetables.
Similarly, we need a validation list for months. We do not have to type this.
We select the “Data” tab in the “Ribbon”, click the “Data Validation” button and select “Data Validation” from the pop-up menu.
We begin with the vegetables.
Select “List” in the “Allow” text box and select the range for the “Source” field.
For the vegetables, the range is A4 till A10.
Click the OK button.

The same thing goes for months.
First, select the cell where you want the list, which is cell C2, in this case.
Click the “Data Validation” button in the ribbon, again, and select “Data Validation” from the popup menu.
Select “List” in the “Allow” text box and select the range for the “Source” field.
In this case, all these are months. Thus we have B3 to G3.
Click the OK button.

The validation lists are now added to our cells C1 and C2.

Then select the cell where your data from the intersection of table rows and columns is to be placed.
In this case, it is the cell F1.
Type the formula = INDIRECT (C1) INDIRECT (C2).
The INDIRECT function gives us the text value. Whether this is from a range, or from a list does not matter.

IMPORTANT!
Both INDIRECT-functions are separated by a space.

Sales for Sprouts in the month of April is 28,500.

Changing your data in a validation list, automatically changes the turnover in cell F1.
If you want the formatting of the data in cell F1 to be modified, you can do it in precisely the same way as when you change the format of a “normal” cell.

FYI: Swotster.com is thinking of you, so you also please remember swotster.com

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats