Excel 2011: Filling Down Columns

How do I fill a column down (drag and fill) using data that is spread across the spreadsheet?

The only way I can currently accomplish this is manually changing the column letters after I've done the drag and fill down ... which takes an eternity for the spreadsheet I'm working with.

It would seem there would have to be an easier way. Please see the attached spreadsheet for a little more clarification.

Thank you! Fill-Down-for-Data-Across.xlsx
Tim JackoboiceOwnerAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

if you enter the formula =A1 into cell A2 and then drag down, it will turn into =A2, =A3, etc.

But if you want to show the contents of A1, B1, C1, etc when dragging down, you can use this formula in A2 instead

=INDEX($1:$1,ROW(A1))

Drag it down and it will show the contents of row 1 across. See attached.

That principle can be applied in more complex formulas, too. Is that what you're after?

cheers, teylyn


Book1.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
Try this formula

=SUM(OFFSET($E$2,0,0,1,ROW()-ROW($A$2)+1))
0
 
Tim JackoboiceOwnerAuthor Commented:
Thanks, ssaqibh. That will work for my example, but I should have been more clear in my question.

Is there a way that you can hold down keys (for example shift+control, or alt+shift+windows) or something similar, so that whatever formula you have in a cell that you need to drag and fill down a column, will work referencing data spread across the spreadsheet?

Sorry I wasn't more clear -- my fault. (But I can also use your example for other future calculations.)

Thanks.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
Saqib Husain, SyedEngineerCommented:
You cannot drag a formula down to make the references move left or vice versa.
0
 
softpro2kCommented:
Good discussion.
0
 
Tim JackoboiceOwnerAuthor Commented:
teylyn:

Sorry for the delay accepting your answer as a solution ... I must never have hit the submit button when I entered it before.

The solution you provided was exactly what I was looking for. This will save me a huge amount of time, and I appreciate your help!

Thanks again.
0
All Courses

From novice to tech pro — start learning today.