Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 848
  • Last Modified:

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.
0
freshgrill
Asked:
freshgrill
  • 5
  • 3
1 Solution
 
nutschCommented:
assuming you start in your summary sheet in cell A2, you could use

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

and copy to the right

0
 
nutschCommented:
You could also use the offset function, like

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

Thomas
0
 
freshgrillAuthor Commented:
nutsh: I don't get what is the 22 & 8 in the formula?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
freshgrillAuthor Commented:
The offset doesn't work, it just show the same formula dragged to the right with no changes in formula or value.
0
 
nutschCommented:
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
 
nutschCommented:
The formula is the same, the value will be different. Promised. Check out the screencast

nutsch-436889.flv
0
 
freshgrillAuthor Commented:
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
 
nutschCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now