Cell Propogation

[Guest]

I am trying to refference specific cells from one worksheet to another. The
information I need is not in consecutive cells, they are, for example, 4
cells apart.

My problem is I have over 8000 cells that need filling and I am not about to
do that by hand When I put a few entries in then try to have the pattern
propogate it doesn't work. instead it backtracks. for example, if I entered
4 cells:
salary!g4
salary!g8
salary!g12
salary!g16

when i try to propogate i get a patern like:

salary!g8
salary!g12
salary!g16
salary!g20
salary!g12
salary!g16
salary!g20
salary!g24

How do i make the pattern consistant over the 8000 cells?

 

[Guest]

One way ..

Put this in the starting cell, say, in B2:
=INDIRECT("'salary'!G"&ROW(A1)*4)

B2 returns the same as: =salary!G4

Just copy B2 down as far as required* to return the desired:

salary!g4
salary!g8
salary!g12
salary!g16

....

*down to B2001 thereabouts, since you have 8000 cells ..

---

 

[Guest]

Thanks a bunch. that worked well.

One more though, I can't seem tofigure it out from your last post.

I have another worksheet that takes its first value from salary!H4, and the
next one is at salary!H100, then salary!H196 (and every 196 thereafter)

I can't seem to get that one to work. maybe It's because I don't fully
understand the formula in your last post.

Thanks!

 

[Guest]

Thanks a bunch. that worked well.

You're welcome !

I have another worksheet that takes its first value from salary!H4, and the
next one is at salary!H100, then salary!H196
(and every 96 thereafter) [Interval typo slightly corrected to 96 <g>]

Put this in the starting cell, and copy down:
=INDIRECT("'salary'!H"&ROW(A1)*96-92)

The construct is similar to the previous except for the multiplier change
from 4 to 96 and the need for a simple arithmetic adjustment since the
starting cell is to point to salary!H4 (so we subtract 92 from 96).

Tinker with this to see what's happening when we copy down ..

Place this in any cell, say C3: =ROW(A1)*96-92
C1 will return as: = (1 x 96) - 92 = 4
( ROW(A1) resolves to: 1 )

When we copy C1 down to C2,
the formula will increment* to: =ROW(A2)*96-92
which now returns: = (2 x 96) - 92 = 100
( ROW(A2) resolves to: 2 )

*ROW(A1) becomes ROW(A2), and so on as we copy down ...

The above hence generates the required number series: 4, 100, 196, ...
for concatenation as the row refs within the INDIRECT

---

 

[Guest]

Oops, typo in line:

Place this in any cell, say C3: =ROW(A1)*96-92

should read as:

Place this in any cell, say C1: =ROW(A1)*96-92

---

 

[Guest]

Your a life saver Max.

Thank you for the explanation, it was very helpfull!

Daiv.

Thanks a bunch. that worked well.

You're welcome !

I have another worksheet that takes its first value from salary!H4, and the
next one is at salary!H100, then salary!H196
(and every 96 thereafter) [Interval typo slightly corrected to 96 <g>]

Put this in the starting cell, and copy down:
=INDIRECT("'salary'!H"&ROW(A1)*96-92)

The construct is similar to the previous except for the multiplier change
from 4 to 96 and the need for a simple arithmetic adjustment since the
starting cell is to point to salary!H4 (so we subtract 92 from 96).

Tinker with this to see what's happening when we copy down ..

Place this in any cell, say C3: =ROW(A1)*96-92
C1 will return as: = (1 x 96) - 92 = 4
( ROW(A1) resolves to: 1 )

When we copy C1 down to C2,
the formula will increment* to: =ROW(A2)*96-92
which now returns: = (2 x 96) - 92 = 100
( ROW(A2) resolves to: 2 )

*ROW(A1) becomes ROW(A2), and so on as we copy down ...

The above hence generates the required number series: 4, 100, 196, ...
for concatenation as the row refs within the INDIRECT

---

 

[Guest]

Your a life saver Max.
Thank you for the explanation, it was very helpfull!

Glad it was, Daiv !
You're welcome.

---