Replacing cell referance with value in INDIRECT-formula
 

Hi!
Can anyone please guide me on how to change part of cell reference in this
formula:
=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$2")

The formula is in Sheet2, and I would like to change the reference $B$2 with
the value from cell H1 in sheet2.
I have tried with =INDIRECT("'[" & $A$19 & "]Sheet1'!$B$(=INDIRECT( H1)"),
but it gives error #REF.

Any help would be appreciated.
--
HebbeLille

Replacing cell referance with value in INDIRECT-formula
 

Hi,
I assume that you want to get the value from column A and from the row
indicated in cell H1 if that is the case use

=INDIRECT("A" & $H$1)

if not please provide an example, thanks

"HebbeLille" wrote:

Hi!
Can anyone please guide me on how to change part of cell reference in this
formula:
=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$2")

The formula is in Sheet2, and I would like to change the reference $B$2 with
the value from cell H1 in sheet2.
I have tried with =INDIRECT("'[" & $A$19 & "]Sheet1'!$B$(=INDIRECT( H1)"),
but it gives error #REF.

Any help would be appreciated.
--
HebbeLille

Replacing cell referance with value in INDIRECT-formula
 

Try the below (row reference from Sheet2 H1)

=INDIRECT("'["&$A$19&"]Sheet1'!$B$" & Sheet2!H1)

If this post helps click Yes
---------------
Jacob Skaria


"HebbeLille" wrote:

Hi!
Can anyone please guide me on how to change part of cell reference in this
formula:
=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$2")

The formula is in Sheet2, and I would like to change the reference $B$2 with
the value from cell H1 in sheet2.
I have tried with =INDIRECT("'[" & $A$19 & "]Sheet1'!$B$(=INDIRECT( H1)"),
but it gives error #REF.

Any help would be appreciated.
--
HebbeLille

Replacing cell referance with value in INDIRECT-formula
 

On Thu, 30 Jul 2009 10:12:01 -0700, HebbeLille
wrote:

Hi!
Can anyone please guide me on how to change part of cell reference in this
formula:
=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$2")

The formula is in Sheet2, and I would like to change the reference $B$2 with
the value from cell H1 in sheet2.
I have tried with =INDIRECT("'[" & $A$19 & "]Sheet1'!$B$(=INDIRECT( H1)"),
but it gives error #REF.

Any help would be appreciated.


If it is only the 2 in the $B$2 that should be replaced, try this:

=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$"&H1)

Hope this helps / Lars-Åke

Replacing cell referance with value in INDIRECT-formula
 

HebbeLille wrote...
Can anyone please guide me on how to change part of cell reference in this
formula:
=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$2")

The formula is in Sheet2, and I would like to change the reference $B$2 with
the value from cell H1 in sheet2.
I have tried with =INDIRECT("'[" & $A$19 & "]Sheet1'!$B$(=INDIRECT( H1)"),
but it gives error #REF.
....

You don't need the $s in the INDIRECT call. You could use

=INDIRECT("'[" & $A$19 & "]Sheet1'!B" & H1)

if transition-formula evaluation is disabled, or

=INDIRECT("'[" & $A$19 & "]Sheet1'!B" & TEXT(H1,"0"))

just to be safe.

If you have fewer than 30 different worksheet names that could appear
in cell A19, you don't even need to use INDIRECT. For example, you
could use something like

=INDEX(CHOOSE(MATCH($A$19,ListOfWorkbookNames,0),S heet1!$B:$B,Sheet2!
$B:$B,...,Sheet29!$B:$B),H1)

Replacing cell referance with value in INDIRECT-formula
 

Thanks! It worked just nice!!
--
HebbeLille


"Lars-Ã…ke Aspelin" wrote:

On Thu, 30 Jul 2009 10:12:01 -0700, HebbeLille
wrote:

Hi!
Can anyone please guide me on how to change part of cell reference in this
formula:
=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$2")

The formula is in Sheet2, and I would like to change the reference $B$2 with
the value from cell H1 in sheet2.
I have tried with =INDIRECT("'[" & $A$19 & "]Sheet1'!$B$(=INDIRECT( H1)"),
but it gives error #REF.

Any help would be appreciated.


If it is only the 2 in the $B$2 that should be replaced, try this:

=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$"&H1)

Hope this helps / Lars-Ã…ke

Replacing cell referance with value in INDIRECT-formula
 

Thank you!

It works nice now, and saved me for some boring work!
--
HebbeLille


"Jacob Skaria" wrote:

Try the below (row reference from Sheet2 H1)

=INDIRECT("'["&$A$19&"]Sheet1'!$B$" & Sheet2!H1)

If this post helps click Yes
---------------
Jacob Skaria


"HebbeLille" wrote:

Hi!
Can anyone please guide me on how to change part of cell reference in this
formula:
=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$2")

The formula is in Sheet2, and I would like to change the reference $B$2 with
the value from cell H1 in sheet2.
I have tried with =INDIRECT("'[" & $A$19 & "]Sheet1'!$B$(=INDIRECT( H1)"),
but it gives error #REF.

Any help would be appreciated.
--
HebbeLille