Replicating worksheet references
 

I have 2 workbooks. The first contains data for events that I run and the
data for each event is on a seperate worksheet. The second workbook is a
summary of all the events and as such gets updated after each event. I want
to automatically insert the data from cell A3 of sheet1 in the first workbook
to cell D4 in the summary workbook and I know how to do this but is there any
way of replicating the formula in cell D4 so that it changes to sheet2,
sheet3 etc. Thanks for your help

Replicating worksheet references
 

I presume you have something like:

=Sheet1!A3

in D4 of your summary sheet and you want the A3 to remain constant as
you copy down but you want the sheet numbers to increment? If so, try
this in D4:

=INDIRECT("Sheet"&ROW(A1)&"!$A$3")

and copy this down. The ROW(A1) term returns 1, but becomes ROW(A2),
ROW(A3) etc as you copy it down, thus returning 2, 3, 4 etc.

Hope this helps.

Pete

On Nov 5, 9:16 am, SueG wrote:
I have 2 workbooks. The first contains data for events that I run and the
data for each event is on a seperate worksheet. The second workbook is a
summary of all the events and as such gets updated after each event. I want
to automatically insert the data from cell A3 of sheet1 in the first workbook
to cell D4 in the summary workbook and I know how to do this but is there any
way of replicating the formula in cell D4 so that it changes to sheet2,
sheet3 etc. Thanks for your help

Replicating worksheet references
 

Thanks Pete. That works great in the same workbook but I'm not sure what I
have to add to the start of the formula so that the data comes from a
different workbook

"Pete_UK" wrote:

I presume you have something like:

=Sheet1!A3

in D4 of your summary sheet and you want the A3 to remain constant as
you copy down but you want the sheet numbers to increment? If so, try
this in D4:

=INDIRECT("Sheet"&ROW(A1)&"!$A$3")

and copy this down. The ROW(A1) term returns 1, but becomes ROW(A2),
ROW(A3) etc as you copy it down, thus returning 2, 3, 4 etc.

Hope this helps.

Pete

On Nov 5, 9:16 am, SueG wrote:
I have 2 workbooks. The first contains data for events that I run and the
data for each event is on a seperate worksheet. The second workbook is a
summary of all the events and as such gets updated after each event. I want
to automatically insert the data from cell A3 of sheet1 in the first workbook
to cell D4 in the summary workbook and I know how to do this but is there any
way of replicating the formula in cell D4 so that it changes to sheet2,
sheet3 etc. Thanks for your help

Replicating worksheet references
 

"SueG" wrote:
.. That works great in the same workbook but I'm not sure what I
have to add to the start of the formula so that the data comes from a
different workbook

Provided the source book (eg: book4.xls) is open at the same time*,
you could use something like this in the other book,
and copy down as befo
=INDIRECT("'[Book4]Sheet"&ROW(A1)&"'!A3")

*required for INDIRECT to work
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Replicating worksheet references
 

Thanks Max. That works brilliantly

"Max" wrote:

"SueG" wrote:
.. That works great in the same workbook but I'm not sure what I
have to add to the start of the formula so that the data comes from a
different workbook

Provided the source book (eg: book4.xls) is open at the same time*,
you could use something like this in the other book,
and copy down as befo
=INDIRECT("'[Book4]Sheet"&ROW(A1)&"'!A3")

*required for INDIRECT to work
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Replicating worksheet references
 

welcome, Sue.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"SueG" wrote
Thanks Max. That works brilliantly