need to copy cell refs with different incrementation

[dmva]

I need to copy a range of cells, and some of them have references t
cells on another worksheet, but the cells with references are no
contiguous, nor are the referenced cells. I have tried the fill handl
to duplicate, and it does it partially correct, but not completely.

Example: B7 contains cell reference =eb!A4 (eb is the name of the othe
worksheet), and C9 contains cell reference =eb!B5. Cell B10 contain
the cell reference =eb!A8, and C12 contains =eb!B9. I want to cop
these six cells over and over down the page.

The problem is that I need the B cells to increment the number by thre
over the numbers in the C cells.

When I use the fill handle, if I have highlighted, say, 6 cells
B7:C12, it only increments the number in B13 to =eb!A10, one digit mor
than C12's =eb!B9. I need it to increment it to =eb!A12.

Of course, I tried highlighting 9 cells with the correct numbers i
them,and all it does is basically repeat the problem every 9 cells.

Hope I have made this clear. Doesn't seem too clear to me after typin
it...

Thanks.

dmv

 

[Frank Kabel]

Hi
you may try to explain the logic after your increment - I didn't get it

 

[dmva]

I didn't think it was too clear.

Here's the pattern I want to establish all the way down the page:

B7 needs to contain =eb!A4
C9 needs to contain =eb!B5
B10 needs to contain =eb!A8
C12 needs to contain =eb!B9
B13 needs to contain =eb!A12
C15 needs to contain =eb!B13
B16 needs to contain =eb!A16
C18 needs to contain =eb!B17
B19 needs to contain =eb!A20
C21 needs to contain =eb!B21

If I copy any multiples of these 3 cell ranges, the fill handle onl
increments the B number by one over the C number after that number o
multiples has been copied. In other words, if I highlight and dra
fill handle with B7:C12, then B13 is entered as =eb!A10, not =eb!A12.
If I highlight and drag fill handle with B7:C15, then B16 is entered a
=eb!A14, not =eb!A16.

I hope you can see what I am trying to accomplish.

Thanks for the help.

dmv

 

[Frank Kabel]

Hi
try the following formulas:
B7:
=OFFSET('eb'!$A$4,INT((ROW()-7)/3)*4,0)

C9:
=OFFSET('eb'!$B$5,INT((ROW()-9)/3)*4,0)

now select these cells and copy them

 

[dmva]

Frank -

Thank you for your help.

This does seem to work, but unfortunately, I won't be able to tell fo
sure until later today, after the information I am copying from on th
first sheet becomes available. At the moment, I am working with onl
one set of 3 cell ranges, and am taking my best shot at guessing wha
the increment needs to be.

That said, I wonder if you could explain your formulas a little more?
Especially since I might have to modify them in a hurry.

I looked at the syntax for the =offset in Help, and can see th
arguments you have for the reference (eb!$A$4 or eb!$B$5), and th
columns would of course be 0. But, I'm not sure I understand exactl
the part you have in there for the rows argument.

Perhaps I'm just having an early senior moment!

Again, I appreciate you help!

dmv

 

[Frank Kabel]

Hi
within the row argument i take the current row (ROW()) and subtract 7
(this is the row of your first cell: B7). I divide this result by 3 and
multply it with 4. Reason:
you insert this formula every third row (B7, B10, B13) and increment
the source cell row by 4

 

[dmva]

I think I've pretty well got it now.

Thanks again, Frank, for all your help.

dmv

 

[dmva]

I think I've pretty well got it now.

Thanks again, Frank, for all your help.

dmv