Best Of

Automations cannot be triggered by data coming from another sheet (cell links/cross sheet references in formulas). You would need to set the trigger to be time based and then use the column containing the cross sheet formula as a condition.

Hi Christian

Please try using the formula below

=IFERROR(INDEX({Sheet1 Colume1}, MATCH(ZeilenID@row, {Sheet1 Bereich 1}, 0)), "")

I got it

Hi Ena,

I would suggest that to create one helper column for Months, =IFERROR(MONTH([Assignment Date]@row), ""), I am assuming in the Result column each cell represents each month or you can create summary fields for each month then apply this formula =COUNTIF(Month: Month, 1), for each month update the month number, for example, 1 for January, 2 for February, etc.

I hope this works for you.

You need something more like this:

=SUMIFS({2023 Spend Formulas Range 1}, {2023 Spend Formulas Range 2}, [Original Material #]@row, {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

Then yes. You can drop them all inside of a SUM function or you can add them all together.

=SUM(SUMIFS(.....), SUMIFS(.....), SUMIFS(.....))

or

=SUMIFS(.....) + SUMIFS(.....) + SUMIFS(.....)

Hi Jeremy

There is an inbuilt solution to this without the need for splitting across 2 sheets.

If you have a Gannt with some tasks needing to observe the working week settings and some tasks that need to ignore the working week setting, then on the tasks to ignore the working week, put an "e" in front of your duration data.

i,e, 7 in duration on a 5 day week will normally put 5 days in week one then 2 days in week 2 with the weekend being missed.

but e7 in duration will put all 7 days in week 1 i.e. it will plot on your non working days.

The feature is called elapsed time.

I hope this helps!

Kind regards

Debbie

Hi @angelapaj

Can you try the formula below?

=IF(AND([CLIENT TYPE]@row = "Platinum", [TOTAL AGING]@row > 1), "Red", IF(AND([CLIENT TYPE]@row = "Gold", [REQUIRED DATE]@row < TODAY(), [DRAWING STATUS]@row = "Not Started"), "Red", IF(AND([CLIENT TYPE]@row = "Gold", [REQUIRED DATE]@row < TODAY(), [DRAWING STATUS]@row = "Review"), "Yellow", IF(AND([CLIENT TYPE]@row = "Silver", [REQUIRED DATE]@row < TODAY(), [DRAWING STATUS]@row = "Not Started"), "Yellow", IF(AND([CLIENT TYPE]@row = "Silver", [REQUIRED DATE]@row < TODAY(), [DRAWING STATUS]@row = "Review"), "Yellow", "Green")))))