How can I edit the same formula used on multiple rows?

Chris Shields

I tried to find the answer but didn't have any luck, so I'm hoping that someone can help me, with either advice or a helpful link.

I've created a materials shipping and receiving form. In the first line, when i created the template, one of the columns is a "health" indicator, called "Symbol" using an RYGB stoplight dropdown. The next column, "Status" translates those with the following formula:

=IF(Symbol1 = "Green", "Received", IF(Symbol1 = "Yellow", "Delayed", IF(Symbol1 = "Red", "Canceled", IF(Symbol1 = "Blue", "In Transit"))))

Every new row that I added automatically carried the formula with it and updated the instance. So the next row uses Symbol2. The row after that, Symbol3, etc.

But now (of course) I need to make a change to the formula to

track returns. If I make a change to the original formula in Row 1, the changes do not trickle down to the other instances of the formula. Is there a way to apply my formula so that if I ever need to make a change, the change happens for all rows?

Thank you,

Chris

PS: I'm open to other suggestions to track progress, outside of the methods I'm already using, if it lends itself to edits easier as we evolve my system,

Chris Shields

Your probably right. I'm guessing a user error due to lack of coffee and trying to do this first thing.

What i ended up doing is using deleting that column, and creating a drop down list ("ordered", "In transit", "delivered", etc), then created conditional formatting rules to change the color of the cell. Works pretty well, and will allow me to make changes are our needs grow. I think I'm going to clone this sheet to create a variation for our deliverable to track their outbound freighting schedule.

Appreciate the comments.

Paul Newcome

There is no way to make this happen automatically. Dragfilling the remaining rows is going to be your fastest option.

Chris Shields

I as afraid of that. Luckily, I didn't get too far- something like 20 rows. I'm researching alternative methods using drop-down menus and conditional formatting to change the colors of the box (vs using the RYGB). The jury is still out (because I'm still learning) but if it works, it might be better in the long run. Not just for editing, but for adding additional information that I might have not yet predicted needing.

Paul Newcome

Dragfilling should only take a few seconds for even one hundred rows. It doesn't seem like dragfilling should be as much of a hassle as it seems to be causing you.

Chris Shields

Your probably right. I'm guessing a user error due to lack of coffee and trying to do this first thing.

What i ended up doing is using deleting that column, and creating a drop down list ("ordered", "In transit", "delivered", etc), then created conditional formatting rules to change the color of the cell. Works pretty well, and will allow me to make changes are our needs grow. I think I'm going to clone this sheet to create a variation for our deliverable to track their outbound freighting schedule.

Appreciate the comments.