Apply column formula to row

English
#1

Hi,

To save a lot of time and tedious work, I have a formula I want to transpose from column to row, but it doesn’t seem to work like expected.

The formula is as follows (translated to English, so not sure if the function names are correct):
=SUM(INDIRECT(CONCATENATE($A2 & "1"));INDIRECT(CONCATENATE($B2 & "1"));INDIRECT(CONCATENATE($C2 & "1"));

So when I copy this formula and paste it in the same column, the formula behaves as expected. I get the following result:
=SUM(INDIRECT(CONCATENATE($A3 & "1"));INDIRECT(CONCATENATE($B3 & "1"));INDIRECT(CONCATENATE($C3 & "1"));

Instead of having this formula in the column, I want to have this exact format in a row. So for the whole row, I want to have it like [Column + (Rownumber+1)].

I looked at the transpose function, but this only transposes the matrix it seems, which is not what I want.

An example document I’ve created:
TransposeFunctionExample.ods (17.9 KB)

In this document I want to have the formula spanning variable A to C in week 1, to span over week 1 to 3 instead.

Maybe there is some function in LO which I have overlooked that is able to do what I want?

#2

It seems to me (this is just my opinion, maybe I’m wrong) that you have chosen a too complicated path for solving a not very difficult task. What about something like
=SUMPRODUCT(INDIRECT(A2:G2&1))
and
=SUMPRODUCT(INDIRECT(A2:A4&1))?

#3

A range would have been easier indeed. However, in my original document I have every month in a separate sheet. E.g., for some weeks I need to retrieve values from the month January and February. Unfortunately, this resulted into a convoluted way of summing the values…

I’m open to suggestions for changing the whole structure of the document!

#4

No, I understand what caused this particular method of calculation. I understand that in the example file, the addresses of the target cells are in a row januari.$F$1:$L$1, januari.$M$1:$S$1 and so on. In a real table, this could be januari.$M$1, januari.$S$1, januari.$AA$1, januari.$CD$1… My formulas are designed to make it easier to access the already existing parts of the addresses on the Blad1 sheet - here the link texts go in a sequance, so you do not need to specify each of the cells separately in the formula - INDIRECT($A2 & "1");INDIRECT($B2 & "1")...
(By the way, a construction like CONCATENATE($A2 & "1") is redundant - write either CONCATENATE($A2;"1")) or just $A2 & "1", this is the same thing, the ampersand symbol is the concatenation operation.)

#5

That is right… Right now I’m just pasting them in the same column and then cut and paste the formulas in the right spot. :skull:

#6

If I understand your objective, and if the data table is consistently one column per day, you can do this without the indirection. Restructuring not needed.

In row 11, insert numbers only, no “Week …”.
Format the row to prefix numbers with “Week” if required.
In cell B12, insert the following formula:
=SUM(OFFSET($januari.$E$1;MATCH($A12;$januari.$E$1:$E$3;0)-1;7*(B$11-1)+1;1;7))

That formula can be copied across your summation grid.
Your sample file with suggested edits: Suggestion.ods (13.6 KB)

With some restructuring, a pivot table can also be utilized. That is in many ways a more robust construct, but it does not update “live” in the spreadsheet. (Requires a couple of mouse clicks to update.)

#7

I still need to look at how your formula works, but looking at the values in the weeks of variable C (B:D$14), this doesn’t seem to get the right results. There should be no products in the formula.

To better illustrate, I have “Blad1” which contains the summary of the monthly/weekly values. It’s the main page if you will.
Then I have a separate sheet for every month. Each month contains values I need to extract. Every month sheet has values for every day in that month.

On “Blad1” I want to display values for every week in the year. The problem is that not every can be sequentially retrieved. Say, week 22 in 2022 spans from (30th of May - 5th of June), so that means I need to retrieve values from 2 columns in the sheet May, and 5 columns in the sheet June. I need to total sum of these 7 days.

It’s hard to say which columns I need beforehand, cause every year the dates in a weeknumber change. Therefore, I dynamically calculate which dates are in week X in year Y.

Anyhow, I think cutting and pasting would cost me less time, than figure out a formula that gets the right values. Thanks for trying to help!

#8

Breaking up the source data in “artificial” sections (like months) is usually not a good idea. It is perhaps advisable to restructure after all.

Better keep data in a single table and use filter/extraction tools and lookup/conditional functions to get the information you want. With that, you can have any sectioning you like in the output, and all at once.

#9

With this organization of data, you will not only get convenience in processing them, but you will also be able to use the Form to enter new daily values.

Moreover, when you come to the conclusion that the database is better suited for your purposes (it really is, you just don’t know about it yet), it will be much easier to transfer data from one general table to the database than to collect data from separate sheets .

1 Like
#10

I definitely agree that having all this in one sheet would be way easier. However, my team consists of 8 people, which each have 10 variables. For those 10 variables, I need values for the entire year. So 365 * 10 * 8 cells, that need to be processed. Finding the right data in one sheet would be very cumbersome and it is super laggy, especially when making adjustments to multiple formulas/cells, making the document unusable (I’ve tried this before).

#11

Yes! Would be a great idea. I’d rather program what I need in a window application than do this in Excel/LO. I’m pretty limited to use only LO, unless I request permission from my company to install my own application and manage a database (which is not really necessary as I could save the data in csv format locally). This document was just to make administration easier with the tool I have at hand. I appreciate the advice!

#12

I understand your difficulty. I have good news for you. You do not need to install anything else, the existing LO is more than enough for you. Now I should link to the manual for using LibreOffice Base. But I understand that having seen almost half a thousand pages of text there, you will not soon decide to read it all. So I will give you another link. I think that over the weekend you will have time to look through enough to completely redo your project on Monday.

1 Like
#13

Ah, I didn’t know you can create a database with LO! This is immensely useful.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.