Column reference problems. HELP!!!

[keith]

I want to know how to copy the formula below to the next cell down a column,
but I want it to increment the column reference from C to D to E then F and
so on, hence the reason I've not $ signed the column referenece.

=IF('Shift plan'!C$6=0,"",'Shift plan'!C$6)
=IF('Shift plan'!D$6=0,"",'Shift plan'!D$6)

But what I get is:

=IF('Shift plan'!C$6=0,"",'Shift plan'!C$6)
=IF('Shift plan'!C$6=0,"",'Shift plan'!C$6)
=IF('Shift plan'!C$6=0,"",'Shift plan'!C$6)

Why does it not increment the column reference and how do I get it to
increment?

Thanks in anticipation Keith

 

[Sheeloo]

Column reference changes if you drag or copy the formula to the RIGHT (from
A1 to B1, C1 and so on, IF you do not have $ before the column reference.
Row reference changes if you drag or copy the formula DOWN (from A1 to A2,
A3 and so on) IF you do not have $ before the row reference.

So A1 will change to B1 (right) and A2 (down)
$A1 will reamain $A1 (right) but change to $A2 (down)...
A$1 will be B$1 (right) and remain A$1 (down)

Hope I got all that right but you must have got the idea.

 

[Max]

One way which addresses the "how do I get it to increment (in that manner) bit"
is to replace your starting formula:

=IF('Shift plan'!C$6=0,"",'Shift plan'!C$6)

with this expression:
=IF(OFFSET('Shift plan'!C$6,,ROWS($1:1)-1)=0,"",OFFSET('Shift
plan'!C$6,,ROWS($1:1)-1))

Then, when you copy the above down, it'll return what you seek, ie:
=IF('Shift plan'!D$6=0,"",'Shift plan'!D$6)
=IF('Shift plan'!E$6=0,"",'Shift plan'!E$6)
etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---

 

[John C]

See Sheeloo's comment as to why what you are trying won't work. What you
could do, is use INDIRECT and ADDRESS functions to accomplish what you need.
Say for example, your formula
=IF('Shift plan'!C$6=0,"",'Shift plan'!C$6)
starts on row 10
Then you could use the following formula:
=IF(INDIRECT("'Shift Plan'!"&ADDRESS(6,3+ROW()-10))=0,"",INDIRECT("'Shift
Plan'!"&ADDRESS(6,3+ROW()-10)))
and copy down. the ADDRESS function always refers to row 6, and the column
aspect starts off at baseline 3, then for every row beyond 10, it will add 1.
You could use the same logic for the row function if necessary.