Solved

Simple Explanation for the syntax used by an INDIRECT Function in Excel to get worksheet references

Posted on 2012-03-15
22
339 Views
Last Modified: 2012-03-17
I have a series of columns in a Summary worksheet that reference data in a series of worksheets. I am comparing the data in each worksheet.
Each column is headed by a header cell that contains the name of the worksheet.
e.g. DR00  DR01, DR01, DR02, etc..
Several rows down I have a cell that contains a worksheet reference that pulls the data from a cell in that specified worksheet.
I want to create a Cell Formula that uses the INDIRECT function to pull the worksheet reference from the header cell.
I don't know what syntax is correct here. Excel keeps telling me my formula is incorrect.
Can someone look at my text workbook and give me the correct formula. I'd also appreciate a little explanation as to why my syntax is not working. Thanks.
test.xlsx
0
Comment
Question by:Scruples89
  • 9
  • 7
  • 3
  • +2
22 Comments
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 50 total points
ID: 37727153
See this link: Indirect
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 125 total points
ID: 37727176
What indirect is looking for is a "String" like

DR01!B2

which can be returned with the formula

C1&"!B2"

So the full formula becomes

=INDIRECT(C1&"!B2")
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 200 total points
ID: 37727221
To add to ssaqibh's explanation, you can then use the INDIRECT formula in wonderful ways:

e.g.,

[C1]="'Sheet1!'A1:B10'"

=VLOOKUP("Dave",INDIRECT(C1),2,0)

To find "Dave" in the first column of Sheet1!A1:B10 table, and return the matching data in the second column.

INDIRECT can be used just about anywhere to represent a range reference that you would have otherwise just directly typed in.  The benefit is you can change what that reference is in the cells that INDIRECT is using, thus tab names, ranges, workbook names, etc., can change depending on what's in those cells.

So, you can also use it with dynamic charts, data validation ranges, etc.

Note INDIRECT does not work on closed workbooks.  (See/Google MOREFUNC Utility for INDIRECT.EXT for that capability, in an add-in)

Cheers,

Dave
0
 
LVL 80

Accepted Solution

by:
byundt earned 125 total points
ID: 37727273
If you want to copy the formula down and across to grab additional data, then you might consider using the CELL function to return a cell address:
=INDIRECT(B$1&"!" & CELL("address",B2))            this formula intended for cell B3

By omitting the $ from B$1, you can copy across to get different worksheets
By using B2 in the CELL function, you return a reference to cell  B2 on the worksheet specified by cell B1.
0
 

Author Comment

by:Scruples89
ID: 37727559
This is all very helpful. Looks like this function can be very useful.

So if  I understand correctly this function prevents excel from seeing directly into the inner workings of the cell it references. Excel only sees a "string". That way excel can't change the string based upon some other criteria. This is important because sometimes you want things to stay the same in the cell when you are adding or deleting rows or columns.

In my example, INDIRECT returns a sting for items NOT in quotes and for items in quotes it returns the normal or expected result. That is why you can drag the formula across cells and the items in the quotes will change according to where the cell is but th eitem not in the quotes will not change.

Correct?
0
 
LVL 80

Expert Comment

by:byundt
ID: 37727584
INDIRECT requires text that looks like a cell reference as its input. The text required by INDIRECT can be the result of another formula, however. That's why you can drag the formula across and down to get such interesting final results.

If you take the part inside the parentheses of INDIRECT and put an = equals sign in front of it (to make it a formula), you should get a perfectly formed cell reference. And depending on your cleverness with $ dollar signs, you can make parts of that cell reference be fixed while other parts change as you copy down and across.

Note that inside double quotes are constant as you copy down and across. The parts outside the double quotes may, or may not change (depending on those dollar signs).

Brad
0
 

Author Comment

by:Scruples89
ID: 37729611
Okay, so I got that backwards. Does anyone have some links to Youtube tutorial videos they could point me to that walk me through the various options for this function?
0
 

Author Comment

