Sequential cell reference

[Jeff McCann]

Example: Cells A1.A500 = a list of anything

I need help with how to write formula so that value for
B:600 = A1; B:603 = A2; B:606 = A3; B:609 = A4; etc. on
through A500 without entering each formulae.

Your help is very much appreciated.

Jeff McCann

 

[Peo Sjoblom]

One fairly easy way if you mean that you want A1 in B600, then blank, blank
then in B603 A2

in B600 put this formula

=INDIRECT("A"&(ROW(3:3)/3))

copy down to B2000 something to get all then values,
it will look like

A1
#REF!
#REF!
A2
#REF!
#REF!
A3

and so on, when copied down select the whole range, copy it, paste special
as values in place,
press F5, click special, check constants and uncheck everything but errors,
click ok or press enter
and then press delete. Done. Takes less than a minute.. When it's done B600
will have A1s value
601 and 602 will be blank and 603 = A2 and so on

-

Regards,

Peo Sjoblom

 

[Guest]

That worked Great! Thank you.

I'm probably pushing it, ...... but it would be ideal if a
qualifer could be added e.g. @IF(A1>0,A1,0)for B:600; @IF
(A2>0,A2,o)for B603; ...

Possible?