How do I make the cell to change data after other cell is populated?

Rob_NYC

I have sheet 1 with the rows below

with formula in:

LAN Migration Ready =COUNTIF({sheet 2 name Range 10})

LAN Migration Ready Approved =COUNTIF({sheet 2 name Range 11})

LAN Migration Scheduled =COUNTIF({sheet 2 name Range 12})

LAN Migration Complete =COUNTIF({sheet 2 name Range 13})

Sheet 2 I have:

Each row represents a Site Location

The trick is when "LAN Migration Ready Approved" is populated in any row in the sheet 2, Sheet 1 Line "LAN Migration Ready" should subtract the "count" because in Sheet 2 a "date" was entered in the "LAN Migration Ready Approved" column.

When Sheet 2, column " LAN Migration Scheduled" a "date" is entered the sheet 1 line "LAN Migration Approved" should subtract the count

When Sheet 2, column "LAN Migration Complete" a "date" is entered, sheet 1 line "LAN Migration Scheduled" should subtract the count

The life cycle is the Migration starts in "LAN Migration Read" then "LAN Migration Ready Approved" then "LAN Migration Scheduled" then finishes in "LAN Migration Complete"

and Sheet 1 should show only the "count" when the next column in Sheet 2 is not populated. If it is populated, then the previous row in Sheet 1 should subtract the count because that entry in sheet 2 column

Any help I will appreciate!😀

Rob

Find more posts tagged with

Formulas

Support

functionality

IT & Operations

Project Management

Accepted answers

Tomasz Giba

Hi @Rob_NYC

Try this:

Enter this formulas in Migration counts starting from the top:

=COUNTIF({READY}, 1) - COUNTIF({APPROVED}, 1)

=COUNTIF({APPROVED}, 1) - COUNTIF({SCHEDULED}, ISDATE(@cell))

=COUNTIF({SCHEDULED}, ISDATE(@cell)) - [Column2]5

=COUNTIF({COMPLETE}, 1)

I named reference ranges as columns in the sheet 2

You naming a range here:

Let me know if that works! Good Luck!

Tomasz Giba

=IF(ISDATE([LAN MIGRATION SCHEDULED]@row), 1, 0)

All comments

Tomasz Giba

Hi @Rob_NYC

Try this:

Enter this formulas in Migration counts starting from the top:

=COUNTIF({READY}, 1) - COUNTIF({APPROVED}, 1)

=COUNTIF({APPROVED}, 1) - COUNTIF({SCHEDULED}, ISDATE(@cell))

=COUNTIF({SCHEDULED}, ISDATE(@cell)) - [Column2]5

=COUNTIF({COMPLETE}, 1)

I named reference ranges as columns in the sheet 2

You naming a range here:

Let me know if that works! Good Luck!

Rob_NYC

I am going to try, I will let you know

Thank you

Rob

Rob_NYC

Tomasz

I think I got it right, I will let you know

Rob

Rob_NYC

Tomasz

The only thing I need is a formula for sheet2 when I enter a date on the date column the column for check box populate automatically, because I created extra columns for the check box that I will hide from the tracker.

If you can help me, I will appreciate very much!

Rob

Tomasz Giba

=IF(ISDATE([LAN MIGRATION SCHEDULED]@row), 1, 0)

Rob_NYC

Tomasz

Thank you very much for your help! Good job!

Rob😀

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.