excel help: copying relationships??

G

Gordon Gekko

say in A1 i have =b10
say in A2 i have =b20

now say i want A3 to be =b30, A4 to be =b40 and so on.

how can i copy and paste this and get excel to realize i want to add multiples of 10??

thanks!!!
 
GG

select both cells by dragging your cursor over them so they are both greyed

Move your cursor over the little square shape in the lower right of the greyed frame until you get a solid cross cursor. Left click and hold down on the cross dragging it down to the cells you want.

The selection A1 A2 now increments the numeric portion to all cells downward by 10 as you drag the frame.
 
Quote from stu:

GG

select both cells by dragging your cursor over them so they are both greyed

Move your cursor over the little square shape in the lower right of the greyed frame until you get a solid cross cursor. Left click and hold down on the cross dragging it down to the cells you want.

The selection A1 A2 now increments the numeric portion to all cells downward by 10 as you drag the frame.
More...
stu,

i think i know what you mean, but when i do that, this is what happens.

i made a dummy blank worksheet with exactly the setup i described.

in A1 i have =b10
in A2 i have =b20

i left click in A1, drag to A2, let go of mouse button. now A1 and A2 are selected.

i left click on the tiny black square on the bottom right corner, drag it down to A3 and let go of the button.

in A3 i get =b12

what am i doing wrong? i would like to get =b30

thanks
 
GG:

Excel copies and fills based on the series of values you are dragging lower - unfortunately, when there is a refernence instead of a value it uses relative referencing - it doesn't look at the cell references it just increases the row based on the distance you are filling from the original row.

Here's the way I'd solve you problem -- it is a bit more advanced but it will do what you want:

in A1 instead of =b10 type : indirect(address(10*Row(A1),2)

this means ... look at the cell represented by the address in column 2, this row *10

same logic only if you drag this down it will skip 10 rows each time.


PM me if you ever need more Excel help -- I used to be frickin guru but now I'm outa practice.

:cool:
 
Quote from Gordon Gekko:


i made a dummy blank worksheet with exactly the setup i described.

in A1 i have =b10
in A2 i have =b20

i left click in A1, drag to A2, let go of mouse button. now A1 and A2 are selected.

i left click on the tiny black square on the bottom right corner, drag it down to A3 and let go of the button.

in A3 i get =b12

what am i doing wrong? i would like to get =b30

thanks
More...
stu's method works. What you have done is clicked on the bottom right corner of A1 (solid cross cursor) in order to drag it to "grey" A1:A2. I bet the value in A2 has changed from b20 to b11 even before the second drag. Hence your b12 in A3.

What you need to do is to left click on the middle of cell A1 and drag to "grey" A1:A2. (Note that the value in A2 is still b20). Then left click on the solid cross cursor on the bottom right corner of A2 and drag it to wherever you wish. Works like a f#@%ing charm.
 
Quote from Mr Subliminal:

stu's method works. What you have done is clicked on the bottom right corner of A1 (solid cross cursor) in order to drag it to "grey" A1:A2. I bet the value in A2 has changed from b20 to b11 even before the second drag. Hence your b12 in A3.

What you need to do is to left click on the middle of cell A1 and drag to "grey" A1:A2. (Note that the value in A2 is still b20). Then left click on the solid cross cursor on the bottom right corner of A2 and drag it to wherever you wish. Works like a f#@%ing charm.
More...
i still can't get this method to work.

this is exactly what i'm doing:

in A1 i have =b10
in A2 i have =b20

i left click in the middle of A1, hold down the button, drag down to the middle of A2, release mouse button. there is now a black line around both these cells.

on the bottom right corner of this outline, there is a little black square. i move the pointer over it until it turns into a black plus sign. then i left click on it, hold down the button, drag down to A3, release the button.

i get =b12 in A3.

:confused:
 
Quote from Gordon Gekko:


i still can't get this method to work.

this is exactly what i'm doing:

in A1 i have =b10
in A2 i have =b20

i left click in the middle of A1, hold down the button, drag down to the middle of A2, release mouse button. there is now a black line around both these cells.
More...
Stop right there. At this juncture, what is in A2?

Edit : Also I'm assuming b10 is a character string which shows up as 'b10 in the Formula Bar.

Final Edit : Just reread your first post and noticed the "=b10". I incorrectly assumed a character string "b10" and not a cell reference. I agree that in your case, stu's method doesn't work. I apologize for adding to the confusion.
 
Waaaay too complicated. Gordon, do this.

Using the keyboard, go to the top cell. Hold down shift and arrow down to the second cell then let go, now both are highlighted. Now Mouse up to the lil square in the bottom right of the lower cell, left click and hold while dragging down...the mouseover text will show you advancing numbers as you drag down before letting go - to know you've done it right. Let go when you're far enough down.

Let me know how it goes.
 
You must log in or register to reply here.
Back
Top