If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
What purpose is the INDIRECT function?
Thanks
Surely if I want cell value of A3 to appear in cell B4 I could enter "=A3" in cell B4? |
#2
|
|||
|
|||
What purpose is the INDIRECT function?
Hi,
This explains it in much more detail than could be gone into here. http://www.contextures.com/xlFunctions05.html Mike "Simon" wrote: Thanks Surely if I want cell value of A3 to appear in cell B4 I could enter "=A3" in cell B4? |
#3
|
|||
|
|||
What purpose is the INDIRECT function?
The INDIRECT function allows you to build up a cell or range reference
as a string and then to pass this into a function where it will be evaluated as if you had typed the reference directly. In the simple example you quoted you would not need to use INDIRECT, but suppose you wanted the value from a range of different cells to appear in B4, depending on the value (1 to 5) in B3. You could do that like this: =INDIRECT("A"&B3) So, if B3 contained 1, then the contents of A1 would appear in B4, but if B3 contained 4, then the contents of A4 would appear in B4. The range reference can also encompass sheet names, so this formula in B4: =INDIRECT("'"&B2&"'!A"&B3) where B2 contains a sheet name like "Sheet2" and B3 contains a number eg 3, would return the value from the cell A3 of Sheet2. INDIRECT does not work with closed workbooks, and it is a volatile function. Hope this helps. Pete On Aug 1, 11:16*am, Simon wrote: Thanks Surely if I want cell value of A3 to appear in cell B4 I could enter "=A3" in cell B4? |
#4
|
|||
|
|||
What purpose is the INDIRECT function?
The INDIRECT function allows you to build up a cell or range reference as a
string and then to pass this into a function where it will be evaluated as if you had typed the reference directly. In the simple example you quoted you would not need to use INDIRECT, but suppose you wanted the value from a range of different cells to appear in B4, depending on the value (1 to 5) in B3. You could do that like this: =INDIRECT("A"&B3) So, if B3 contained 1, then the contents of A1 would appear in B4, but if B3 contained 4, then the contents of A4 would appear in B4. The range reference can also encompass sheet names, so this formula in B4: =INDIRECT("'"&B2&"'!A"&B3) where B2 contains a sheet name like "Sheet2" and B3 contains a number e.g. 3, would return the value from the cell A3 of Sheet2. INDIRECT does not work with closed workbooks, and it is a volatile function. Hope this helps. Pete "Simon" wrote in message ... Thanks Surely if I want cell value of A3 to appear in cell B4 I could enter "=A3" in cell B4? |
#5
|
|||
|
|||
What purpose is the INDIRECT function?
Sorry about the double post - Google Groups is acting up again and
didn't seem to accept the post, so I copied it to OE and posted through there, only to find that it had eventually gone through first time. Pete On Aug 1, 11:41*am, "Pete_UK" wrote: The INDIRECT function allows you to build up a cell or range reference as a string and then to pass this into a function where it will be evaluated as if you had typed the reference directly. In the simple example you quoted you would not need to use INDIRECT, but suppose you wanted the value from a range of different cells to appear in B4, depending on the value (1 to 5) in B3. You could do that like this: =INDIRECT("A"&B3) So, if B3 contained 1, then the contents of A1 would appear in B4, but if B3 contained 4, then the contents of A4 would appear in B4. The range reference can also encompass sheet names, so this formula in B4: =INDIRECT("'"&B2&"'!A"&B3) where B2 contains a sheet name like "Sheet2" and B3 contains a number e.g.. 3, would return the value from the cell A3 of Sheet2. INDIRECT does not work with closed workbooks, and it is a volatile function. Hope this helps. Pete "Simon" wrote in message ... Thanks Surely if I want cell value of A3 to appear in cell B4 I could enter "=A3" in cell B4?- Hide quoted text - - Show quoted text - |
#6
|
|||
|
|||
What purpose is the INDIRECT function?
Another thing I like about INDIRECT is that it doesn't change the cell
reference. So if you have, for example, a data tab that you are constantly adding or deleting data from, the =INDIRECT("A3") will always refer to A3 and not get bumped up (to the point of a REF error, or down. -- John C "Pete_UK" wrote: The INDIRECT function allows you to build up a cell or range reference as a string and then to pass this into a function where it will be evaluated as if you had typed the reference directly. In the simple example you quoted you would not need to use INDIRECT, but suppose you wanted the value from a range of different cells to appear in B4, depending on the value (1 to 5) in B3. You could do that like this: =INDIRECT("A"&B3) So, if B3 contained 1, then the contents of A1 would appear in B4, but if B3 contained 4, then the contents of A4 would appear in B4. The range reference can also encompass sheet names, so this formula in B4: =INDIRECT("'"&B2&"'!A"&B3) where B2 contains a sheet name like "Sheet2" and B3 contains a number eg 3, would return the value from the cell A3 of Sheet2. INDIRECT does not work with closed workbooks, and it is a volatile function. Hope this helps. Pete On Aug 1, 11:16 am, Simon wrote: Thanks Surely if I want cell value of A3 to appear in cell B4 I could enter "=A3" in cell B4? |
#7
|
|||
|
|||
What purpose is the INDIRECT function?
On Aug 1, 2:02*pm, John C johnc@stateofdenial wrote:
Another thing I like about INDIRECT is that it doesn't change the cell reference. So if you have, for example, a data tab that you are constantly adding or deleting data from, the =INDIRECT("A3") will always refer to A3 and not get bumped up (to the point of a REF error, or down. -- John C "Pete_UK" wrote: The INDIRECT function allows you to build up a cell or range reference as a string and then to pass this into a function where it will be evaluated as if you had typed the reference directly. In the simple example you quoted you would not need to use INDIRECT, but suppose you wanted the value from a range of different cells to appear in B4, depending on the value (1 to 5) in B3. You could do that like this: =INDIRECT("A"&B3) So, if B3 contained 1, then the contents of A1 would appear in B4, but if B3 contained 4, then the contents of A4 would appear in B4. The range reference can also encompass sheet names, so this formula in B4: =INDIRECT("'"&B2&"'!A"&B3) where B2 contains a sheet name like "Sheet2" and B3 contains a number eg 3, would return the value from the cell A3 of Sheet2. INDIRECT does not work with closed workbooks, and it is a volatile function. Hope this helps. Pete On Aug 1, 11:16 am, Simon wrote: Thanks Surely if I want cell value of A3 to appear in cell B4 I could enter "=A3" in cell B4?- Hide quoted text - - Show quoted text - HI John yes I know what you been. It locks it in which is useful if you have an "Add line" macro say to get the latest 10 results. |
#8
|
|||
|
|||
What purpose is the INDIRECT function?
On Aug 1, 11:57*am, Pete_UK wrote:
Sorry about the double post - Google Groups is acting up again and didn't seem to accept the post, so I copied it to OE and posted through there, only to find that it had eventually gone through first time. Pete On Aug 1, 11:41*am, "Pete_UK" wrote: The INDIRECT function allows you to build up a cell or range reference as a string and then to pass this into a function where it will be evaluated as if you had typed the reference directly. In the simple example you quoted you would not need to use INDIRECT, but suppose you wanted the value from a range of different cells to appear in B4, depending on the value (1 to 5) in B3. You could do that like this: =INDIRECT("A"&B3) So, if B3 contained 1, then the contents of A1 would appear in B4, but if B3 contained 4, then the contents of A4 would appear in B4. The range reference can also encompass sheet names, so this formula in B4: =INDIRECT("'"&B2&"'!A"&B3) where B2 contains a sheet name like "Sheet2" and B3 contains a number e..g. 3, would return the value from the cell A3 of Sheet2. INDIRECT does not work with closed workbooks, and it is a volatile function. Hope this helps. Pete "Simon" wrote in message .... Thanks Surely if I want cell value of A3 to appear in cell B4 I could enter "=A3" in cell B4?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Hi Pete (also from UK!) Appreciate your reply I too have summarised myself in a similar range. Within VB I use Ranges As Range And with Paths to help define things. "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 It means you can vary the rows you wish to count in cells B1 and C1. |
#9
|
|||
|
|||
What purpose is the INDIRECT function?
Thanks for the feedback, Simon.
Presumably, then, you asked the question rhetorically to promote debate, as you seem to know what it can be used for !! bg Pete On Aug 1, 2:49*pm, Simon wrote: Hi Pete (also from UK!) Appreciate your reply I too have summarised myself in a similar range. *Within VB I use Ranges As Range And with Paths to help define things. "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 It means you can vary the rows you wish to count in cells B1 and C1 |
Thread Tools | |
Display Modes | |
|
|