RusselB wrote:You have no entries in column F for the rows where you are getting your #VALUE error, since your formula requires data from column F of that same line.

No, that is not the problem. Cell E5 wants to use the value of cell F5 in a subtraction operation. F5 is empty. Cell F5 is treated as having a value of zero and subtraction will work. As a test, temporarily put formula

=9-F5 in cell H5. This will produce 9, not #VALUE!.

Engineer817 wrote:I think I've narrowed it down to the fact that each cell's calculation is dependent on the cell above it.

No, that is not the problem. Cell E4 contains

text, not a number. As a test, temporarily put formula

=ISTEXT(E4) in cell D4. Cell E5 uses E4's text value in an arithmetic operation, and you receive a #VALUE! error.

Engineer817 wrote:Cell E4:

=IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A1)) = 1; (B2 +(B4 *V3)-F4 ); "")

&T(STYLE(IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A1)) = 1;"Green";"Black")))

This formula has two functions, IF() and T(), joined by the & operator. The & operator always returns text.

=ISTEXT(E4) is TRUE.

You are using the STYLE function when you should be using Format → Conditional Formatting. Simplify your formula by removing everything after the & operator so that it can return a number. Well, when the IF test is false it will return the null string, but this value is treated as zero when you use it with an arithmetic operator. Format these cells with style Black.

Put your formatting test in a helper column:

=OR(WEEKNUM(TODAY(); 1) >= An; (YEAR(TODAY()) <> A1)). Then test the helper in the conditional formatting dialog: Condition 1 → Formula is →

HelperCell and conditionally apply cell style Green. Read about this feature in Help → Index or in

User Guides (PDF) or

searching for topics about it in the

Calc Forum. The helper column does not have be visible. You can use Format → Column → Hide or you can put it far to the right, say column ZZ.

Cell E4 produces a (text) result and does get a #VALUE! error because the formula there is different than all the other cells in column E. (I'm not showing the part after the & operator.)

E3:

=IF(OR(WEEKNUM(TODAY(); 1) >= A3 ; (YEAR(TODAY()) <> A1)) = 1; (E2 +(B3 *V3)-F3 ); "")E4:

=IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A1)) = 1; (B2 +(B4 *V3)-F4 ); "")E5:

=IF(OR(WEEKNUM(TODAY(); 1) >= A5 ; (YEAR(TODAY()) <> A1)) = 1; (E4 +(B5 *V3)-F5 ); "")E6:

=IF(OR(WEEKNUM(TODAY(); 1) >= A6 ; (YEAR(TODAY()) <> A1)) = 1; (E5 +(B6 *V3)-F6 ); "")Note that E4 uses B2 before the + operator. The others use a cell in column E.

It seems that you want to use $V$3 in your formula instead of V3 so you can fill this formula down. Read section

8. Using formulas and cell references in

Ten concepts that every Calc user should know. The conditional formatting formulas also support relative, absolute, and mixed references.

I see that

FJCC has suggested a way to change your formula so STYLE() will produce numbers. A minor problem with

=IF()+STYLE() is that you will get 0 in the cell instead of "" when the OR() test fails since the + operator always returns a number. There are ways to write the formula to get "" insead of 0 in those cases but I'm not going to provide further details because you really should use conditional formatting for this task.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer

AOO 4.1.7 Build 9800 on MacOS 10.14.6. The locale for any menus or Calc formulas in my posts is English (USA).