Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill range/series help required please
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??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill range/series help required please
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, -- Farhad Hodjat "Loosey" wrote: 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??? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill range/series help required please
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. Loosey wrote: 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??? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill range/series help required please
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 On Tue, 29 May 2007 11:00:06 -0500, Dave Peterson wrote: 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. Loosey wrote: 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??? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill range/series help required please
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") -- Regards Roger Govier "Loosey" wrote in message ... 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??? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill range/series help required please
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. Gord Dibben wrote: 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 On Tue, 29 May 2007 11:00:06 -0500, Dave Peterson wrote: 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. Loosey wrote: 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??? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill series | Excel Discussion (Misc queries) | |||
fill series | New Users to Excel | |||
How can I fill in the required fields on my spreadsheet? | Excel Discussion (Misc queries) | |||
Edit Fill Series - How do I fill using minute increments | Excel Discussion (Misc queries) | |||
make a cell required to fill-in on form template | Excel Worksheet Functions |