How to convert my formulas to a column formula

jgneely72151

How do I convert these formulas to a column formula?

So the formulas work perfectly; however, I find myself constantly updating the source sheet when someone inserts or enters new records and then I have to go to my metrics sheet to make sure they are applied. I'm doing way too much manually work with these sheets and I want the sheets to work for me.

Below are the formulas that I have in specific columns on each of my source sheets at the row level and these same columns are updating my metrics sheet by totoal count from all of my sheets (not listed here but just wanted to explain a little more):

1. CLEARED ON TIME

=IF([# Days between date cleared & rotation start date]1 > -1, "✔", "❌")

2. # DAYS TO CLEAR STUDENT FROM START TO FINISH

=NETDAYS([Credentialing Started]1, [Date Cleared]1) - 1

3. # DAYS BETWEEN DATE CLEARED & ROTATION DATE

=NETDAYS([Date Cleared]1, [Rotation Start Date]1) + -1 

What am I doing wrong? 🤔

Andrée Starå

Hi @jgneely72151

Try something like this. (a row formula can't have specific row references)

1. CLEARED ON TIME

=IF([# Days between date cleared & rotation start date]@row > -1, "✔", "❌")

2. # DAYS TO CLEAR STUDENT FROM START TO FINISH

=NETDAYS([Credentialing Started]@row, [Date Cleared]@row) - 1

3. # DAYS BETWEEN DATE CLEARED & ROTATION DATE

=NETDAYS([Date Cleared]@row, [Rotation Start Date]@row) + -1 

Did that work/help?

I hope that helps!

Be safe and have a fantastic day!

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!

Andrée Starå

Hi @jgneely72151

Try something like this. (a row formula can't have specific row references)

1. CLEARED ON TIME

=IF([# Days between date cleared & rotation start date]@row > -1, "✔", "❌")

2. # DAYS TO CLEAR STUDENT FROM START TO FINISH

=NETDAYS([Credentialing Started]@row, [Date Cleared]@row) - 1

3. # DAYS BETWEEN DATE CLEARED & ROTATION DATE

=NETDAYS([Date Cleared]@row, [Rotation Start Date]@row) + -1 

Did that work/help?

I hope that helps!

Be safe and have a fantastic day!

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!

jgneely72151

Oh, my! You mean it was that simple, to just remove the specific row reference? Oh my goodness! Thank you so much and this column feature is going to make life so much easier. Thank you so very much! 🙏

Andrée Starå

@jgneely72151

Easy to miss!

You're more than welcome!

Yes, I agree, but keep in mind that it's all or nothing, so you'll have to think about everything in the column.

jgneely72151

Oh, yeah, figured that out rather quickly 🤦 because my primary column does have formulas listed. I will be revising that real soon. Again, thanks!