asked on

Excel Auto increment drag left add cell like dragging down

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.

nutsch

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

=INDIRECT(ADDRESS(22+COLUMN(),8,,,"Data"))

and copy to the right

nutsch

You could also use the offset function, like

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

Thomas

freshgrill

ASKER

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

freshgrill

ASKER

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

nutsch

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

nutsch

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

nutsch-436889.flv

freshgrill

ASKER

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?

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial