Data Validation Inconsistency when using Linked Cell

Question

When setting up DataValidation the format of the Cells in the list default to 'General' and a 'Blank Cell' (See screenshot of Cell C16).

That's fine, I can insert a formula, in this instance '=IF( C16="","Blank", "Filled"), and it correctly returns 'Blank'. However if I link the contents of Cell C16 to another Celll, in this instance to Cell C26, I get a different result because the Format of the Cell, althought still 'General' has defaulted to 'Zero'.

And the result of the same formula returns 'Filled' when Cell C16 is still 'Blank'. This seems an inconsistency in the default setting of the Data Validation being 'Blank' or Visa versa. (makes no difference is 'Display Zeros' is switched off)

Answer 1

Thanks, but that is just papering over the issue. I don't want to know the content of Cell C13 as your alternative formula does. (I already know that by looking at Cell C13.

This is an issue of Data Validation and if it is incorrect using a link then it has repercussions to subsequent formulas that use Cells C23-C29 to calculate other formulas or data validations.

The Format should either be set by the App at 'Zero' or 'Blank' in both instances. Problem solved.

Answer 2

Change the formula in C23 to =IF(C13="","",C13)

For text values, you can also use the shorter formula =C13&"" or alternatively =T(C13)

Fill down.