Building a reference to specific Worksheet based on Cell value using Formula

Most of the time businesses are managed using specialized software but when it comes to a final touch in reporting the data is exported to Excel where calculations and everything is done much more easily.

However, exporting to excel or importing in excel sometimes create very bizarre situations. For example, imagine a situation that you are maintaining records of your clients in a software to keep a track of their tours and flights. You thought of summarizing the details using Excel so exported the data in the form of excel file.

The intention is to create an invoice template in Excel in a way that if customer’s name is changed the data changes automatically.

To best understand this tutorial, download this example workbook to apply the concepts and techniques learned in this article

Not so simple things

My initial impression was that customer’s name will be in one table on one worksheet. One cell say ‘E1’ will have customer’s name which will act as a lookup_value in my VLOOKUP formulas that I will put in each invoice field targeted at that same table. For example if cell E1 is “ABC” VLOOKUP will find this name in the table and will fetch the respective value.

But situation was way off this direction when I opened the exported file in Excel and each customer had a separate worksheet after his own name. WHAT!!! Really??? What was the software thinking while exporting way.

The problem is multiple worksheet. As each customer’s data is on separate worksheet, I cannot provide a single unified lookup_value as for each customer I have to jump to its own worksheet.

But there is little hope. Cell E1 will have a customer’s name in it, let’s say it is “Zahid”. Zahid is a customer having worksheet with this exact same name. Now if somehow we can build reference to worksheet based on the value in cell E1 we will be able to fetch the value from that worksheet easily.

If you can’t go direct go INDIRECT!

INDIRECT function converts a text string into address or a reference somewhere in Excel.

=INDIRECT(ref_text, [a1])

ref_text: the text that you want to be converted into reference

[a1]: this argument is optional and it is either TRUE or FALSE. TRUE means referencing is in normal A1 style. If false, then referencing is in R1C1 style. If left empty, by default value is TRUE.

How this works?

Suppose in cell A1 you have written B3. Now as this combination of alphabets and numbers excel will take them as text and it will be left aligned by default (just for your general knowledge). In cell B3 you have number 86.

Now in cell A3 write this formula: =INDIRECT(A1). Press enter. You will get 86!

So what has happened here. Basically INDIRECT function treated a text string in cell A1 as cell reference i.e. B3 and in B3 you have 86. That is why you get 86. You will get the same result if you put =B3 in cell A3. In other words, in our example:

=INDIRECT(A1) is equal to =B3

Understanding Worksheet reference

In above example things are simple. We just put B3 in one cell and used INDIRECT function to convert that text to real cell reference. However, when you reference to worksheet it is a little different. For example if you want to make a reference to cell B2 of worksheet named ABC then its address will be:

=ABC!B2

So we have “worksheet’s name” (ABC), exclamation mark (!) and then the cell reference (B2).

In our example instead of ABC we have customer’s name and we want this to be dictated by a certain cell. Read on to know how this is done.

The key point to remember in using INDIRECT is that caters text only strings. So whatever we are going to feed this function first it has to be converted to text otherwise it won’t work. We will workout on how to get everything in text form before it is fed to INDIRECT function.

Prepping other things

Listing customers using simple Data validation

To list down all the customer in a drop-down fashion so that user can pick the name of customer easily and error free its good to use data validation lists. The particulars of all the customers are available in a table on worksheet named “customers”. To create data validation list using this table follow these steps:

Open up the example excel workbook you downloaded. You can download the sample workbook that goes with this tutorial here.

Step 1: Make “Invoice” worksheet active and select cell C4.

Step 2: Go to Data tab > Data tools group > click Data validation button.

Step 3: In a new dialogue box, settings tab is active by default, From the drop down select list

Step 4: Click the collapse button and click Customers worksheet tab and select the column that contains name. Click OK

Now you have the drop down list containing names of customers. You can check it by clicking the drop down arrow and making the selection.

Following animation helps you how to perform all the above steps:

To learn more about data validation then check out this tutorial and for full list of articles on data validation visit this page.

Fetching customer’s particulars using VLOOKUP

We need to populate basic invoice information like address and telephone and these particulars must change if different customer is selected. The easiest way is to use VLOOKUP for this purpose. To accomplish this workout following steps:

Step 1: Select the area under “Address”. This is a merged field with three columns and two rows merged together.

Tip: Remember, in case of merged columns and rows, the address of such field is of top left cell of the merged field. For example in our case it is B6.

Step 2: Press F2 to enter edit mode and put this formula: =VLOOKUP(C4,Customers!A2:B6,2). Press Enter and you will get the address.

C4: is the lookup value

Customers!: is the worksheet where our lookup range is

A2:B6: is the range we are using to do the lookup

2: is the number of column in the range from where we want to fetch the corresponding value.

Step 3: Now you have to repeat Step 2 again in cell C8 but in formula instead of mentioning 2, you will put 3 because telephone numbers of customers are in column 3. Therefore, the formula you have to put in cell C8 to fetch telephone numbers depending on the customer in cell C4 is:

