How to convert a formula on a column formula by doing absolute reference

Options

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

Answers

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Otman
    Otman ✭✭
    Options

    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  

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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 drag-fill the formula down the entire column. The formula will auto-populate 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Otman
    Otman ✭✭
    Options

    Hi Genevieve,

    Thank you for your answer, that will work; I am sincerely grateful

    for your help.

    Best regards

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!