Avoid circular conflict

masplin

Active Member
Joined
May 10, 2010
Messages
413
I am trying to to use data in one column in a second column, but running into a circular arguement. Is there any way to avoid this. My table looks like this

PINVNoCustomer NoService Code LineTypeCodeService
PINV000002200000310000Item501720169LUBE01
PINV000002200000310000Service CodeLUBE01LUBE01
PINV000002200000310000ItemENOCAS012B1LUBE01
PINV000002200000320000ItemMOTSLOTMOT01
PINV000002200000320000Service CodeMOT01MOT01
PINV000002200000330000Service CodeSUP01SUP01

<tbody>
</tbody>

I am trying to calculate the last column from the 5th column. The lines 10000 are all part of the service LUBE01, but the types are for parts (items) and the sale cost (service code). I need to group these together for other calculations.

The code I tried is this

Code:
=IF(     [Type]="Service code",
     [Type],    
    CALCULATE(
                          VALUES('Posted Service Invoice Line'[code]),
                           FILTER('Posted Service Invoice Line',
                                         [PINVNo]=EARLIER([PINVNo]) &&
                                         [Service Code Line]=EARLIER([Service Code Line]) &&
                                         [Type]="Service Code"
                                         )
                            )   
      )
I thought the IF clause would exclude any rows where the circular argument would happen. Is there a way to write this please?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top