Changing multiple cell formulas

  • Thread starter Thread starter The.Mantz
  • Start date Start date
T

The.Mantz

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.)
Click to expand...

---
The.Mantz said:
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
Click to expand...
 
=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)

---
 
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
---
Click to expand...

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]
Click to expand...

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

---
 
Back
Top