Ask Your Question
0

How to 'drag-copy' a formula to the same row and modify (only) the row parameter (and vice versa)?

asked 2015-01-30 11:49:44 +0200

Herbivore13 gravatar image

updated 2015-01-30 11:52:16 +0200

Hello,

I've got another question. I'm pretty sure someone has asked this before, but I have no idea how to google this problem.

First off, I wanna define what I mean by 'drag-copy': If you select a cell and click on that little square at the bottom right corner of the black rectangle and drag it somewhere, it copies the value/formula of the cell, probably modifying the formula's cell references.

I know the $ operator. What I sometimes wanna do, is to copy the formula from one cell to other cells in the same row and I want calc to modify the row parameter automatically (which it doesn't, because it's the same row; calc would only modify the column parameter).

Example: In A1 there is the formula = A2 + 42. Now I want to copy the value to the cells B1, C1, D1 and E1. The result will be for B1 = B2 + 42, for C2 = C2 + 42 and so on.

If I have in A1 = $A2 + 42, the results will be like B1 = $A2 + 42.

But what I actually want, is B1 = $A3 + 42, C1 = $A4 + 42 and so on.

Is there a way to achieve this? Pressing CTRL or SHIFT while copying doesn't seem to work. I've got the same question if you only want to change the column parameter when copying to the same column, but I assume it has the same solution.

I found a solution only for one situation: If you don't need to modify the cells formulas' after copying.

New example: In A1:A5 are some arbitrary values and I want the same values in A10:E10. Then I can use this formula in A10 = TRANSPOSE(A1:A5) and hit CTRL + SHIFT + RETURN to declare it as an array function. But this works only, if I don't want to - for example - change the formula in C10 after copying.

I am using Kubuntu 10.14 and LibreOffice Calc Version: 4.3.3.2.

Best Regards

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2015-01-31 04:42:20 +0200

Wildcard gravatar image

updated 2015-01-31 23:18:52 +0200

OFFSET would work but I think easiest is INDIRECT.

=INDIRECT(ADDRESS(COLUMN()+1,1))+42

Entered into A1 it gives A2+42. In B1 it gives A3+42.

I just used the COLUMN function to provide the row in the ADDRESS function and kept the column as a constant, 1 (meaning column A). I added 1 to the column function output before using it as row input to the address function, because you want the formula to give A 2 when put in the first column, not give A 1.

This could be extended to pretty much any scenario. Lets say you are going to put a formula in some random cell (AB7, we'll say), and the formula will include a reference to cell G45. You want it set up so that if you click and drag the formula over to AC7, AD7, and so on, that part of the formula referencing G45 will instead reference G46, then G47.

Here is how to reference G45 from AB7 so it will work as intended:

UPDATE: While working this out I discovered that the OFFSET function is actually the easiest way to do this, not the INDIRECT function. So the formula would actually look like:

=OFFSET($G$45,COLUMN()-COLUMN($AB$7),0)

To reference G45 from AB7 so that when copied to AB8 it will reference H45, use:

=OFFSET($G$45,0,ROW()-ROW($AB$7))

But if you want it to work in either direction, use:

=OFFSET($G$45,COLUMN()-COLUMN($AB$7),ROW()-ROW($AB$7))

When put into AB7 this references G45. Put in AC7 it references G46. Put in AB8 it references H45. Put in AC8 it references H46.

(If this answers your question, you can mark it as the answer—green checkmark.) :)

edit flag offensive delete link more

Comments

Thank for your suggestion. I don't fully understand it, yet. But since I'd like to have an arbitrary starting cell ('mother cell'), I want to ask you if you could generalize the formula for an arbitrary starting cell? That would be kind! I wasn't able to achieve this by myself.

This example here was an attempt of creating a minimal example to demonstrate my problem. :)

Herbivore13 gravatar imageHerbivore13 ( 2015-01-31 19:54:39 +0200 )edit

Yes, your updated answer solves my problem! Thank you very much! In This formula =OFFSET($G$45,COLUMN()-COLUMN($AB$7),0) you don't to type in $AB$7, apparently. Also $AB$42 would be correct. But that totally makes sense since you only need the column, not the specific row. I also prefer the $ operator, although one may not need it in every situation here.

Herbivore13 gravatar imageHerbivore13 ( 2015-02-02 13:16:47 +0200 )edit

