Extend a formula down a column with data in a row
Olivier
-
PHILOU10120 Posted messages 6463 Registration date Status Contributeur Last intervention -
PHILOU10120 Posted messages 6463 Registration date Status Contributeur Last intervention -
Hello,
In my spreadsheet, I have a database that allows me to perform calculations (in the same file).
So, in another table, I have a formula based on this database with data in rows, but my results are in columns. To avoid copying the formula, I would like to pull it down (in the column) with the formula extending into the rows, shifting by one cell each time.
I've been searching but I can't find this method. Does it exist, and if so, how can I do it, please?
Thank you for your help.
Olivier
Configuration: Windows 7 / Chrome 55.0.2883.87
In my spreadsheet, I have a database that allows me to perform calculations (in the same file).
So, in another table, I have a formula based on this database with data in rows, but my results are in columns. To avoid copying the formula, I would like to pull it down (in the column) with the formula extending into the rows, shifting by one cell each time.
I've been searching but I can't find this method. Does it exist, and if so, how can I do it, please?
Thank you for your help.
Olivier
Configuration: Windows 7 / Chrome 55.0.2883.87
1 réponse
Hello Olivier
Example with the ADDRESS function( row;column;3;1;)
=ADDRESS(6;ROW()-1;3;1;)
I retrieve the info from row 6
column the value of the row-1 if I am in row 2 it gives 1
A6
If you want the value contained in A6
=INDIRECT(ADDRESS(6;ROW()-1;3;1;);1)
If you copy the formula down
B6, then C6 etc...
--
It's by forging that one becomes a blacksmith. - It's at the foot of the wall that one sees the mason - we always learn from our mistakes.
Example with the ADDRESS function( row;column;3;1;)
=ADDRESS(6;ROW()-1;3;1;)
I retrieve the info from row 6
column the value of the row-1 if I am in row 2 it gives 1
A6
If you want the value contained in A6
=INDIRECT(ADDRESS(6;ROW()-1;3;1;);1)
If you copy the formula down
B6, then C6 etc...
--
It's by forging that one becomes a blacksmith. - It's at the foot of the wall that one sees the mason - we always learn from our mistakes.