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"?