Fill range/series help required please

[Guest]

I am trying to auto fill a series, the problem I have is the wrong values are
amended through the series and I cannot see how to correct this. . .

My series to fill is:
=COUNTIF('[2007.xls]Mar 07'!B2:W2,"U")

I want to see:
=COUNTIF('[2007.xls]Mar 07'!B3:W3,"U")
=COUNTIF('[2007.xls]Mar 07'!B4:W4,"U")

What I actually get is:
=COUNTIF('[2007.xls]Mar 07'!C3:X3,"U")
=COUNTIF('[2007.xls]Mar 07'!D3:Y3,"U")

I really want the cell letter to remain the same but the row identifier to
change, however I get the opposite of what I want/need. . . . anyone got any
ideas please???

 

[Guest]

Hi,

To fix the letters (column) put a $ in the right side of letter e.i.: $B3:$W3
To fis the numbers (row) put a $ in the left side of the letter e.i.: B$3:W$3

Thanks,

 

[Dave Peterson]

If you try:

=COUNTIF('[2007.xls]Mar 07'!$B2:$W2,"U")

Does it work ok?

Be aware that as soon as you close 2007.xls and recalculate, you're going to see
an error. =countif() won't work when the sending workbook is closed.

I am trying to auto fill a series, the problem I have is the wrong values are
amended through the series and I cannot see how to correct this. . .

My series to fill is:
=COUNTIF('[2007.xls]Mar 07'!B2:W2,"U")

I want to see:
=COUNTIF('[2007.xls]Mar 07'!B3:W3,"U")
=COUNTIF('[2007.xls]Mar 07'!B4:W4,"U")

What I actually get is:
=COUNTIF('[2007.xls]Mar 07'!C3:X3,"U")
=COUNTIF('[2007.xls]Mar 07'!D3:Y3,"U")

I really want the cell letter to remain the same but the row identifier to
change, however I get the opposite of what I want/need. . . . anyone got any
ideas please???

 

[Gord Dibben]

When Loosey writes

What I actually get is:
=COUNTIF('[2007.xls]Mar 07'!C3:X3,"U")
=COUNTIF('[2007.xls]Mar 07'!D3:Y3,"U")

To get that incrementing, looks to me like Loosey is dragging across columns
rather than dragging down rows of one column so the absolute refs won't do the
job.

Or am I missing what's happening?


Gord

If you try:

=COUNTIF('[2007.xls]Mar 07'!$B2:$W2,"U")

Does it work ok?

Be aware that as soon as you close 2007.xls and recalculate, you're going to see
an error. =countif() won't work when the sending workbook is closed.

I am trying to auto fill a series, the problem I have is the wrong values are
amended through the series and I cannot see how to correct this. . .

My series to fill is:
=COUNTIF('[2007.xls]Mar 07'!B2:W2,"U")

I want to see:
=COUNTIF('[2007.xls]Mar 07'!B3:W3,"U")
=COUNTIF('[2007.xls]Mar 07'!B4:W4,"U")

What I actually get is:
=COUNTIF('[2007.xls]Mar 07'!C3:X3,"U")
=COUNTIF('[2007.xls]Mar 07'!D3:Y3,"U")

I really want the cell letter to remain the same but the row identifier to
change, however I get the opposite of what I want/need. . . . anyone got any
ideas please???

 

[Roger Govier]

Hi

Maybe
=COUNTIF(OFFSET('[2007.xls]Mar 07'!$B$2,0,ROW(A1)-ROW($A$1)):
OFFSET('[2007.xls]Mar 07'!$W$2,0,ROW(A1)-ROW($A$1)),"u")

 

[Dave Peterson]

The column letters will stay the same (B and W) with the $'s there. But you're
right, I don't know how the OP got the rows to increase by filling to the right.

But I think using =countif() is gonna cause more trouble than Loosey expects
when that workbook closes.

I figured I'd wait for the real question to show up <bg>.

When Loosey writes

What I actually get is:
=COUNTIF('[2007.xls]Mar 07'!C3:X3,"U")
=COUNTIF('[2007.xls]Mar 07'!D3:Y3,"U")

To get that incrementing, looks to me like Loosey is dragging across columns
rather than dragging down rows of one column so the absolute refs won't do the
job.

Or am I missing what's happening?

Gord

If you try:

=COUNTIF('[2007.xls]Mar 07'!$B2:$W2,"U")

Does it work ok?

Be aware that as soon as you close 2007.xls and recalculate, you're going to see
an error. =countif() won't work when the sending workbook is closed.

I am trying to auto fill a series, the problem I have is the wrong values are
amended through the series and I cannot see how to correct this. . .

My series to fill is:
=COUNTIF('[2007.xls]Mar 07'!B2:W2,"U")

I want to see:
=COUNTIF('[2007.xls]Mar 07'!B3:W3,"U")
=COUNTIF('[2007.xls]Mar 07'!B4:W4,"U")

What I actually get is:
=COUNTIF('[2007.xls]Mar 07'!C3:X3,"U")
=COUNTIF('[2007.xls]Mar 07'!D3:Y3,"U")

I really want the cell letter to remain the same but the row identifier to
change, however I get the opposite of what I want/need. . . . anyone got any
ideas please???