Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column reference problems. HELP!!!
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column reference problems. HELP!!!
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. "keith" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column reference problems. HELP!!!
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 --- "keith" wrote: 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 Thanks in anticipation Keith |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column reference problems. HELP!!!
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. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "keith" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any ideas why a reference that works will in 2003 has problems in | Excel Worksheet Functions | |||
cell reference problems | Excel Discussion (Misc queries) | |||
Linest: problems using broken reference data | Excel Worksheet Functions | |||
Row reference increment but preserve column reference | Excel Worksheet Functions | |||
Cell reference problems with Summary sheet | Excel Worksheet Functions |