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

try

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

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


--
Pranav Vaidya
VBA Developer
PN, MH-India


"BarryL" wrote:

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

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

--
Pranav Vaidya
VBA Developer
PN, MH-India


"BarryL" wrote:

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

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


"BarryL" wrote in message
...
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"?

This works perfectly - Thnks

"Pranav Vaidya" wrote:

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

--
Pranav Vaidya
VBA Developer
PN, MH-India


"BarryL" wrote:

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote...
Change the formula from:
....
To
....
=(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))