Dear Aamir,
The INDIRECT Function
The *INDIRECT* worksheet function is a useful tool for creating cell or
range references "on the fly", as a formula is evaluated, rather than "hard
coding" them into the formula. Microsoft's on-line Help documentation of
this function only hints at is usefulness.
In its simplest usage, the *INDIRECT* function allows you to put the
address of one cell in another, and get data from the the first cell by
referencing the second. For example, if cell A1 has the value "C3", then *
=INDIRECT(A1)* will return the value in C3.
The real power of the *INDIRECT* function is that it can turn any string
into a reference. This includes any string that you build up using string
constants and the values of other cells in the formula, strung together
with the & concatenation operator. For example, the simple formula
*=SUM(A5:A10)*
will sum the values in the range A5:A10. However, suppose you want to be
able to specify which range of rows to sum "on the fly", without having to
change the formula. The INDIRECT function allows you to do this. Suppose
you put your starting row cell B1, and your ending row in C1. Then, you
can use the formula
*=SUM(INDIRECT("A"&B1&":A"&C1))*
The argument to the INDIRECT function is
*"A"&B1&":A"&C1*
If B1 contains 5 and C1 contains 10, this evaluates to the string "A5:A10".
The INDIRECT function converts this string to an actual range reference,
which is passed to the SUM function.
For VBA formulas to return relative sheet names (e.g., the name of the next
or previous worksheet), see the Referencing Worksheets From
Formulas<sheetref.htm>page.
Another useful feature of the INDIRECT function is that since it takes
string argument, you can use it to work with cell references that you don't
want Excel to automatically change when you insert or delete rows.
Normally, Excel will change cell references when you insert or delete rows
or columns, even when you use absolute referencing <relative.htm>. If you
have the formula =SUM($A$1:$A$10), and then insert a row at row 5, Excel
will convert the formula to =SUM($A$1:$A$11). If you don't want this to
happen, use the INDIRECT function to change a text string to a reference:
*=SUM(INDIRECT("A1:A10"))*
Since Excel sees "A1:A10" as a text string rather than a range reference,
it will not change it when rows or columns are deleted or inserted.
This feature is important when working with some array formulas <array.htm>.
Frequently, an array formula will use the ROW() function to return an array
of numbers. For example, the following formula will return the average of
the 10 largest numbers in the range A1:A60 :
*=AVERAGE(LARGE(A1:A60,ROW(1:10)))*
However, if you insert a row between rows 1 and 10, Excel will change the
formula to
*=AVERAGE(LARGE(A1:A60,ROW(1:11)))*
which will return the average of the 11 largest numbers. If we use the
function with a string, Excel won't change the reference, so the formula
will remain correct, regardless of whether and where rows are inserted or
deleted.
*=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))*
You can use the *INDIRECT* function in conjunction with the
*ADDRESS*function. The
*ADDRESS* function uses row and column numbers to create a string address.
For example, the formula *=ADDRESS(5,6)* returns the string *$F$5*, since *$F$5
*is the 5th row of column 6. You can use then pass this to *INDIRECT *to
get the value in cell *F5*. For example, *=INDIRECT(ADDRESS(5,6))* . While
this example may seem trivial, it illustrates a technique that you can use
to build more complicated formulas.
--
Thanks & regards,
Noorain Ansari
*http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
*http://noorain-ansari.blogspot.com/* <http://noorain-ansari.blogspot.com/>
On Wed, Nov 30, 2011 at 11:47 PM, Aamir Shahzad <aamirshahza...@gmail.com>wrote:
> Dear Group,
>
> Please provide the "Indirect Function" samples how is it working & when we
> use? Sample files will appreciate.
>
> Regards,
>
> Aamir Shahzad
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
