Google spreadsheet validation data empty cell

Question

So i'm struggling with the following:

I have this formula (i know it's huge but it works so far :P)

=IF(AND(COUNTIF(basictier1;"*"&A4&"*"));"T1 Basic"; IF (AND(COUNTIF(basictier2;"*"&A4&"*"));"T2 Basic";IF(AND(COUNTIF(basictier3;"*"&A4&"*"));"T3 Basic";IF(AND(COUNTIF(basictier4;"*"&A4&"*"));"T4 Basic";IF(AND(COUNTIF(basictier5;"*"&A4&"*"));"T5Basic";IF(AND(COUNTIF(basictier6;"*"&A4&"*"));"T6 Basic"))))))

The basictier1, 2 etc are all ranges. The T1 Basic etc are values the K column has to get when something from the Basictier1 range has been selected in the A column. So that all works, but... i only want to have the value per cell in column K empty instead of always displaying T1 Basic when the cells in column A are empty.

Can anyone give me a tip? I feel that it's not that complicated in google spreadsheet, although many things seem to be unfortunately.

Answer 1

Try this out:

=IF(A4="",K4="",IF(AND(COUNTIF(basictier1;"*"&A4&"*"));"T1 Basic"; IF (AND(COUNTIF(basictier2;"*"&A4&"*"));"T2 Basic";IF(AND(COUNTIF(basictier3;"*"&A4&"*"));"T3 Basic";IF(AND(COUNTIF(basictier4;"*"&A4&"*"));"T4 Basic";IF(AND(COUNTIF(basictier5;"*"&A4&"*"));"T5Basic";IF(AND(COUNTIF(basictier6;"*"&A4&"*"));"T6 Basic")))))))

I have just added one if condition to your formula, that is

=IF(A4="",K4="",Else Formula provided by you)

above formula validates your required contion.

I hope this will help you.