Drag cells down but auto fill across

[Vigilance]

I would like to have my spread sheet increment across (So using the letters) instead of down. To clarify, I need it such that, as I drag down to have calc auto fill in cells, I need it to increment across the cells in the formula.

So if I have a cell at A1 and it has =2*B2
I want to drag the cell down all the way to A26, and have each cell say =2*C2 =2*D2 =2*E2

I’ve tried putting a $ in front of the number when I drag the column down, but that doesn’t do it. Being able to have it auto increment across while I drag down wouldnot only save me a LOT of time for my current project, but would also help to make my project readable and user friendly.

[EarnestAl]

What does =2B2 mean? Does it mean =B2*2?

Does this work? =INDIRECT(ADDRESS(2;COLUMN()+ROW();4;;))*2

[Vigilance]

Oh my gosh, yeah I’m so sorry. I meant to have it say t*B2, but it looks like the site ate the asterisk and used it for formatting.

[JohnSUN]

Besides the solution that @EarnestAl suggested in his comment =INDIRECT(ADDRESS(2;COLUMN()+ROW();4;;))*2, you can use other formulas as well. For instance,

=OFFSET($B$2;0;ROW()-1)*2 or

=INDEX($B$2:$AA$2;ROW())*2 or

=LOOKUP(ROW();ROW($A$1:$A$50);$B$2:$AA$2)*2

Or (if you know that last cell with data in second row is AA2) just type in cell A1 =TRANSPOSE(B2:AA2)*2 and press Ctrl+Shift+Enter

Formulas can be simpler or more complex, depending on the problem you are trying to solve.

It should be noted that with certain skills and knowledge of hotkeys, many tasks can be solved without formulas at all. For example, your task is solved by a dozen keystrokes on the keyboard.

If the fixed values do not suit you, but you need exactly references to the source cells in the form of formulas - no problem! Just add Alt-L in the right place - this will enable the Link option and you will get the formulas.

[Vigilance]

Understood. I guess I’m a bit surprised there is no clean way to do this. I’m not sure if excel has what I want or not, and I don’t know if other people want it, but it would be a nice feature.

Thank you for your help as well.

[Vigilance]

Oh wow, thank you for that really indepth edit! I really appreciate all the extra info!

[EarnestAl]

If the question is answered then please click the grey tick beside the answer to turn it green. This will help others with the same question. Cheers, Al