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

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

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

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

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

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