Solved

# Excel Auto increment drag left add cell like dragging down

Posted on 2011-03-25
Medium Priority
852 Views
In Excel 2010,

I can drag down and have it auto increment: I have references to another sheet, the data is in vertical columns but the summary sheet with the references need it horizontal.

DATA Sheet:

1: h23
2: h24
3: h25

Summary sheet: ='data'!h23 |'data'!H24|'data'\$h25

I would like to create the first one ('data'!h23), highlight and drag right automatically filling in the cells to the right.
0
Question by:freshgrill
• 5
• 3

LVL 39

Expert Comment

ID: 35217099
assuming you start in your summary sheet in cell A2, you could use

and copy to the right

0

LVL 39

Expert Comment

ID: 35217111
You could also use the offset function, like

=OFFSET(Data!\$H\$23,COLUMN()-1,)

Thomas
0

Author Comment

ID: 35217119
nutsh: I don't get what is the 22 & 8 in the formula?
0

Author Comment

ID: 35217149
The offset doesn't work, it just show the same formula dragged to the right with no changes in formula or value.
0

LVL 39

Expert Comment

ID: 35217155
8 is column H (A=1, B=2... H=8) ...to get the column of the destination
22+column() =23 in column A, 24 in column B to get the row of the destination

0

LVL 39

Expert Comment

ID: 35217175
The formula is the same, the value will be different. Promised. Check out the screencast

nutsch-436889.flv
0

Author Comment

ID: 35217279
nutsch: Okay, I created 2 new sheets and did the same thing and it worked. But when do it on the real sheets it doesn't work. I think it maybe the column I start with. I Start with Column C on the summary sheet, does that have any effect?
0

LVL 39

Accepted Solution

nutsch earned 2000 total points
ID: 35217311
yes it does. the column()-1 parameter starts the offset at 0 in column a but 2 in column C

try this instead and update \$C1 to whatever cell you're going to start the formula in.

=OFFSET(Data!\$H\$23,COLUMN()-column(\$C1),)

Thanks,

Thomas
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.