Autofill column values in formulas

[NOV Michael S]

I can't get excel to autofill my formula correctly. I need it to refer to
every sixth cell on another sheet. Excel just chooses the number of cells I
click for autofill and increments the cell number by that much.

e.g., =if(isnumber(sheet2!c1)... then next row down is =if(...c7) and next
is c13, etc. the autofill pattern for the next cells is c4, c10, and c16. I
need it to be c19, c25, and c31. Selecting 6 cells doesnt work either, i.e.,
1,7,13,19,25,31 produces 7,13,19,25,31,37. How do I go about this?

TIA

 

[Dave Peterson]

You may be able to use =indirect() or =index(), but you didn't share enough of
the formula to get a meaningful suggestion (from me anyway).

When I want this kind of increment in a formula, I'll do this...

I'll create a formula that builds a string that looks like the formula:

="=if(isnumber(sheet2!c"&(6*(row()-1)+1&")...."
This assumes that the formula is in row 1.
If the formula is in a different row, then I'd adjust that (6*(row()-1)+1 to a
different expression.

Then I'd drag this formula down.

If my formula that builds that string looks like it's the correct formula, then
I'll convert it to values.
(edit|copy followed by edit|paste special|values).

But the results are still just plain old strings (that look like formulas).

Next, I'd convert it to formulas.

Select the single column range.
Data|text to columns
Choose Fixed width, but don't have any delimiters.

Excel will see the results as those nice straight-forward formulas.

============
If you want to try this, but are having trouble, share your entire formula and
the cell that gets that first formula.

 

[NOV Michael S]

Thanks for responding.

It should not matter what the formula is or where it starts. Think of the
simplest formula you can that refers to any cell (n). Next row down the
formula should refer to cell (n+6).

Frustrating that if I put in any coulmn 6, 12, 18 and select and drag them
down 3 more rows I get 24, 30, and 36. But if I put it in a formula, e.g.,
row(c6), row(c12), row(c18), and do the same thing I get 9, 15, and 21. Not
logical.

I see where you're going with the example below, but I can't seem to make it
do what you're intending. I can't get it to autogenerate the numbers that you
want me to cut and paste as values.

If you need to know my exact formula, here it is.

=IF(OR(ISNUMBER('Price Summary - details'!I12),ISTEXT('Price Summary -
details'!I12)),'Price Summary - details'!I12," ") then substitute I12 with
increments of 6. This starts in row 20.

Thanks again for your attention.

 

[Dave Peterson]

The starting row of the cell with the formula makes a difference to the formula
that creates that string that looks like the formula.

 

[Dave Peterson]

I didn't notice the last sentence that the first formula starts in row 20:

="=IF(OR(ISNUMBER('Price Summary - details'!I"&((ROW()-20)+2)*6&"),
ISTEXT('Price Summary - details'!I"&((ROW()-20)+2)*6&")),
'Price Summary - details'!i"&((ROW()-20)+2)*6&","""")"


It looks like you're trying to hide the 0 that's returned when the sending cell
is empty.

This would work if you were typing it yourself:

=if('price summary - details'!i12="","",'price summary - details'!i12)

The formula that builds the formula for this would be:

="=IF('Price Summary - details'!I"&((ROW()-20)+2)*6&"="""","""",
'Price Summary - details'!I"&((ROW()-20)+2)*6&")"