Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative addressing with INDIRECT function
Hi group,
Suppose I want to copy this formula: =A1*INDIRECT("Sheet2!a1") To the next cell down, such that the new cell get's the formula: =A2*INDIRECT("Sheet2!a2") If I use the ordinary copy command, the new cell gets the formula: =A2*INDIRECT("Sheet2!a1") which is not what I want. I know there is a way to do this, because I've done it before--but it was a while back. Can someone please refresh my memory? Thanks in advance. -- John Uebersax |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative addressing with INDIRECT function
Assuming that sheet2!a1 contains the cell reference, then the formula
is wrong - it should be =A1*INDIRECT(Sheet2!a1) which will then copy correctly (it's the quotes that cause the problem!) if it's sheet2!a1 you actually want to refer to then you don't need the indirect at all. john wrote: Hi group, Suppose I want to copy this formula: =A1*INDIRECT("Sheet2!a1") To the next cell down, such that the new cell get's the formula: =A2*INDIRECT("Sheet2!a2") If I use the ordinary copy command, the new cell gets the formula: =A2*INDIRECT("Sheet2!a1") which is not what I want. I know there is a way to do this, because I've done it before--but it was a while back. Can someone please refresh my memory? Thanks in advance. -- John Uebersax |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative addressing with INDIRECT function
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative addressing with INDIRECT function
=A1*INDIRECT("Sheet2!A"&ROW(A1))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "john" wrote in message oups.com... wrote: Assuming that sheet2!a1 contains the cell reference, then the formula is wrong - it should be =A1*INDIRECT(Sheet2!a1) No, I meant the question exactly as I asked it. sheet2!a1 contains a value, not a formula or cell reference. The purpose of the INDIRECT function here is to let me rearrange cells in sheet2 without affecting formulas in sheet1. In any case, removing the quotes doesn't work. -- John Uebersax |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative addressing with INDIRECT function
=A1*Indirect("Sheet2!a" & row(A1))
would be one way. This wouldn't be affected by changes on Sheet2, but could be affected by changes on the sheet with the formula. -- Regards, Tom Ogilvy "john" wrote: wrote: Assuming that sheet2!a1 contains the cell reference, then the formula is wrong - it should be =A1*INDIRECT(Sheet2!a1) No, I meant the question exactly as I asked it. sheet2!a1 contains a value, not a formula or cell reference. The purpose of the INDIRECT function here is to let me rearrange cells in sheet2 without affecting formulas in sheet1. In any case, removing the quotes doesn't work. -- John Uebersax |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative addressing with INDIRECT function
Thanks Bob and Tom.
This seems to work for copying cells within the same column (which I recognize was how I posed the question). But what about also copying the formula =A1*INDIRECT("Sheet2!a1") to the next column, or to a 10x10 block of cells? -- John |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative addressing with INDIRECT function
=a1*INDIRECT("Sheet2!"&ADDRESS(ROW(A1),COLUMN(A1)) )
-- Regards, Tom Ogilvy "John Uebersax" wrote: Thanks Bob and Tom. This seems to work for copying cells within the same column (which I recognize was how I posed the question). But what about also copying the formula =A1*INDIRECT("Sheet2!a1") to the next column, or to a 10x10 block of cells? -- John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative Addressing not working | Excel Worksheet Functions | |||
Indirect function €“ relative column copy/paste | Excel Worksheet Functions | |||
Relative addressing | Excel Worksheet Functions | |||
Relative Addressing | Excel Discussion (Misc queries) | |||
Indirect function - relative reference | Excel Programming |