Search Not Just Numbers

Tuesday, 3 June 2014

Excel Tip: Referring to cells based on the contents of other cells

Obviously, one of the most wonderful things about Excel is that you can refer to the contents of any cell by reference to its sheet, column and location and use those contents in a formula.

But what if you could store the row, column or sheet information in other cells that you could refer to to create the reference?

Well, you can, and, if you've lost me, I'll tell you why you might want to!

The Excel function we can use here is INDIRECT.

This function asks Excel to treat the text between the brackets as a reference. To illustrate what I mean, let us assume that cell A1 contains the number 5 and cell A2 contains the text A1.

If in cell A3, we type the formula:


cell A3 will show the text A1 (i.e. the contents of cell A2)

If, however, we type the following into cell A3:


cell A3 will now show the number 5.

This is because we have told Excel not to return the contents of cell A2, but to treat those contents as a reference. So because A2 contains the text A1, the function now returns the contents of cell A1, i.e. the number 5!

This is most useful if you wish to build up a reference using text. For a refresher on manipulating text you might want to visit my earlier post:

Excel Tip: Manipulating text in Microsoft Excel

For example, we might want a formula to return the cell A1 from a particular tab, specified in another cell. So, say, cell B1 contains a drop-down of sheet names, our formula would be:


This takes the sheet name from cell B1 and adds the text !A1 on the end and then uses the INDIRECT function to treat the result as a reference, so if cell B1 contained Sheet2, then the reference would be Sheet2!A1.

This needs to be slightly more complicated if the sheet name could have spaces in it. When a sheet name has spaces, Excel requires it to be enclosed in single quotes, so Sheet2 is OK, but if you want a space, it must be 'Sheet 2'. Excel will be happy with or without the single quotes when there is no space, so it is safer to include them. The formula would then be:


There are many uses for INDIRECT, but I find manipulating the sheet name in this way to be one of the most useful.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".


  1. I spent much of yesterday evening moving data from a database (limited output only available) to Excel and playing (cleaning) it on the way - parsing and removing leading spaces/spaces around commas etc. This is very timely as there is always more to learn and better ways to do things. Keep 'em coming!

  2. This type of precise gains you will get by using pounding in conjunction with replica chanel are only several this many gains they will produce. It is possible to examine safeguard process ability, you will easy use in predicament affecting miserable predicaments. Also, you may have this full satisfaction when you finally boxing techinque together with master. Peace together with self-motivation is usually formulated. You can good sense almost any sigh connected with elimination in addition to imagine that you will be showcased by using gucci outlet. Furthermore, it really helps to undoubtedly eradicate of which rage that is certainly just a man or women. As soon as them usually are yields, you will good sense easy fold into your human body together with ease likewise. Many coronary heart in addition to treatment professionals replica watches uk give techinques knowledge instructions firstly. Many men and women are coming into internet marketers a internet-based advertising and marketing often to help product the ‘‘real world" profits or maybe the item to be his or her key source of income. Exactly why? Mainly because internet promotion purely delivers these individuals many gains! Primary, you possibly can gain almost anyone on this planet that's Internet access when you showcase ones merchandise on the net. It indicates some sort of greater specific niche market available for you, which will choose much larger profits. Minute, putting together a profitable business on the net involves solely a tiny part of the purchase price desired to build a proper company place, which suggests many benefits with the corporation rolex replica uk. A different feature comprising fascinated many men and women on the way to internet promotion is usually the point that just one isn't going to will need to have her own things for starters. With affiliate marketing, fake watches can start doing big dollars through advertising, as well as simply by trying to sell, added folks' merchandise.

  3. "We are having a great time," he says replica chanel. "I think it's a rare event where you actually find yourself being moved by the scripts replica louis vuitton as they come in, and the story that you are telling. You go through phases with the rolex replica show, and I absolutely have a love affair with the show right now."