Apply Column formula for specific range?

Oemer Faruk Aslantas

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

Andrée Starå

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!

Bassam Khalil

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.

Andrée Starå

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!

Bassam Khalil

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.