Dynamic column reference

rushfis

I have a smartsheet setup to act as a daily tracker based on working day with statuses of the activity getting updated using a formula.

The formula I am using currently is: 

IF(AND((ISBLANK([WD13]@row)), NOT(OR(SubStatus@row = "Completed", SubStatus@row = "Not scheduled to start yet"))), "Daily update pending", IF(AND((NOT(ISBLANK([WD13]@row))), (SubStatus@row) = "Select"), "Daily update pending", IF(AND(([End Date]@row < TODAY()), (NOT(OR(SubStatus@row = "Completed", SubStatus@row = "Not scheduled to start yet")))), "Delayed", SubStatus@row))), 

I understand that the formula Networkday can be used to calculate the working day number.

i.e. =Networkday(Aug 1, 2023 , Today()) will return 5 on Aug 7 & 13 on Aug 17, etc.

What I need help with is:

How do I use this number to shift the WD13 in the formula above to 14, 15 each day automatically everyday?

IF(AND((ISBLANK([WD+NETWORKDAY([Start Date]4, TODAY())]@row)), NOT(OR(SubStatus@row = "Completed", SubStatus@row = "Not scheduled to start yet"))), "Daily update pending", IF(AND((NOT(ISBLANK([WD13]@row))), (SubStatus@row) = "Select"), "Daily update pending", IF(AND(([End Date]@row < TODAY()), (NOT(OR(SubStatus@row = "Completed", SubStatus@row = "Not scheduled to start yet")))), "Delayed", SubStatus@row)))

Something like this does not work...

Genevieve P.

Hi @rushfis

If I'm understanding you correctly, you have multiple columns set up with WD before the day number and you're looking to have a formula automatically shift from one column to the next depending on today's date, is that right?

There currently isn't a way to dynamically reference columns within a formula to change the reference as you're describing. Please feel free to add your vote and voice to this related Product Idea thread, Column Reference, or create your own, new idea.

There may be a way to reference the entire row, and then use INDEX to identify what column number you want to reference. Here's information about the INDEX function if that helps.

Without seeing your set-up it's hard to know what a good alternative would be... I would potentially suggest one "Date" column with the dates populated down versus multiple, individual columns. There would be ways to identify each day if they're each individual dates in rows down a column.

Cheers,

Genevieve