Macro like functionality

Is there any method planned that would enable a change to trigger other changes.

For example, I'd like to be able to cut out a step in one of my sheets whereby if I mark a task as complete that it would automatically change the % complete column to 100 (and vice versa).

Find more posts tagged with

Formulas

IT & Operations

Cell linking

Comments

Travis

Hi Rachel! This could be done using formulas. Here are a couple examples:

Change % Complete to 100% if a task is marked as Complete

-Have a Dropdown List in your sheet called DropdownList with predetermined values, including “Complete”

-In the % Complete column (which should be formatted as %), add this formula:

=IF(DropdownList1 = “Complete”, 1)

This will show 100% if the corresponding Dropdown List cell contains “Complete” .

Note- if you are using dependencies and the % Complete column is associated with the dependencies, you cannot add formulas to the % Complete column. If this is the case, you may want to use this next option.

In your Status column (whichever column you want to show “Completed”, we will call it “Status” in this example) add this formula:

=IF([% Complete]1 = 1, “Completed”)

This will display “Completed” if % Complete is 100%

Rachel Ambler

The % complete column though is normally manually updated as work progresses. Currently I have to change it to 100% AND change the Status to complete in order for the row to be valid and have to currently use Conditional Formatting to highlight rows where I've not changed both.

I'd like to be able for one to cause the other, no matter which way I hit it.

I should add that I have forumula's up the yazoo on my sheet doing all manner of hairy stuff so I'm no lightweight on that!

David Fitzgerald

Hi Rachel,

I've run into this in the past, also. It seems that if the % Complete column is chosen in Gantt mode to be the source for showing % Complete on the Gantt chart, then the column will not allow formulas. While I do find it a little frustrating, what I've done to get around this is I have created another column that shows % complete with formulas, and set the Gantt to work off of a column that I label "Gantt % Complete". Then, I periodically copy all of the text from the formula column to the Gantt % Complete column so my chart agrees with the data.

Otherwise, I just don't use the Gantt to show % Complete at all, and I just use the data columns.

Hope this helps.

Travis

There isn’t going to be a way to have connect columns where you can change the other based on which one you select. The best bet is to choose which column you want to edit (mark as completed) and add the formula to the other column.

Rachel Ambler

Which is why I entitled the question 'Macro like' as I knew that formula's alone weren't going to cut the mustard here.

Thanks anyway!

Jeremy Pemberton

Rachel,

Our team has run into the same problem..

Please let us know if you find a solution.. meanwhile we are going the to try a google apps scripts and see what might happen.