Need another condition but I´ve reached the maximum number of conditions.

[sofiachr]

Hi everyone

I´m trying to put one more criteria to this formula but there may not be any more criterias. That´s why I need a new way to do this. Following formula is used right now:

=IF($N15="","",CONCATENATE(IFERROR(INDEX(Städavgifter!$K$13:$K$500,SMALL(IF($N15=Städavgifter!$B$13:$B$500,ROW(Städavgifter!$B$13:$B$500)-ROW(Städavgifter!$B$13)+1),ROW($1:$1))),""),", ",IFERROR(INDEX(Städavgifter!$K$13:$K$500,SMALL(IF($N15=Städavgifter!$B$13:$B$500,ROW(Städavgifter!$B$13:$B$500)-ROW(Städavgifter!$B$13)+1),ROW($2:$2))),""),", ",IFERROR(INDEX(Städavgifter!$K$13:$K$500,SMALL(IF($N15=Städavgifter!$B$13:$B$500,ROW(Städavgifter!$B$13:$B$500)-ROW(Städavgifter!$B$13)+1),ROW($3:$3))),""),", ",IFERROR(INDEX(Städavgifter!$K$13:$K$500,SMALL(IF($N15=Städavgifter!$B$13:$B$500,ROW(Städavgifter!$B$13:$B$500)-ROW(Städavgifter!$B$13)+1),ROW($4:$4))),""),", ",IFERROR(INDEX(Städavgifter!$K$13:$K$500,SMALL(IF($N15=Städavgifter!$B$13:$B$500,ROW(Städavgifter!$B$13:$B$500)-ROW(Städavgifter!$B$13)+1),ROW($5:$5))),"")))

The formula is getting the value of fines for persons in the sheet named “Städböter”. The cell in N13 is a customer number he match with column B in the other sheet. In this rows where he finds matches he takes the fine (column K) and put it in this cell the formula is written in. But I only want him to take the fine if the status in column L is set as “Not Paid”. If it´s “Paid” he should ignore that one and go one to the next match. Every person can have a maximum number of 5 fines for each payment, as you can see.

What I would like to ad is IF($L$13:$L$500=”Paid”;””;
It would look like:
(IFERROR(INDEX(Städavgifter!$K$13:$K$500,SMALL(IF($L$13:$L$500=”Paid”;””;IF($N15=Städavgifter!$B$13:$B$500,ROW(Städavgifter!$B$13:$B$500)-ROW(Städavgifter!$B$13)+1),ROW($1:$1))),"")

Problem is that it´s to many conditions. So, my (not really simple) question is, how could I do this in another way?

Thank you! I´m desperate!

 

[nuked]

Can you post some sample data please.

 

[Akashwani]

Hi,

I may be mistaken here, but if you have hit the maximum 7 (I assume you are not using 2007>)
Then put your 7th/8th IF formula in another cell and refer to that as the last part of your 6th IF....

This is not a very good example, but it appears to work...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8		IF Formula1	IF Formula2
2	Red	2	8	7	2	3	2	3		27	27
3	Blue	8	9	5	6	8	2	1
4	Green	10	2	2	10	8	10	7
5	Pink	4	2	1	10	10	9	7
6	Purple	7	6	9	3	9	7	7
7	Brown	8	9	4	2	10	7	5
8
Sheet12

With the formula in K2 deleted....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8		IF Formula1	IF Formula2
2	Red	2	8	7	2	3	2	3		0
3	Blue	8	9	5	6	8	2	1
4	Green	10	2	2	10	8	10	7
5	Pink	4	2	1	10	10	9	7
6	Purple	7	6	9	3	9	7	7
7	Brown	8	9	4	2	10	7	5
8
Sheet12

Having said this, I'm sure if you can provide accurate sample data an alternative can be found.

I hope that helps.

Ak

 

[sofiachr]

Hi

Thank you for helping. Not exactly the solution I needed but I´ve managed to solve the problem by having two lists (one for payed ones and one for unpaid) instead of just one so that I can avoid this last condition. It´s a little more work to move around rows but it´s working.

Thank you for trying!

 

[MPW]

I do not think that there is a limit using vba.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'sample conditional if statement

    If ActiveCell.Row = 1 And ActiveCell.Column = 1 Then
        If Range("A1") = 1 Then
            Range("A1").Interior.Color = RGB(255, 255, 0)
        ElseIf Range("A1") = 2 Then
            Range("A1").Interior.Color = RGB(0, 255, 0)
        ElseIf Range("A1") = 3 Then
            Range("A1").Interior.Color = RGB(255, 0, 0)
        End If
    End If

End Sub

This method can be tricky, for instance you always have to have a condition to set it back to normal if none of the conditions are met but there are times when it is handy. You also have more controls then are available in the regular conditional format. Such as more borders etc.