Replicating worksheet references

[Guest]

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

 

[Pete_UK]

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

 

[Guest]

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

 

[Guest]

.. 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 before:
=INDIRECT("'[Book4]Sheet"&ROW(A1)&"'!A3")

*required for INDIRECT to work

---

 

[Guest]

Thanks Max. That works brilliantly

.. 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 before:
=INDIRECT("'[Book4]Sheet"&ROW(A1)&"'!A3")

*required for INDIRECT to work

---

 

[Max]

welcome, Sue.