=VLOOKUP(C4,Customers!A2:B6,3)

Following animation shows how to insert this formula:

To learn how VLOOKUP works read this tutorial. To read other example uses of VLOOKUP access this page.

Bonus Tip: Writing better formulas in Step 2 and 3

In order to mention lookup range we really have to put weird stuff there and writing lengthy formulas not only takes time but are also error prone. And with worksheet’s name included there are so many characters dancing in the formula bar. So here is a little trick to make it look neat.

Customers and their particulars are actually maintained in the form of a table. And if you have read 20+ Table tricks then you probably know that tables come with their own names which you can use in formula. To know the name of table go to customer’s worksheet and click anywhere inside table. Go to design tab and you will find the name of table in left most group named properties. In my case its name is Table2.

Change the name to ‘customers’ and press Enter key.

Now with table named as what it actually contains its much easier to know about it if this name is mentioned anywhere in this excel workbook.

Now lets go back to the formula in cell Step 2 which is: =VLOOKUP(C4,Customers!A2:B6,2)

The argument Customers!A2:B6 basically represent the range A2:B6 which is in fact whole table but as every other worksheet can have range A2:B6 therefore we have to tell excel about what worksheet we are talking which is ‘Customers’.

Now instead of this whole argument i.e. Customers!A2:B6 you can use the name of table itself which is ‘customers’ and your formula will become:

=VLOOKUP(C4,customers,2)

This looks not only neat but also easy to remember and decode in your brain especially if you are working late night with sleep debt 😀

In case of step 3 formula will become

=VLOOKUP(C4,customers,3)

Following animation shows how all this is performed and how the formula to fetch telephones is altered to look better:

To learn more benefits of excel’s awesome user friendly feature TABLE read this article as it covers each of its benefit with examples

Lets get back to our original work at hand. So far we are able to fetch particulars of customer mentioned in specific cell. Now we have to pull the last major task which is to fetch the sales totals from respective customer’s worksheet.

Referencing worksheet using value mentioned in specific cell

Lets do a little observation of data itself on each worksheet of customers. Within each worksheet we have sales made in months January through June with respective amounts. But  we want total sales for each customer.

Inserting formulas in multiple worksheets in one go!

One of the ways to total sales is to put sum formula in each worksheet one by one. In our example we have only few customers but imagine if you have many customers or many worksheets then it will be quite hectic.

The easy way is to edit multiple worksheets at once! In our example we are lucky that the structure of data on each customers’ worksheet is of same height and width. Therefore we can play with all of them in one go.

If I want to calculate total sales for each customer, I have to insert formula in cell B8 on each worksheet. And as formula and address is same we can have this done easily. Follow these steps:

Step 1: Make ABC worksheet active. Hold down Shift key and click the last customer’s worksheet MNO. You will notice that all worksheets between ABC and MNO will gets selected as tabs change their colors.

Step 2: Go to cell B8 and press Alt+=. This is a shortcut to put sum formula. Excel will automatically detect the above range of values to sum. Press Enter.

Now go to other customer’s worksheet and you will find the sum there as well. Excellent!!!

Following animation shows this step by step:

Atlast! going directly to INDIRECT!

Go Invoice worksheet. Highlight cell G11 and put this formula in it:

=INDIRECT("'"&C4&"'!B8")

Lets decode this formula.

Ampersand (&) are used to link two text strings. As we want to take the value in cell C4 to be considered to get to respective worksheet therefore we have mentioned C4 there. But C4 is a reference and not text. How that is converted to text? Read on.

Remember, INDIRECT function require text string in it to work and it converts text to references. The easiest way to make anything text in Excel is to use ( ‘ ) apostrophe before the value and even if it is number, date, or whatever, excel will take it as text.

Now in Excel to mention text inside function you have to put in double quotes. That is why you see apostrophe taken hostage by double quotes :D. If you don’t enclose apostrophe in quotes to tell excel its “text”  it will consider it a special character and will render error. Similarly before exclamation mark you see apostrophe the reason is before this character is taken by function for processing we want Excel to treat it as part of text.

So what happens with all this inside function. Excel will start unfolding each layer of double quotes inside formula starting with the inner most cell reference which is C4. Now in cell C4 customer’s name is mentioned. Excel will put that name in place of C4 (e.g. ABC) and open other double quotes. This will join up exclamation mark and B8 together. Now the text string will be ABC!B8. And then excel will execute INDIRECT function convert text string into a reference which is Cell B8 on ABC customer’s worksheet. Got it? If not read again 😉

Once everything is text. Time to press Enter key. BANANAA!!!! So we got the right minion I mean the right value in the cell.

Go on change the value in cell C4 i.e. select another customer and it will update accordingly.

Following is the animated steps to write INDIRECT function:

So once again! The day is saved by Excel using INDIRECT function which in the beginning looked so difficult.

So how do you use INDIRECT function? Let me know about your creative ideas and uses of this awesome function in the comments below. Would love to learn more about INDIRECT.