Hi,
I have a multiple page document in Excel, where the first page is a
summary of information, and the other pages pull information from this
summary page. On the individual pages, I can enter in more specific
information. Now, if I want to create another individual page that
pulls information from the summary page, I right click on the tab and
create a copy of the page. With this copy, however, I want to link to
a different section on the summary page. How can I change the formulas
in this entire section without manually changing the cells?
Example (so you know what I mean):
The area on the original individual page looks like this:
[='Reference'!$B$ 7 ][='Reference'!$D$ 9 ][='Reference'!$A$ 7]
[='Reference'!$B$10][='Reference'!$D$12][='Reference'!$A$10]
[='Reference'!$B$13][='Reference'!$D$15][='Reference'!$A$13]
(....etc.)
And when I copy the sheet it remains the same, but I want it to look
like this:
[='Reference'!$B$38][='Reference'!$D$40][='Reference'!$A$38]
[='Reference'!$B$41][='Reference'!$D$43][='Reference'!$A$41]
[='Reference'!$B$44][='Reference'!$D$46][='Reference'!$A$43]
(....etc.)
I want all of the references to change 31 cells down. How do I do
this?
Thanks!
The.Mantz

> [='Reference'!$B$ 7 ][='Reference'!$D$ 9 ][='Reference'!$A$ 7]

Replace the 3 top cell formulas above (eg in A1:C1) with:
=INDIRECT("'Reference'!B"&ROW(A1)*3-3+38)
=INDIRECT("'Reference'!D"&ROW(A1)*3-3+38)
=INDIRECT("'Reference'!A"&ROW(A1)*3-3+38)

Then select all 3 cells (eg: A1:C1) and copy down as far as required
to return the desired results:
> [='Reference'!$B$38][='Reference'!$D$40][='Reference'!$A$38]
> [='Reference'!$B$41][='Reference'!$D$43][='Reference'!$A$41]
> [='Reference'!$B$44][='Reference'!$D$46][='Reference'!$A$43]
> (....etc.)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"The.Mantz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
> I have a multiple page document in Excel, where the first page is a
> summary of information, and the other pages pull information from this
> summary page. On the individual pages, I can enter in more specific
> information. Now, if I want to create another individual page that
> pulls information from the summary page, I right click on the tab and
> create a copy of the page. With this copy, however, I want to link to
> a different section on the summary page. How can I change the formulas
> in this entire section without manually changing the cells?
> Example (so you know what I mean):
> The area on the original individual page looks like this:
> [='Reference'!$B$ 7 ][='Reference'!$D$ 9 ][='Reference'!$A$ 7]
> [='Reference'!$B$10][='Reference'!$D$12][='Reference'!$A$10]
> [='Reference'!$B$13][='Reference'!$D$15][='Reference'!$A$13]
> (....etc.)
> And when I copy the sheet it remains the same, but I want it to look
> like this:
> [='Reference'!$B$38][='Reference'!$D$40][='Reference'!$A$38]
> [='Reference'!$B$41][='Reference'!$D$43][='Reference'!$A$41]
> [='Reference'!$B$44][='Reference'!$D$46][='Reference'!$A$43]
> (....etc.)
> I want all of the references to change 31 cells down. How do I do
> this?
> Thanks!
> The.Mantz
>

> =INDIRECT("'Reference'!D"&ROW(A1)*3-3+38)

Oops, slight correction to the above "middle" formula (eg: in B1)

It should have read as:
=INDIRECT("'Reference'!D"&ROW(A1)*3-3+40)
since the link points to col D's row 40 (D40) in the top cell
(unlike the other 2 which points to row 38)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On May 22, 5:54 pm, "Max" <demecha...@yahoo.com> wrote:
> > =INDIRECT("'Reference'!D"&ROW(A1)*3-3+38)
>
> Oops, slight correction to the above "middle" formula (eg: in B1)
>
> It should have read as:
> =INDIRECT("'Reference'!D"&ROW(A1)*3-3+40)
> since the link points to col D's row 40 (D40) in the top cell
> (unlike the other 2 which points to row 38)
> --
> Max
> Singaporehttp://savefile.com/projects/236895
> xdemechanik
> ---

Max,
thanks for your help, but I can't seem to get this to work. Where does
the 'A1' part link to? To the 'Reference' page, or the individual
page?
Thanks.
- The.Mantz

The ROW(A1) within the INDIRECT formulas is used as an incrementer in the
top* cell formula. It simply returns the number series: 1,2,3... as you copy
down.
*the 1st cell containing the formula right at the top, which is then copied
down.

To apply, refer to my earlier postings, re:

> [='Reference'!$B$ 7 ][='Reference'!$D$ 9 ][='Reference'!$A$ 7]

Replace the 3 top cell formulas above (eg in A1:C1) with:
=INDIRECT("'Reference'!B"&ROW(A1)*3-3+38)
=INDIRECT("'Reference'!D"&ROW(A1)*3-3+40)
=INDIRECT("'Reference'!A"&ROW(A1)*3-3+38)

The above means ..
supposing you have the simple link formula in A1: ='Reference'!$B$ 7

Just replace the formula in A1 with:
=INDIRECT("'Reference'!B"&ROW(A1)*3-3+38)
The above will return the same results as the link formula:
='Reference'!$B$38

The part: ROW(A1)*3-3+38 resolves to 1x3-3+38 = 38
which is then concatenated with the string: 'Reference'!B
to yield: 'Reference'!B38

INDIRECT(...) then resolves the concat string to return the contents of:
'Reference'!B38

And when you copy A1 down to A2,
the INDIRECT formula will "increment"** to return
the same as the link formula:
='Reference'!$B$41
and so on, which are exactly the results that you're after

**In A2, the formula will become:
=INDIRECT("'Reference'!B"&ROW(A2)*3-3+38)
where: ROW(A2)*3-3+38 = 2x3-3+38 = 41
and the concat string is hence: 'Reference'!B41

Hope the above clarifies it further ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"The.Mantz" <(E-Mail Removed)> wrote
> Max,
> thanks for your help, but I can't seem to get this to work. Where does
> the 'A1' part link to? To the 'Reference' page, or the individual
> page?
> Thanks.
> - The.Mantz
>