Auto fill consecutive row after processing from different sheet.

**ganekon** · 01-03-2011, 02:34 PM

Originally Posted by snb

use this formula in H2 in sheet Sell:

=F2-SOMPRODUCT((Buy!$B$2:$B$30=$C2)*(Buy!$D$2:$D$30=$D2)*(Buy!$F$2:$F$30))

snb, your formula is trying to get a result from calculating the weight if the coupon and product type has a match between "Buy" and "Sell", am I correct?
The formula I made in "Sell" H2 is correct in condition if we processed in the same rows.

Based on your formula, Thanks to you I came to this:

=G2-SUMPRODUCT((Buy!$B$2:$B$30=$C2)*(Buy!$D$2:$D$30=$D2)*(Buy!$I$2:$I$30))

Now.. moving on to "Cash Balance"
How do we fill the date, from "Buy" and "Sell sheets to A2:A32 automaticaly?
Each total from K2:K32 in "Buy" goes to Output
Each total from I2:I32 in "Sell" goes to Input.

**snb** · 01-03-2011, 04:04 PM

cfr. the attachment

**ganekon** · 01-03-2011, 05:39 PM

Hi snb,
What I'm trying to make will look like the this attachment.
So, total from "Buy" or "Sell" will be put in sequence by the date in "Cash Balance".
Each will be separated in different row.

**snb** · 01-03-2011, 06:16 PM

cfr. the attachment.

In the balance sheet all data form sell and buy will be inserted.(64 rows)
After that you can sort this balance sheet (A1:F65) on date (menu/Data/sort).

**ganekon** · 01-03-2011, 08:57 PM

Originally Posted by snb

cfr. the attachment.

In the balance sheet all data form sell and buy will be inserted.(64 rows)
After that you can sort this balance sheet (A1:F65) on date (menu/Data/sort).

That won't work, snb. After I sort it out, the datas were mixed with each other.
I need to keep every total from each dates (even the same date) to be in different rows.

**snb** · 01-04-2011, 05:32 AM

That will definitely work if you sort correctly:
select A1:D63, menu/Data/sort

or using a macro

Sub sort_snb()
  Sheets("Cash Balance").Range("A1:D63").Sort Range("A2"), , , , , , , xlYes
End Sub

**ganekon** · 01-04-2011, 10:53 PM

Originally Posted by snb

That will definitely work if you sort correctly:
select A1:D63, menu/Data/sort

or using a macro

Sub sort_snb()
  Sheets("Cash Balance").Range("A1:D63").Sort Range("A2"), , , , , , , xlYes
End Sub

Hi snb,
I've change the formula after your ideas, thanks again. I think there is no other choice than to make it separate into double tables like the one you suggest. I have 1 last question, how do we fix the #VALUE! error in E37:E63 ?