Microsoft Excel Q&A

[McLovin]

I was planning to offer payment if it looked like something you could automate for me.

Nah, this forum is built on sharing knowledge. I'm happy to help where I can.

 

[McLovin]

Yes B is added, and is calculated from the raw live data. Probably doesn't need to be there as a column in itself. To calculate it, I used that nested IF function [edited].

And is the raw live data in a set range (ie from say row 1 to row 20) or it varies?

It might be easier to do this by PM instead of clogging this thread with minutae.

 

[Gringotts Bank]

Pm'd

 

[McLovin]

Posting here as it appears there is no method of sending attachments via PM.

A couple of things. The macro won't check to see if the code already appears in the list, so everytime the list is updated any "BUY" rows will copied over (that can be fixed relatively easily with a pivot table or just turning on auto filters. "BUY" is case sensitive, so "buy" will not be copied over. Anytime Sheet1 is modified the macro will run. It's running pretty fast at the moment.

Any questions let me know...

Code below, with my (probably less than helpful!) explanations:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim xRange As Range
Dim cl As Range
    ' Set the range in Sheet1 to be searched. In this instance the range is each cell in column A with data in it
    With Worksheets("sheet1")
    Set xRange = .Range("a2", .Range("a2").End(xlDown))
    End With
        ' Once the range is defined, for each cell in column A an IF statement is used to determine
        ' if the text string is "BUY" (nb this is case sensitive)
        ' If a "BUY" is found, then the entire row is copied into the first free row in "Sheet2"
        For Each cl In xRange
                If cl.Offset(0, 1).Value = "BUY" Then cl.EntireRow.Copy Destination:=Sheets("Sheet2").Range("a3000").End(xlUp).Offset(1, 0)
        Next cl
Application.ScreenUpdating = True
End Sub

 

[Gringotts Bank]

Thanks for your efforts McLovin.

I have to wait to get back on a computer with excel (unfortunately I don't have it on this one) before I can try it out.

 

[Uncle Festivus]

Apologies if this has been asked before.

I'm trying to speed up the process of my tax return from my trading account but my broker is lazy and aggregates trades then does a currency conversion on the aggregate. I need to know the AU amount of each row ie do a currency conversion for each row. But I have the conversion number in the E column and want to just copy this number to column M say, as per pic below.

This is basically what I want to 'code' -

IF column E starts with Transfer THEN copy the last 6 digits of column E to column M

Thanks in advance

 

[pixel]

Apologies if this has been asked before.

I'm trying to speed up the process of my tax return from my trading account but my broker is lazy and aggregates trades then does a currency conversion on the aggregate. I need to know the AU amount of each row ie do a currency conversion for each row. But I have the conversion number in the E column and want to just copy this number to column M say, as per pic below.

This is basically what I want to 'code' -

IF column E starts with Transfer THEN copy the last 6 digits of column E to column M

Thanks in advance

View attachment 46347

formula in column M: =if((left(rc5,8)="Transfer",value(right,rc5,6)),0)
(I use r1c1 addressing)

 

[McLovin]

=IF(LEFT(E1,8)="Transfer",RIGHT(E1,6))

ETA: pixel beat me...

 

[Uncle Festivus]

Wow - now that's service - thank you both

 

[skc]

In Excel I normally apply Filters to my table headings and those drop down lists allow me to easily rank the data.

Now if I have a table with streaming data coming in - is there a way to auto-sort a table?

E.g. My table has % change for ASX 200 stocks being streamed in, and I want the top movers to always appear on the top without me having to actually click the little filter arrow and select "Sort smallest to largest".

Thanks

 

[Gringotts Bank]

In Excel I normally apply Filters to my table headings and those drop down lists allow me to easily rank the data.

Now if I have a table with streaming data coming in - is there a way to auto-sort a table?

E.g. My table has % change for ASX 200 stocks being streamed in, and I want the top movers to always appear on the top without me having to actually click the little filter arrow and select "Sort smallest to largest".

Thanks

Tried a macro? Make it repeat the click every so often.

 

[Trembling Hand]

How would I get this bar chart to show negative values a different colour to positive one?

 

[skc]

How would I get this bar chart to show negative values a different colour to positive one?

View attachment 48632

Right click on the chart columns. Under Format Data Series > Fill > Invert colour if negative.

 

[prawn_86]

Good few weeks there for you TH

 

[Trembling Hand]

Right click on the chart columns. Under Format Data Series > Fill > Invert colour if negative.

Cool cept is there a way to specify the colour?

Good few weeks there for you TH

Just another few Prawn......

 

[prawn_86]

Just another few Prawn......

Or is it a graph of dinners paid for vs received?

 

[skc]

Cool cept is there a way to specify the colour?

Depends on your version.

http://peltiertech.com/WordPress/invert-if-negative-excel-2007/

http://www.ozgrid.com/Excel/invert-negative.htm

 

[boofis]

Anyone use Kinetick as a data provider? Just swapped over from eSignal and am struggling to import data from them into excel.

 

[Trembling Hand]

Hello excel gurus. if I have a data series plotted on a bar graph such as this,

='P & L'!$B$83:$B$120,'P & L'!$D$83:$D$120

Is there a way to exclude one cell, being D103?

 

[boofis]

Hello excel gurus. if I have a data series plotted on a bar graph such as this,

='P & L'!$B$83:$B$120,'P & L'!$D$83:$D$120

Is there a way to exclude one cell, being D103?

='P & L'!$B$83:$B$120,'P & L'!$D$83:$D$102,'P & L'!$D$104:$D$120

Am currently uni's macs that don't have excel so can't verify, but can't see why that shouldn't work.