Anomandaris
05-28-2009, 02:45 AM
I have 4 sheets with raw data, Sheets named BOLT,HOOD,GUN,ROPE. In each of these sheets there's 5 datasets which I have set as dynamic ranges
eg. on "BOLT" you can see JPN alternatives A to E, the ranges are named JPNA,JPNB,JPNC,JPND,JPNE
similarly in "HOOD" you see EUR alternatives from A-E, ranges are named EURA,EURB and so on
GUN........has ranges UKA,UKB etc
"ROPE" has ranges USA,USB,USC, etc
Now what I'm trying to do is, when I make a selection on the sheet 'chart' from the drop down boxes, I need to get the spread(difference) between the 2 selections and copy this into the Range 'DATA' in sheet 'Dataforchart'. And if both selections are the same e.g. US- alternative B, US- alternativeB, then simply copy that range into Dataforsheet.
How do i do that, I havent used comboboxes and option buttons so im not sure how to link it to the data....
*** ...as you will see in the data on 5th May there is no data for EUR alternatives, so its left as blank...
Say for e.g. If during calculation of Range(EURA) minus Range (JPNA)
EURA has blanks on May 5th, JPNA doesnt have any blanks....so on that day I dont want any calculation done, simply show it as a blank on Dataforchart
how do i do that?
this is the code i have for subtracting: (currently on Dataforchart I've done the USA - UKB spread. Is there another way to refer to the ranges using the Name? I tried to use range names-- USA-UKB, but that didnt work)
Sub SpreadUSA_UKB()
With Worksheets("Dataforchart").Range("DATA")
.Formula = "=ROPE!C14 - GUN!M13 "
.Value = .Value
End With
End Sub
As you can see on 'Dataforchart', it gives me some '0's around the table, why is that? and how do i get rid of it?
Please see the file as without it even i wouldnt understand what i'm talking about haha
any help or advice would be greatly appreciated,
thanks a lot
eg. on "BOLT" you can see JPN alternatives A to E, the ranges are named JPNA,JPNB,JPNC,JPND,JPNE
similarly in "HOOD" you see EUR alternatives from A-E, ranges are named EURA,EURB and so on
GUN........has ranges UKA,UKB etc
"ROPE" has ranges USA,USB,USC, etc
Now what I'm trying to do is, when I make a selection on the sheet 'chart' from the drop down boxes, I need to get the spread(difference) between the 2 selections and copy this into the Range 'DATA' in sheet 'Dataforchart'. And if both selections are the same e.g. US- alternative B, US- alternativeB, then simply copy that range into Dataforsheet.
How do i do that, I havent used comboboxes and option buttons so im not sure how to link it to the data....
*** ...as you will see in the data on 5th May there is no data for EUR alternatives, so its left as blank...
Say for e.g. If during calculation of Range(EURA) minus Range (JPNA)
EURA has blanks on May 5th, JPNA doesnt have any blanks....so on that day I dont want any calculation done, simply show it as a blank on Dataforchart
how do i do that?
this is the code i have for subtracting: (currently on Dataforchart I've done the USA - UKB spread. Is there another way to refer to the ranges using the Name? I tried to use range names-- USA-UKB, but that didnt work)
Sub SpreadUSA_UKB()
With Worksheets("Dataforchart").Range("DATA")
.Formula = "=ROPE!C14 - GUN!M13 "
.Value = .Value
End With
End Sub
As you can see on 'Dataforchart', it gives me some '0's around the table, why is that? and how do i get rid of it?
Please see the file as without it even i wouldnt understand what i'm talking about haha
any help or advice would be greatly appreciated,
thanks a lot