Copying a formula which uses indirect()

[Guest]

I have a mult-page suvery analysis workbook that consists of a "source" table
that potentially contains all the survey responses arranged each set of
responses in a single row for each respondant. I have a number of other
tables that contain subsets of the survey data (i.e. selected rows of
respondant data). I would like to populate the source table with data from
the subset tables by changing a cell that contains the table reference. I
used the following formula in cell a4 of the source table
=(INDIRECT($A$2&"a4")) to capture the data from the table referenced in $A$2.
I would now like to copy this formula to the rest of the cells in the source
table so that when I change A2 the entire souce table changes. However, when
I copy the formula, a4 remains constant because it is in quotes. If I remove
the quotes A4 creates a circular reference in the source table.

Questions: Is this an efficient way to populate the source table?
If so how can I copy this formula to neighboring cells and have a4 change to
b5 for example? If not, how might I better handle the population of my
"source table"?

 

[Guest]

try

=INDIRECT($A$1&ADDRESS(COLUMN(),ROW()))
OR

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

 

[Guest]

try
=INDIRECT($A$1&ADDRESS(ROW(),COLUMN()))

 

[Bernie Deitrick]

Barry,

Change the formula from:

=(INDIRECT($A$2&"a4"))

To

=INDIRECT($A$2 & ADDRESS(ROW(A4),COLUMN(A4)))

and the A4 reference will update as you want.

Note that if A2 has a sheet name, you will need ! and may also need to add 's

=(INDIRECT("'" & $A$2&"'!" &ADDRESS(ROW(A4),COLUMN(A4))))

HTH,
Bernie
MS Excel MVP

 

[Guest]

This works perfectly - Thnks

try
=INDIRECT($A$1&ADDRESS(ROW(),COLUMN()))

 

[Harlan Grove]

Change the formula from: ....
....
=(INDIRECT("'" & $A$2&"'!" &ADDRESS(ROW(A4),COLUMN(A4))))

[Why the unnecessary outer parentheses?]

If you're going to use ADDRESS, at least use it to its fullest.

=INDIRECT(ADDRESS(ROW(A4),COLUMN(A4),,,$A$2))

Or use CELL.

=INDIRECT("'"&$A$2&"'!"&CELL("Address",A4))