Sales Order ID
178474
178474
178474
178474
178474
178474

Product ID
1730
1740
1800
1410
1000D
1605

Sales Price
$9,500.00
$4,950.00
$5,000.00
$0.00
($21,550.00)
$52,500.00

SalesOrderID, Product ID, and Sales Price represent 3 separate columns.
The negative sales amount ($21,550) is a discount that needs to be
prorated among the other products sold on this invoice. I've developed
a formula that performs the calculation, however it currently uses an
absolute cell reference to identify the Discount - which requires me to
manually update this cell reference for each change in Sales Order ID.
Since I have 10,000 records representing roughly 2,000 sales, I need to
find a scaleable solution.

I've been trying to use the Address Function along with Index and
Match, but I haven't had any luck yet.

Any help you can provide would be great.


--
Rlmccants
------------------------------------------------------------------------
Rlmccants's Profile: http://www.excelforum.com/member.php...o&userid=26097
View this thread: http://www.excelforum.com/showthread...hreadid=395007