What I'd like to remark for other people who are also trying to solve the same problem: Sometimes you have to change COLUMN()-COLUMN($AB$7) to `COLUMN($AB$7)-COLUMN(), depending on if you wanna copy from left to right or right to left and 'read input' from left to right or right to left. Same for rows and copying and input reading up and down.

I still prefer the TRANSPOSE solution if I only have to copy data and not modify them, though. I don't see any downside of this solution then.

Herbivore13 gravatar imageHerbivore13 ( 2015-02-02 13:19:30 +0200 )edit

@Herbivore : "Flipping" the difference of column numbers will change the direction of passing through the vertical source range. You may well start the polling at any element of that column arry. The original difference will then pass through the source downwards if you fill the formula rightwards - and upwards for cells filled with the formula leftwards. This coordination will invert if you flip the difference. TRANSPOSE cannot do that. Another downside: Inserting/deleting columns; scaling.

Lupp gravatar imageLupp ( 2015-02-02 15:03:26 +0200 )edit

Yes, you're right. I think, the suggested formulas are the ones that I or anybody else will need most of the time since reading rightwards and copying downwards is more common (I think).

And you're right again about the inserting/deleting issue with TRANSPOSE. I just haven't figured out yet, what you mean by a 'scaling issue' (?).

In my case, I've got an example here where I just want to copy values associated with months from a column to a row and then TRANSPOSE could be just enough.

Herbivore13 gravatar imageHerbivore13 ( 2015-02-03 02:37:58 +0200 )edit

I also tried to use that solution across two different sheets and it works fine! Two different sheets meaning referring to sheet1 and outputting data on sheet2.

Herbivore13 gravatar imageHerbivore13 ( 2015-02-04 11:20:48 +0200 )edit
0

answered 2015-01-30 13:50:26 +0200

karolus gravatar image

Maybe

=OFFSET(reference ; row_distance ; Column_distance ; [optional Row_size] ; [optional Column_size] )
edit flag offensive delete link more

Comments

The example under "But what I actualy want" should be covered then by

=OFFSET($A2;COLUMN(A1)-COLUMN($A1);0)+42
Lupp gravatar imageLupp ( 2015-01-30 14:24:48 +0200 )edit

Makes COLUMN($A... any sense ? why not simply 1

karolus gravatar imagekarolus ( 2015-01-30 14:40:46 +0200 )edit

@karolus - You are completely right, of course. As this will not have been my first post you noticed, you also will be aware of the fact that I sometimes do things in a somewhat different way - and sometimes in error state. In this case I tried to implicitly hint how the formula should be adapted for usage starting with another "mother cell". It's the way I do it in my own sheets, at least if the formulae are a bit more complex. Altersstarrsinn halt! Nachsicht bitte?

Lupp gravatar imageLupp ( 2015-01-30 14:55:40 +0200 )edit

First off, it seems that there is no magic key combination that I can press during copying or another operator like & that means 'change the row reference although you're copying in the same row'. That makes it more complicated and hence, TRANSPOSE seems to be the best solution if I only want to output the cell values and not modify them.

Thank you for your suggestion, it works for me! I prefer the example from Lupp, because it is a general solution with an arbitrary 'mother cell' :).

Herbivore13 gravatar imageHerbivore13 ( 2015-01-31 19:52:09 +0200 )edit

@Herbivore - Don't seek for magic, please. As I see it, we need an uncluttered toolbox of reliable implements designed for efficient use in clearly structured solutions! The OFFSET function is a tool I wouldn't forgo. "Shortcut spells" often are rather confusiung than reliable. And software trying to guess my intentions is driving me crazy.

Lupp gravatar imageLupp ( 2015-01-31 22:55:50 +0200 )edit

Hehe, well, actually, I was hoping for another operator like $. Key combinations can be a great and a pain in the * at the same time. Honestly, if I use Calc, it still feels more like playing around a little bit. For any serious stuff I wanna do, I think I'd prefer programming it in a proper programming language.

This answer is also correct, but since I prefer Wildcard's solution and since I can only mark one as an answer, I picked Wildcard's answer as a correct answer.

Herbivore13 gravatar imageHerbivore13 ( 2015-02-02 13:22:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-01-30 11:49:44 +0200

Seen: 12,323 times

Last updated: Jan 31 '15