Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

So I'm struggling with the following:

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

=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.

share|improve this question
    
Can you share a google spreadsheet with us? It makes this apparent not complicated question a bit less complicated ;-) –  Jacob Jan Tuinstra Nov 27 '12 at 17:37
add comment

1 Answer

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.

share|improve this answer
    
Hi! thanks for trying to help me out here. The above solution gives a parse error, the K4 is actually F4 now btw, but it should matter... i changed the K4 to F4 and even others but always parse error. –  user23072 Aug 22 '12 at 8:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.