how to compare one parent child value with another parent child value?

PYU · 08/31/21

first parent child has startdate and end date and second parent child has startdate and enddate, how can i compare both dates are greaterthan or equal or inbetween

Paul Newcome · 09/01/21

My suggestion would be a helper column with the following in the child rows:

=PARENT(Infos@row)

Now that we have the material also listed on the child rows, you can use this to flag the rows at the top...

=IF(COUNTIFS([Helper Column]:[Helper Column], @cell = Infos@row, Startdate:Startdate, @cell <= Enddate@row, Enddate:Enddate, @cell >= Startdate@row) = 0, "Green", "Red")

Paul Newcome · 08/31/21

Are you able to provide a screenshot for reference?

PYU · 09/01/21

Hi paul, thank you for ur time,,,

Screenshot _sheet1.png

There are three materials named AAA, BBB, and CCC. I would like to submit a request to offer material AAA from 5th September 2021 to 7th September 2021. it has to compare material AAA customer list ,However, this period clashes with material AAA’s Customer 2 slot (4th September 2021 to 9th September 2021) and therefore it should indicate red symbol in Availability column . If my intended request period does not clash with material AAA’s Customer slots, then should indicate as green symbol.

The second material that I would like to request for is material BBB, from 24th September 2021 to 25th September 2021. now it has to compare material BBB customer list ,it clashes Material BBB’s Customer 1 slot (23rd September 2021 to 28th September ) therefore it should indicate red symbol in Availability column.

can u pls help me out

Paul Newcome · 09/01/21

My suggestion would be a helper column with the following in the child rows:

=PARENT(Infos@row)

Now that we have the material also listed on the child rows, you can use this to flag the rows at the top...

=IF(COUNTIFS([Helper Column]:[Helper Column], @cell = Infos@row, Startdate:Startdate, @cell <= Enddate@row, Enddate:Enddate, @cell >= Startdate@row) = 0, "Green", "Red")

PYU · 09/02/21

hello paul,,, as per ur instruction

formula2.png

i created a Helper column also specified the formula =PARENT(Infos@row) for that column

and i tried out next formula on Availability column

=IF(COUNTIFS([Helper Column]:[Helper Column], @cell = Infos@row, Startdate:Startdate, @cell <= Enddate@row, Enddate:Enddate, @cell >= Startdate@row) = 0, "Green", "Red")

i got like this,,can u pls have a look..

thanks for ur help

PYU · 09/02/21

oh ok,, i did a mistake in Helper Column name,,,

That perfectly great,,,, works fine👍️

Thnak you so much

PYU · 09/02/21

Is it possible to specify availability formula only for chilrows under first parent

i mean i want to show availability status only under request parent childs

Paul Newcome · 09/02/21

I'm not sure I understand your question.

PYU · 09/02/21

Status.png

Ok, Actually i clicked "Covert to cell formula" so it shows availability status for all cell.

i would like to show Availability status only for Request parent Child not for Material parent and Material parent Child.

Paul Newcome · 09/02/21

The helper column can actually be hidden. The availability formula would only go on the rows in the Request section.

PYU · 09/08/21

perfect!!!! thanks for your help!!

Paul Newcome · 09/08/21

Happy to help. 👍️

PYU · 10/14/21

Hi. Mr .Paul,

i have a simliar problem to compare, bit different way,, can you please me out

https://community.smartsheet.com/discussion/83979/compare-time#latest

Thank you for your help...

how to compare one parent child value with another parent child value?

Best Answer

Answers

Help Article Resources

Categories