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

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

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

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

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)

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

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