by:Scruples89
ID: 37729833
So =INDIRECT(C1&"!B2") works in my test workbook. I can drag it to the right and it works fine, but I cannot drag it down. I get a reference error.  If I anchor the first row with a $1 the reference error goes away but I just get the same result in each row.

What if I want to pull the data from a series of rows by dragging down.  Do I need to use another indirect function to call the row? Please see updated spreadsheet test
test.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37729979
Use this in B3

=INDIRECT(B$1&"!B"&ROW()-1)
0
 
LVL 80

Expert Comment

by:byundt
ID: 37730049
As an alternative to ssaqibh' more compact formula, I had previously suggested:
=INDIRECT(B$1&"!" & CELL("address",B2))

If you put that formula in cell B3, you may copy it down and across.
=INDIRECT(B$1        the $ prevents the row number from updating. You never lose your worksheet reference that way.

CELL("address",B2)    gives you the address of cell B2. Very easy to understand. As you copy the formula down and across, the B2 part will update automatically to refer to different rows and columns.

In ssaqibh's formula, the "!B" & ROW() -1 part also gives you an updateable reference to cell B2--but you can't copy it across and it is harder to understand the logic (in my opinion), hence my suggestion to use CELL function.

Note that MartinLiss gave you a link to a tutorial on the INDIRECT function. Do take a look at it.

Brad
0
 

Author Comment

by:Scruples89
ID: 37730064
Great! Okay, now I need some explanation please so I can better understand what is going on here.

=INDIRECT(B$1&"!B"&ROW()-1) is the formula that works. I can drag it up or down and sideways and it will return the corresponding data from those worksheet cells.

=INDIRECT
This is the function that enables this to happen. In laymen terms what exactly is INDIRECT doing?

B$1
This is the location of the string that points to the worksheet (in my example it is DR00, DR01)

&
And! This is a conjunction that puts two things together. Why is it necessary syntax?

"!B"
What is this telling me?

&

ROW()-1)
Again, how does this work in this function. It refers to a ROW but what does the () and the -1 do?

Sorry to be so dull. but if I don't understand what is going on I am more likely to run into further problems down the road. Appreciate your consideration for an explanation or links to an explanation. Thanks again!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 80

Expert Comment

by:byundt
ID: 37730130
If you always want to be pulling data from column B, then try:
=INDIRECT(B$1&"!" & CELL("address",$B2))

=INDIRECT
INDIRECT treats the stuff between the parentheses as a cell address, possibly including worksheet name and workbook name. This information must be provided as text.

B$1
This is the location of the string that points to the worksheet (in my example it is DR00, DR01). Correct understanding.

&
And! This is a conjunction that puts two things together. Why is it necessary syntax? You need to build the complete text of the cell address. The worksheet name is coming from one place (the contents of cell B1) and the cell address from another (the part that follows). You need to concatenate these two pieces. Although there is a CONCATENATE function for that purpose, it is much easier to type & to tell Excel to tie the two pieces into one.

"!B"
What is this telling me? The name of the worksheet must be followed by an exclamation mark when you get data from another worksheet. The B is a fixed reference to column B on that target worksheet.

&

ROW()-1)
Again, how does this work in this function. It refers to a ROW but what does the () and the -1 do? When there is no cell address passed to ROW function, it returns the row of the cell containing the formula. As you copy the formula down, ROW() will give you the numbers 4, 5, 6, etc. Since you wanted a reference to cell B2 you have to subtract 1 from the value returned by ROW(). If the formula is in cell B3, then ROW()-1 will return 2
0
 

Author Comment

by:Scruples89
ID: 37731149
Thanks! Those are good explanations. I am learning lots!

I don't understand how

=INDIRECT(B$1&"!" & CELL("address",$B2))

equates to

=INDIRECT(B$1&"!B"&ROW()-1).

I can see that both formulas work in the test spreadsheet, but  I don't understand what is going on in the CELL("address",$B2)

In the actual spreadsheet I am using the starting cell address is DR00!$K38
I want to get data from the same cell in DR01 and DR02 etc.

