Creating a copiable range address in libreoffice calc 7.5

English
#1

I have a table of data running about 686 rows. On the right most column I have some row numbers arrived at using Aggregate function. I want to enter a formula in the column next to it (i.e. in the new column). The formu;a will use the previous column’s row number and create a range address for matching the values of the first column within the range starting from the row following the row number arrived at based on Aggregate fuction in the previous column to the row previous to the row where the formula is being entered. I then want to copy the formula to all the rows. The formula should therefore have the relative address of the row previous to the row where ever the formula is copied. I have tried using the indirect function, but it expects me to put a string at the end of the previous row address also making it a string and the to address in the range in all copied rows therefore remains unchanged. (For example in row 16, if I have in W16 the value of the Aggregate function, in column X, I have to writre the function as Indirect("A"&W16+1&":$A15") I tried reversing the address ( with higher value of the row coming first followed by the lower value of the row at the end (i.e., instead of indirect("A"&W16+1 &":$A15"), I tried indirect($A15&":A:"&w16+1). But this is not working. I also tried Indirect("A"&W16&":"&$A15). I was not working. With Address function put inside an indirect function, I am not sure whether I will be able to create a range address which will change address as per the row in which it is copied. Is there any other way or is there anyother function which I am missing.

esbee51

#3

Make yourself familiar with absolte, mixed and relative references. Spreadsheet essential since Visicalc of 1979.

#4

I don’t know your abilities, but as you already “construct” the first part of the range by concatenation "A"&W16+1 you can do the same for the second part like for example &":$A"&(15+ROW())

#5

Thank you. I did n’t think of this possibility as “:”&A15 was not working. I have constructed a range using “:$A”&(Row()-1) as required by me. The Row() has changed the picture I think. Thank you once again.

1 Like
#7

Thank you for the suggestion. I am already familiar with the absolute, relative and mixed references. I was having a problem with ‘constructing’ a changing range reference with regard to the value generated by the ‘Aggregate’ function and the rows (when the range reference should also change when I copy the formula down in additional rows)

#8

Sorry. Finally, I got your question after the second reading. I find the OFFSET function easier to calculate with.

OFFSET(ref ; rows_off ; cols_off ; resize_rows ; resize_cols)

ref is a reference to a cell or range (absolute, mixed or relative).
rows/cols is a positive, negative or zero offset number.
resize_rows/cols is an optional positive number.

#9

Thank you. I tried Offset also. But I am yet to understand it fully. Particularly, the rerence part and the row and col resizing at the end. I think I need more examples to understand it. Nevertheless, I am not sure whether it will generate a variable range – range with different lengths. One suggestion to use row() in the fomula with indirect was useful in solving my problem.

#10

OFFSET(A1;1;2) from A1 one down, two to the right is C2
OFFSET(A1;1;2;3;4) from A1 one down, two to the right, 3 rows tall, 4 columns wide is C2:F4

#2

Sorry for the typos. I am getting old, I think.

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