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

sofiachr

Board Regular
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:

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:

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!

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Can you post some sample data please.

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
ABCDEFGHIJKL
1Data1Data2Data3Data4Data5Data6Data7Data8IF Formula1IF Formula2
2Red28723232727
3Blue8956821
4Green1022108107
5Pink421101097
6Purple7693977
7Brown89421075
8
Sheet12

With the formula in K2 deleted....

Excel Workbook
ABCDEFGHIJKL
1Data1Data2Data3Data4Data5Data6Data7Data8IF Formula1IF Formula2
2Red28723230
3Blue8956821
4Green1022108107
5Pink421101097
6Purple7693977
7Brown89421075
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

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!

I do not think that there is a limit using vba.
Code:
``````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.

Replies
5
Views
198
Replies
6
Views
162
Replies
9
Views
270
Replies
8
Views
193
Replies
2
Views
109

1,203,758
Messages
6,057,180
Members
444,913
Latest member
ILGSE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back