Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Indirect en direct cell references
Hi all,
I want to use a range on another worksheet. For this the worksheet name is named in a cell G1 (called Jan2005), within this worksheet i have a big matrix and the management info is extracted by row, so the formula looks like this. =SUM((INDIRECT($G$1&"!O52:X52"))) So far so good. Now I want to copy this formula down to re-use the formula, as the management info is by row, I want the 052:X52 to adjust, so actually I want to set them outside the indirect influece and no longer be text, but a real range. Something like this, but it doesn't work =SUM((INDIRECT($G$1&"!")O52:X52)) I can't this get to work, anybody any idea ? |
#2
|
|||
|
|||
=SUM(INDIRECT("'"&$G$1&"'!"&SUBSTITUTE(CELL("Addre ss",($A$1,O53:X53)),"$A$1,","")))
Bart Schouw wrote: Hi all, I want to use a range on another worksheet. For this the worksheet name is named in a cell G1 (called Jan2005), within this worksheet i have a big matrix and the management info is extracted by row, so the formula looks like this. =SUM((INDIRECT($G$1&"!O52:X52"))) So far so good. Now I want to copy this formula down to re-use the formula, as the management info is by row, I want the 052:X52 to adjust, so actually I want to set them outside the indirect influece and no longer be text, but a real range. Something like this, but it doesn't work =SUM((INDIRECT($G$1&"!")O52:X52)) I can't this get to work, anybody any idea ? |
#3
|
|||
|
|||
Hi Aladin,
Thanks it works, altough I wonder if there is an easier solution to this, it seems a little artifical. Best regards Bart "Aladin Akyurek" wrote: =SUM(INDIRECT("'"&$G$1&"'!"&SUBSTITUTE(CELL("Addre ss",($A$1,O53:X53)),"$A$1,",""))) Bart Schouw wrote: Hi all, I want to use a range on another worksheet. For this the worksheet name is named in a cell G1 (called Jan2005), within this worksheet i have a big matrix and the management info is extracted by row, so the formula looks like this. =SUM((INDIRECT($G$1&"!O52:X52"))) So far so good. Now I want to copy this formula down to re-use the formula, as the management info is by row, I want the 052:X52 to adjust, so actually I want to set them outside the indirect influece and no longer be text, but a real range. Something like this, but it doesn't work =SUM((INDIRECT($G$1&"!")O52:X52)) I can't this get to work, anybody any idea ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Changing cell references | Excel Discussion (Misc queries) | |||
#REF! with cell value check | Excel Discussion (Misc queries) | |||
Graph and Cell References | Excel Discussion (Misc queries) | |||
Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) |