This is complex to describe but here goes.
If have 12 sheets representing a sports draw with every sheet representing
on division/grade of the competition. Within those sheets each round (1 week)
is represented by 12 rows of data.
I then have a sheet for the first round of competition where it gathers the
relevant data from the same set of rows across the 12 sheets and displays it
in the single sheet. All works fine.

I now want to copy the first round sheet and make it the second round sheet
and have the references calculate based on a cell value or sheet name.

In the Round 1 sheet it gathers data by ref Division1'!$d3, next row
Division1'!$d4, next row Division1'!$d5 etc for 5 rows.
When i copy this sheet for Rount 2 i need the references to be ref
Division1'!$d13, next row Division1'!$d14, next row Division1'!$d15 etc for 5
rows. in the source sheet the data is in multiples of 10 rows but i only use
the first 5.

The i will copy again the next week for Round 3 and the references will be
ref Division1'!$d23, next row Division1'!$d24, next row Division1'!$d25 etc
for 5 rows

Obviously i can manually alrer the row reference each time I copy the sheets
but i might make a mistake and there many columns with the same issue.

I would like to be able to use a formula to calcualte the row numbers based
on multiple of the Round number ie Round 1 the row numbers are all single
digits and start at 3 ie 03, Round 2 they start at 13, next round they start
at 23 etc.

I can easily calculate the leading digits of the row number based on the
Round value but how do i get it into a formula so than when i copy the Round
1 sheet to be the Round 2 sheet I don't have to manually change the row
reference in the above formulas.

I have a recollection that I've done this many years ago but I just can't
recall how.

regards

Graeme

I think you need to use INDIRECT ...

Assuming you want to refer to Division1!$d3 use this
=INDIRECT("Division1!$"& C1) which will give you the valuein Division1!$d3
if C1 contains the string D3.

You can adapt it to replace C1 by the formula which will return D3 as its
result and so on for other cells.

"Gadgetgw" wrote:

This is complex to describe but here goes.
If have 12 sheets representing a sports draw with every sheet representing
on division/grade of the competition. Within those sheets each round (1 week)
is represented by 12 rows of data.
I then have a sheet for the first round of competition where it gathers the
relevant data from the same set of rows across the 12 sheets and displays it
in the single sheet. All works fine.

I now want to copy the first round sheet and make it the second round sheet
and have the references calculate based on a cell value or sheet name.

In the Round 1 sheet it gathers data by ref Division1'!$d3, next row
Division1'!$d4, next row Division1'!$d5 etc for 5 rows.
When i copy this sheet for Rount 2 i need the references to be ref
Division1'!$d13, next row Division1'!$d14, next row Division1'!$d15 etc for 5
rows. in the source sheet the data is in multiples of 10 rows but i only use
the first 5.

The i will copy again the next week for Round 3 and the references will be
ref Division1'!$d23, next row Division1'!$d24, next row Division1'!$d25 etc
for 5 rows

Obviously i can manually alrer the row reference each time I copy the sheets
but i might make a mistake and there many columns with the same issue.

I would like to be able to use a formula to calcualte the row numbers based
on multiple of the Round number ie Round 1 the row numbers are all single
digits and start at 3 ie 03, Round 2 they start at 13, next round they start
at 23 etc.

I can easily calculate the leading digits of the row number based on the
Round value but how do i get it into a formula so than when i copy the Round
1 sheet to be the Round 2 sheet I don't have to manually change the row
reference in the above formulas.

I have a recollection that I've done this many years ago but I just can't
recall how.

regards

Graeme

Thanks

it didn't work at first but as soon as I'd open the other workbook all was
well.

Graeme

"Sheeloo" wrote:

I think you need to use INDIRECT ...

Assuming you want to refer to Division1!$d3 use this
=INDIRECT("Division1!$"& C1) which will give you the valuein Division1!$d3
if C1 contains the string D3.

You can adapt it to replace C1 by the formula which will return D3 as its
result and so on for other cells.

"Gadgetgw" wrote:

This is complex to describe but here goes.
If have 12 sheets representing a sports draw with every sheet representing
on division/grade of the competition. Within those sheets each round (1 week)
is represented by 12 rows of data.
I then have a sheet for the first round of competition where it gathers the
relevant data from the same set of rows across the 12 sheets and displays it
in the single sheet. All works fine.

I now want to copy the first round sheet and make it the second round sheet
and have the references calculate based on a cell value or sheet name.

In the Round 1 sheet it gathers data by ref Division1'!$d3, next row
Division1'!$d4, next row Division1'!$d5 etc for 5 rows.
When i copy this sheet for Rount 2 i need the references to be ref
Division1'!$d13, next row Division1'!$d14, next row Division1'!$d15 etc for 5
rows. in the source sheet the data is in multiples of 10 rows but i only use
the first 5.

The i will copy again the next week for Round 3 and the references will be
ref Division1'!$d23, next row Division1'!$d24, next row Division1'!$d25 etc
for 5 rows

Obviously i can manually alrer the row reference each time I copy the sheets
but i might make a mistake and there many columns with the same issue.

I would like to be able to use a formula to calcualte the row numbers based
on multiple of the Round number ie Round 1 the row numbers are all single
digits and start at 3 ie 03, Round 2 they start at 13, next round they start
at 23 etc.

I can easily calculate the leading digits of the row number based on the
Round value but how do i get it into a formula so than when i copy the Round
1 sheet to be the Round 2 sheet I don't have to manually change the row
reference in the above formulas.

I have a recollection that I've done this many years ago but I just can't
recall how.

regards

Graeme