Moving Entries with same info to another spreadsheet
Posted by RogerBlack on June 25, 2001 5:09 AM
I have a spreadsheet call poldetailsmot with columns called Policy Number,Reference Number,Date Effective and Premium Charged. Can anyone tell me how I can get all the transactions that have the same info in the first three columns copied to another spreadsheet as ONE ENTRY with an overall total?
Thanks,
Roger
Posted by Joe Was on June 25, 2001 8:27 AM
Concatenate Values/Text.
On sheet X where you need the data;
if the data is in Sheet "Y" Cell "D9" and "E9"! Then;
The trick is to reference the sheet name with a ! next to the Cell address or Range, like SheetY! or 'SheetY'! both work. The concatenation is done by space then "&" space with no quotes around the ampersand. You must add any formatting on your own!
If D9=123 and E9=Test then,
=D9 & E9 gives 123Test and
=D9 & "and" & E9 gives 123andTest
=D9 & " and " & E9 gives 123 and Test.
Try an "If test."
If A1=1 and B1=2 and C1=3 Then,
=If(AND(A1=B1,B1=C1),A1 & " " & B1 & " " & C1, "")
==> 1 2 3
or
=IF(AND(A1=B1,B1=C1),Sum(A1:C1),"")
==> 6
The Sheet reference is: SheetY!A1 in each reference above. JSW
Posted by Joe Was on June 25, 2001 8:33 AM
As VB Code.
Set MyData= Worksheets("Sheet y").Range("D9") & Worksheets("Sheet y").Range("E9")
MyData.Copy Destination:=Range(Worksheets("Sheet X").Range("A1"").Address)
You can avoid the Sheet tag by using a with statement.
With Worksheets("Sheet y")
your code...
End With
JSW
Posted by RogerBlack on June 25, 2001 1:15 PM
Re: As VB Code.
This spreadsheet is for accounting purposes for a whole month and a client might come three times during the month. What i want is a way to getall the transactions with the same policy number, reference number, date effective and premium to be combined as one transaction and placed in another worksheet with an overall total.
Policy NO Ref. No. Date Effec premium
e.g P10000201 PA1111 07/07/2001 $25.00
P10000201 PA1111 07/07/2001 $20.00
P10000201 PA1111 07/07/2001 $10.00
How would i get this to another worksheet with a total of $55.00?
Roger Set MyData= Worksheets("Sheet y").Range("D9") & Worksheets("Sheet y").Range("E9")
