Complicated formula help

[kjpinch]

I have a data table that looks something like this, only with significantly more rows. In this data, PR+TC=GL for each set of data in column A. So, for example, 

12345 PR + 12345 TC = 12345 GL. In this case $20 + $40 = $75, which is not correct. When it isn't correct, I need the difference to come from the number in the TC row -- so in this example, the TC row would change from $40 to $55. Is it possible to create a formula to do this?

Col A	Col B	Col C
12345	PR	$20
12345	TC	$40
12345	GL	$75
67890	PR	$30
67890	TC	$50
67890	GL	$80

[#2]

@kjpinch 

Please check the formula and result in Column D in the attached file. 

Your data has been formatted as an Excel table, this helps to copy down the formula.

The formula also assumes that previous two consequetive rows have PR and TC values for a specific GL item.   

[#3]

Hello @kjpinch,

Assuming "GL" is always directly under "TC" and your table begins in cell A1:

=IF(B3="GL",C3-C2-C1,"") could be a formula for cell D2

Then copy the formula down as necessary.

This would show the value in the TC row that is required to force PR+TC=GL.

[#4]

---

@kjpinch wrote:

so in this example, the TC row would change from $40 to $55. Is it possible to create a formula to do this?

---

A cell can either have a value or a formula but not the both.

To change the TC price in the first place, you will need a VBA solution to achieve that.

And if you are open to a VBA solution, please upload a sample file with small subset of your data (after removing any sensitive data) and the layout of your sample file and your original file must be exactly the same.