Ask Your Question
0

Drag cells down but auto fill across

asked 2021-03-02 01:49:51 +0200

Vigilance gravatar image

updated 2021-03-02 06:26:50 +0200

JohnSUN gravatar image

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.

edit retag flag offensive close merge delete

Comments

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

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

Earnest Al gravatar imageEarnest Al ( 2021-03-02 02:52:28 +0200 )edit

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.

Vigilance gravatar imageVigilance ( 2021-03-02 03:14:04 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2021-03-02 06:47:30 +0200

JohnSUN gravatar image

updated 2021-03-03 06:31:01 +0200

Besides the solution that @Earnest Al 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.

Trans2.gif

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.

edit flag offensive delete link more

Comments

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 gravatar imageVigilance ( 2021-03-02 18:22:19 +0200 )edit

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

Vigilance gravatar imageVigilance ( 2021-03-03 19:33:33 +0200 )edit

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

Earnest Al gravatar imageEarnest Al ( 2021-03-03 22:09:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-03-02 01:49:51 +0200

Seen: 48 times

Last updated: Mar 03