Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT, relative name as argument
The name (string) of the relative name (e.g "TestName") is written in
Range "A1". TestName itself is a named formula like "= SheetRef!A$3". Purpose is to get always the value in row three of the correspondent column. When I enter "=TestName" in Range "F26" i will get the value of Range "F3". So, and now I'd like to use a formula, where I use the relative name, which is entered in Range "A1", something like =INDIRECT($A$1), but this doesn't work. The idea is to use the values in column "A" for the relative names in the INDIRECT function. Any approach for a solution? Regards Werner |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT, relative name as argument
Hello Werner,
What exactly are you doing? If you enter into A1 testname Into C1 3812 Into E1 =INDIRECT(A1) Then you will get as a result in E1 3812 if testname has been defined as a name with value =Sheet1!C1 Regards, Bernd |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT, relative name as argument
Hi,
I don't understand the problem because what you have described works. Tou have a named range "TestName" that holds a formula =Sheet3!A1 You write the name of that range in another cell (A1) the formula =indirect(A1) evaluates the formula in "TestName" and returns the value from sheet 3 A1 Mike "Werner Rohrmoser" wrote: The name (string) of the relative name (e.g "TestName") is written in Range "A1". TestName itself is a named formula like "= SheetRef!A$3". Purpose is to get always the value in row three of the correspondent column. When I enter "=TestName" in Range "F26" i will get the value of Range "F3". So, and now I'd like to use a formula, where I use the relative name, which is entered in Range "A1", something like =INDIRECT($A$1), but this doesn't work. The idea is to use the values in column "A" for the relative names in the INDIRECT function. Any approach for a solution? Regards Werner |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT, relative name as argument
Hi Bernd,
is your name really defined as: =Sheet1!C1 or as Sheet1!$C$1? When you use mixed relative or relative names it doesn't work and this is my problem. Regards, Werner |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT, relative name as argument
As in my previous post that still works perfectly for me independant of
whether the ranges are referenced absolutely. Mike "Werner Rohrmoser" wrote: Hi Bernd, is your name really defined as: =Sheet1!C1 or as Sheet1!$C$1? When you use mixed relative or relative names it doesn't work and this is my problem. Regards, Werner |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT, relative name as argument
Hi Mike,
difficult to explain, I'm going to try my best, I'm not a native speaker. See below row 1 to 3 In row 2 I have the string "TestName" in col A and then the formula to the right in all columns. In row 3 once more the same INDIRECT formula. A B C D E F G TestName A A A A A A <= INDIRECT($A$2) A <= INDIRECT($A$2) TestName defined as: =Sheet2!A$1 (column is relative) Regards Werner |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT, relative name as argument
Mike,
the named formula is referenced relative, not the reference for the INDIRECT function. When you do that you will get 0 instead of 3812. Regards Werner |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT, relative name as argument
I think implicit intersection does what you want:
define TestName as =SheetRef!$A$3:$IV$3 put TestName in cell A2 then =INDIRECT(A2) will give you the intersect of whatever column the INDIRECT is in and SheetRef row 3 Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Werner Rohrmoser" wrote in message ... The name (string) of the relative name (e.g "TestName") is written in Range "A1". TestName itself is a named formula like "= SheetRef!A$3". Purpose is to get always the value in row three of the correspondent column. When I enter "=TestName" in Range "F26" i will get the value of Range "F3". So, and now I'd like to use a formula, where I use the relative name, which is entered in Range "A1", something like =INDIRECT($A$1), but this doesn't work. The idea is to use the values in column "A" for the relative names in the INDIRECT function. Any approach for a solution? Regards Werner |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT, relative name as argument
Hi Charles,
yes that's it! It was really hard to explain what I was looking for. Great, it's not the first time that I learned something about names and relative references fom you. Thank you very much! Regards Werner |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
if argument? | Excel Discussion (Misc queries) | |||
Trying to use the INDIRECT funtion with a relative Row reference | Excel Discussion (Misc queries) | |||
IF THEN argument | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions |