Apply Column formula for specific range?

Hi community,

sry for spamming you these days but there is a task which is challenging me these days. We have made some changes in our formula all over the sheet (about 130 unique columns, I guess 80 of them with column formula). I am trying to find out a way to apply a column formula only on the "new" values, so that I can fix the historic entries. Is there a way to do this?


My thoughts:

  1. Try a COLLECT(xxx.xxx, with a date as criteria) ---> this would cause a lot of new rows and I would reach the 500K cell limit even faster. Could do the same with IF/ELSE I guess. Problem here: 1. Column (old formula), 2. Column (new formula) 3. Column (IF/ELSE or COLLECT with date as criteria) ---> creates me 2 new rows for each new formula.
  2. Same as above with IF(Created date > 02/02/2021, then....NEW Formula....ELSE... OLD FORMULA) Problem here: I reached yesterday some character limits per cell. I guess more than a A4 page is not possible. I haven't counted them but I already have some formula where I cut the part, created a help column to make it work

Is there a way to get this leaner?


Thanks in Advance!

Best regards

Ömer

Best Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Oemer Faruk Aslantas

    I hope you're well and safe!

    If there is something unique on each of the rows you want to change the column formula for, you can add that as a condition or add a so-called helper column to indicate which rows should be updated.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/11/21 Answer ✓

    Hi @Oemer Faruk Aslantas

    Hope you are fine, you can't apply the column formula for a specific range. i suggest that you save your current sheet as new and keep the old one as an archive and apply your column formula on the new sheet for new entries.

    and as you know for reports you can read the data from the 2 sheets.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Oemer Faruk Aslantas

    I hope you're well and safe!

    If there is something unique on each of the rows you want to change the column formula for, you can add that as a condition or add a so-called helper column to indicate which rows should be updated.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/11/21 Answer ✓

    Hi @Oemer Faruk Aslantas

    Hope you are fine, you can't apply the column formula for a specific range. i suggest that you save your current sheet as new and keep the old one as an archive and apply your column formula on the new sheet for new entries.

    and as you know for reports you can read the data from the 2 sheets.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!