Dynamic cell reference using INDIRECT..
 

Hi,

Is there a way to take this formula "=INDIRECT("'"&$P$3&"'!X9")" and
make X9 dynamic upon copying to other cells? For P3 I only need to
remove the $'s.

Thanks!

- Alden

Dynamic cell reference using INDIRECT..
 

Try this one..

=INDIRECT("'"&$P$3&"'!"&ADDRESS(ROW(),COLUMN()))

Hope this helps.
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


" wrote:

Hi,

Is there a way to take this formula "=INDIRECT("'"&$P$3&"'!X9")" and
make X9 dynamic upon copying to other cells? For P3 I only need to
remove the $'s.

Thanks!

- Alden

Dynamic cell reference using INDIRECT..
 

I should have been more specific, I need the cell reference to be
relative to the cell, but it will likely not match. Instead of ROW()
and COLUMN() I entered in the correct number, and that returned the
correct value, however it did not let me copy it dynamically. Thank
you for your help so far.


On Jul 27, 2:46 am, Pranav Vaidya
wrote:
Try this one..

=INDIRECT("'"&$P$3&"'!"&ADDRESS(ROW(),COLUMN()))

Hope this helps.
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!

" wrote:
Hi,

Is there a way to take this formula "=INDIRECT("'"&$P$3&"'!X9")" and
make X9 dynamic upon copying to other cells? For P3 I only need to
remove the $'s.

Thanks!

- Alden

Dynamic cell reference using INDIRECT..
 

wrote...
Is there a way to take this formula "=INDIRECT("'"&$P$3&"'!X9")" and
make X9 dynamic upon copying to other cells? For P3 I only need to
remove the $'s.

=INDIRECT("'"&$P$3&"'!"&CELL("Address",X9))

or if you want it internationalization-safe,

=INDIRECT(ADDRESS(ROW(X9),COLUMN(X9),,,$P$3))

Dynamic cell reference using INDIRECT..
 

On Jul 27, 3:24 am, "Harlan Grove" wrote:
wrote...
Is there a way to take this formula "=INDIRECT("'"&$P$3&"'!X9")" and
make X9 dynamic upon copying to other cells? For P3 I only need to
remove the $'s.

=INDIRECT("'"&$P$3&"'!"&CELL("Address",X9))

or if you want it internationalization-safe,

=INDIRECT(ADDRESS(ROW(X9),COLUMN(X9),,,$P$3))

I am not sure what internationalization safe could mean except if
referring to another language version of excel. This did the trick, I
still have a lot of editing while copy/pasting to do but this will
make it much better! Thanks!