Offset formula

[Guest]

Can someone help me with what I think will be an offset formula.
My spreadsheet is used to prepare an import file for Accpac. Every 10 rows,
colum D is linked to a customer number in another worksheet. The source
worksheet has data in continuous rows. The import file has data for the same
customer in 10 rows so column D, every 10 rows, the link should only increase
by 1, not 10. Thanks in advance!

 

[Guest]

with data in column A, this formula in cell C1 (and copied down) will return
the contents of A1 in C1:C10, A2 in C11:C20, etc......adapt as needed.

=OFFSET($A$1,INT((ROWS(C$1:C1)-1)/10),0)

 

[Guest]

Tried to work with your formula but just can't seem to get it. Here is some
actual data:

RECTYPE CNTBTCH CNTITEM IDCUST IDINVC TEXTTRX
RECTYPE CNTBTCH CNTITEM CNTLINE IDITEM IDDIST
RECTYPE CNTBTCH CNTITEM CNTPAYM DATEDUE AMTDUE
1 2000 1 780A 2008001 1
2 2000 1 1 101 1
2 2000 1 2 169 1
2 2000 1 3 169 1
2 2000 1 4 169 1
2 2000 1 5 169 1
2 2000 1 6 169 1
2 2000 1 7 169 1
2 2000 1 8 110 10
1 2000 2 110A 2008002 1
2 2000 2 1 101 1
2 2000 2 2 169 1
2 2000 2 3 169 1
2 2000 2 4 169 1
2 2000 2 5 169 1
2 2000 2 6 169 1
2 2000 2 7 169 1
2 2000 2 8 110 10
1 2000 3 20A 2008003 1
2 2000 3 1 101 1
2 2000 3 2 169 1
2 2000 3 3 169 1
2 2000 3 4 169 1
2 2000 3 5 169 1
2 2000 3 6 169 1
2 2000 3 7 169 1
2 2000 3 8 110 10

It's the "780A, 110A, 20A etc. that needs to change and comes from another
spreadsheet where this data is in consecutive rows, i.e. 780A is in cell D27,
110A is in cell D28, 20A is in cell D29, etc.

 

[Guest]

Okay - I thought you wanted 780A repeated 10 times, then 110A, etc. I assume
you want to keep the 1-8 numbering in between 780A and 110A.

Let's say 780A appears in cell D4 and 780A appears on Sheet2!D27. I would
try this formula in cell D4 and copy down.

=IF(MOD(ROWS(D$44)-1,9),MOD(ROWS(D$44)-1,9),OFFSET(Sheet2!$D$27,INT((ROWS(D$44)-1)/9),0))

This will put the data from Sheet2 in every 9th cell and the cells in
between should be numbered 1-8. Be sure to backup in case it is not what you
need.

 

[Guest]

That works beautifully - thanks so much!

Okay - I thought you wanted 780A repeated 10 times, then 110A, etc. I assume
you want to keep the 1-8 numbering in between 780A and 110A.

Let's say 780A appears in cell D4 and 780A appears on Sheet2!D27. I would
try this formula in cell D4 and copy down.

=IF(MOD(ROWS(D$44)-1,9),MOD(ROWS(D$44)-1,9),OFFSET(Sheet2!$D$27,INT((ROWS(D$44)-1)/9),0))

This will put the data from Sheet2 in every 9th cell and the cells in
between should be numbered 1-8. Be sure to backup in case it is not what you
need.