Then I want to be able to drag the formula down so it gives me data from $K39 in each of those worksheets.

How do I set the Function in CELL to start at $K$38?  Is it CELL("address',$K38)?
0
 

Author Comment

by:Scruples89
ID: 37731166
I just tested it and it seems to be working well in my actual spreadsheet. So the Cell Function takes what is the "address" that is the cell reference $K39 and returns it as a part of the INDIRECT Function. Correct?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37731196
Looks like you're gaining a good familiarization with the INDIRECT function.  Its just a formula based function like every other formula you might design, except that the formula inside indirect ultimately resolves to a string which represents a range address, and the contents of that range address is returned by the indirect function (which could be a value, an array, or otherwise).

CELL("Address",$B2) literally is $B2 in string form (e.g., it returns "$B2"), but because its written this way, it can be dragged down to change its reference as you copy formulas down.

CELL("Address",$K39) would return the string representation of "$K39" and can also be dragged up/down.

=INDIRECT(B$1&"!B"&ROW()-1) would translate to:

if you were on row 9, e.g.,

[C9]=INDIRECT(B$1&"!B"&ROW()-1) would translate to:

=INDIRECT(B$1&"!B8")

and on c10, if you dragged it down would be

=INDIRECT(B$1&"!B9)


Cheers,

Dave
0
 
LVL 80

Expert Comment

by:byundt
ID: 37731248
One small correction to Dave's statements is that =CELL("Address",$B2) will return $B$2. It always returns an absolute address ($ sign in front of both column letter and row number). If you copy the formula down, it will return $B$2, $B$3, $B$4 and so on. If you copy it across, it will return $B$2, $B$2, $B$2, etc.

When you copy the formula down, the $B2 inside the CELL function will change to $B3, $B4, $B5, etc. But when you copy it across, the $B2 inside the CELL function never updates. That's because the $ prevents the column letter from updating. It is because the reference to cell $B2 inside the CELL function is updating that the value returned by CELL is changing.

Please try the experiment of copying the following formula both down and across. You'll see what I mean.
=CELL("address",$B2)
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37731252
Agreed
0
 
LVL 80

Expert Comment

by:byundt
ID: 37731279
Another experiment that you really ought to try is to turn the inside of the INDIRECT function into a formula and copy it both down and across:
=B$1 & "!B" & ROW()-1
=B$1 & "!" & CELL("address",$B2)

Put both formulas in row 3 when you try the experiment. It's OK to put the second formula a number of columns to the right. Except for the extra $ signs, you should find that they both return the same answers. Given the names of your worksheets, the results should be:
DR01!B2, DR01!B3, DR01!B4 when first formula copied down
DR01!B2, DR02!B2, DR03!B2 when copied across

DR01!$B$2, DR01!$B$3, DR01!$B$4 when second formula copied down
DR01!$B$2, DR02!$B$2, DR03!$B$2 when copied across
0
 

Author Comment

by:Scruples89
ID: 37733348
Wow1 This has all be been very helpful! I have learned a lot and I can really see the potential of this function to make life easier linking data in various worksheets. Thanks everyone1 I wish I could award you all with 500 points each!
0
 

Author Closing Comment

by:Scruples89
ID: 37733359
As I said 500 points each would have been better.
0
 
LVL 80

Expert Comment

by:byundt
ID: 37733390
Scruples89,
One final note with INDIRECT function is that it is volatile. That means that the function recalculates every time anything changes in any open workbook. Non-volatile formulas recalculate only when one of their inputs change.

If you use a few dozen INDIRECT functions in formulas, you will never see a problem caused by its volatile nature. But if you have thousands of them, you may notice an annoying delay as you enter data. This delay is caused by the need of the workbook to recalculate all of those volatile formulas.

So like the Greek philosopher recommended, "moderation in all things."

Brad
0
 

Author Comment

by:Scruples89
ID: 37733588
Okay, that is a good note about the volatile nature of the INDIRECT formula. I was going to use it a lot but I will moderate that idea!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now