Need cell to return NULL if there is a formula in referenced cell

ArviLaanemets · #6 10-11-2023, 10:52 PM

OK, I looked at your table, and yes, you are using a Defined Table there. But why have you all those empty rows there at all? With Defined Table, whenever you enter anything into any cell immediately below of currently last one, the Table is automatically expanding, and will include the row you entered this value into. Also all formats, Data Validation Lists, conditional formats, formulas, etc. are automatically applied for all cells in new Table row, so long as they are same for whole column. The same happens, when you activate rightmost cell in last row of your Table, and press Tab key.

Now about your formulas.
For column S, the working formula may be like

Code:

=IF(SUM($T2)=0,"", MAX(0,$T2-TODAY()))
or same formula in Table format
=IF(SUM([@[Application Extension Submission Deadline]])=0,"", MAX(0,[@[Application Extension Submission Deadline]]-TODAY()))

The formula in Table syntax looks longer, because you have unreasonably long header for column it is referring to, plus the header contains spaces, so for every reference an additional pair of brackets must be used, but with this syntax, you can move, add or delete columns in your Table without this affecting the formulas in any way, unless you delete a column some formula is referring to. Btw. You can also rename columns freely - all formulas in this workbook (and designed in Table Formulas format) will adjust automatically!

You also may consider to change your other formulas likewise:
For column R like

Code:

=IF(SUM($Q2)=0,"", DATE(YEAR($Q2)+1,MONTH($Q2),DAY($Q2)))
or
=IF(SUM([@[Declaration Date]])=0,"", DATE(YEAR([@[Declaration Date]])+1,MONTH([@[Declaration Date]]),DAY([@[Declaration Date]])))

For column T like

Code:

=IF(SUM($Q2)=0,"", $Q2-60)
or
=IF(SUM([@[Declaration Date]])=0,"",[@[Declaration Date]]-60)

(In this formula, there was no need to use DATE() function, as really dates are long integers, where value 1 represents a day, and adding or removing an integer value simply returns a date later or earlier this number of days (so long as result remains a positive number!)
etc.

In your original formulas, when the formula had to return empty string, you used a space string instead! I.e. you used " " instead "". Those are different values!

In deadline formula (in column R) you are adding 1 to year number. Let's assume the declaration date will be 29.02.2024 (a leap year). With current formula you get deadline as 01.03.2025. Do you really want this, or you'd prefer 28.02.2025? When yes, then the formula must be something like

Code:

=IF(SUM([@[Declaration Date]])=0,"", MIN(DATE(YEAR([@[Declaration Date]])+1,MONTH([@[Declaration Date]]),DAY([@[Declaration Date]])),DATE(YEAR([@[Declaration Date]])+1,MONTH([@[Declaration Date]])+1,0)))

(Because day 0 of any month is the last day of previous month!)

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I want my formula to result in zero if referenced cell = 0, otherwise calculate	Daronovitz	Excel	2	12-21-2022 11:44 AM
Filling a formula down a column where only one cell value referenced changes?	sakurasanta86	Excel	1	08-27-2018 03:23 AM
Filling a formula down a column where only one cell value referenced changes?	takamaz	Excel	2	03-11-2018 08:56 AM
Random cell return using vlookup formula	mattbeaves	Excel Programming	3	05-22-2017 04:38 AM
Formula to return next non-blank cell in a range	Anyroad	Excel	4	09-15-2015 04:13 PM