Auto fill consecutive row after processing from different sheet.

Originally Posted by 6StringJazzer

I think this can be done just with formulas but your explanation is very difficult to understand, and the formulas you are using in your workbook do not make it any easier.

I have compared your first paragraph to your workbook. The first problem is that the same coupon code appears multiple times in "Buy". So how do you determine which row applies to the "Sell" row?

Second, it is completely unclear what your business rules are. If you could describe what you are actually trying to accomplish without diving down into Excel formulas it might be easier. For example, it is completely unclear why a calculation of weight would have anything to do with a coupon. In your situation what does "coupon" mean? If you hired me as a consultant I would say throw away your Excel workbook and tell me in detail what your requirements are so we can build the appropriate tool.

I can't figure out the next paragraph at all.

As a side note it is unnecessary to use

=SUM(G2-H2)

as seen in Buy!I2. Instead, use

=G2-H2

Hi 6StringJazzer.
OK, let's make this clear. The Coupon is the number of the bill. So if we want to sell any product, the Coupon in "Sell" sheet will be looking for the coupon number in "Buy". If it has any match, then it will proceed. The problem is, some buyer won't always buy all kind of the products from the same bill number. Some times they just buy just triangle or just bowl.

The row to apply in the "Sell" row could be any row from the "Buy". That's why there are same coupon's number in "Buy" but different "type" of product.

[/QUOTE] For example, it is completely unclear why a calculation of weight would have anything to do with a coupon.[/QUOTE]
You got the points there
I need to link up the "Buy" and the "Sell" sheets, but to do that, I can only think the number of the bill on the paper. Do you have any suggestion for a better reference?

Thanks for the input about the SUM, i was used to it, but i will change it.

The next paragraph is about the "Cash Balance" Sheet. where I need to fill the Input if I got any total from each row in the "Sell".
And Output in a different row if i got any total from the "Buy". So basically, an auto simple accounting from any purchase or sells we made in "Buy" and "Sell"

Thanks for your effort in understanding my problem, I really appreciate that

First, it would help if you describe what you are trying to calculate in column H. To do that I expect that you would also have to describe what Weight, Box, and Net, and Decrease/Increase mean.

Column H in "Buy" is the weight of the container.
Weight is the total weight of the product, including the container. First, I want to differentiate the product by it, but I'm afraid I may come into container with the same weight. So I was thinking of using the number of the bill which is unique.
Net, is the net weight of the product, so it will be

=G2-H2

It seems to me that you can't just match on any coupon number in Buy because the Net is different for each item. Do you need to match on both Coupon and Type?

If it is possible, yes. but I think a match on the Coupon will be enough, also just in case a buyer will buy not the whole type of product. ie: the total net weight in "Buy" is 5515, the buyer only take 2000.

Also, in Sell, you have two rows with the same coupon number but different buyers. How can two buyers be on the same bill?

It's because we only use the bill for a reference. In circumstances, we open a new bill for the buyer. Sometimes the buyer only buy a type of the product, not the whole.

Regards,
Peter