How to convert a formula on a column formula by doing absolute reference
Hi,
I would like to do some calculation on the same sheet that I am recolting data,
I have a recolting data culumns : Culumn 2 and i would like to multiply the Culumn 2 with a fix value on Column 3
I am using a simple formula Culumn 4 =([Colonne2]@row * [Colonne3]@row) , how to fix the culumn3 value and
convert it on a culumn formula in culumn 4
Best Answer

Hi @Otman
Thank you for clarifying!
Then yes, you can lock your formula to that one cell using absolute references or the $ sign, like so:
=[Colonne2]@row * $[Colonne3]$1
This will keep the formula locked that cell. (See: Create a Cell or Column Reference in a Formula)
However you will not be able to make this a Column Formula because of the $. Instead, you can dragfill the formula down the entire column. The formula will autopopulate as new rows are added, as long as the new rows are added directly beneath two rows of data with the formula. See: Use or Override Automatic Formatting and Formula Autofill
Cheers,
Genevieve
Answers

Hi @Otman
Instead of having the value listed in Column 3, what about typing it into the formula itself?
ex:
=[Colonne2]@row * 10
That way the formula will always have the same value fixed.
Would this work?
Cheers,
Genevieve

Hi Genevieve,
Thank you for your answer, yes definitely that will work, and this
is how it is actually mounted in my Smart sheet. However, the values in my Column
3 are included in many different calculations and they are supposed to be changed
many times. I would like to make it automatically, by changing it in column 3
it will changes automatically in all the calculation concerned. Others wise I
have to do it each time manually, and this is what I am trying to avoid, due to
the volume of information’s received.
Thank you very much for your answer, please advice on how I can
resolve this problematic.
Best regards

Hi @Otman
Thank you for clarifying!
Then yes, you can lock your formula to that one cell using absolute references or the $ sign, like so:
=[Colonne2]@row * $[Colonne3]$1
This will keep the formula locked that cell. (See: Create a Cell or Column Reference in a Formula)
However you will not be able to make this a Column Formula because of the $. Instead, you can dragfill the formula down the entire column. The formula will autopopulate as new rows are added, as long as the new rows are added directly beneath two rows of data with the formula. See: Use or Override Automatic Formatting and Formula Autofill
Cheers,
Genevieve

Hi Genevieve,
Thank you for your answer, that will work; I am sincerely grateful
for your help.
Best regards
Help Article Resources
Categories
Check out the Formula Handbook template!