Excel help please

very briefly (and assuming you know a fair bit of excel/vba)

Use the "Worksheet Change" event (or selection change if c32 is selected during update).

Store new/prev value on another worksheet and base your graph on that data.

I'm guessing though that you don't know that much excel/vba because if you did, the easiest thing would be to do this automatically everytime the data is updated.

If you want, I'll have a look at it for you - send it to triangulartriangle@hotmail.co.uk

ta

Thanks yyw. You're right, my VBA skills are very limited. I can run a few simple macros, but writing code is not my thing. Looked at Worksheet Change Event on Google and that looks beyond me at first glance. If it's too complicated I can live without it. I'm a bit reluctant to send the worksheet. Cheers.

Hi dave b, If you still want to do it follow these steps.

Try to do this exactly ~
Turn off updating for cell C32
Copy this formula
=OFFSET($AA$2,,,COUNT($AA$2:$AA$500))
Select Cell AA2
Go to name manager, click new, in the name box type Graphit
In the refers to box paste the formula you copied, click OK

Now copy this ~

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("C32")  Range("AA5000").End(xlUp).Offset(0, 0) Then
Range("AA5000").End(xlUp).Offset(1, 0) = Range("C32")
End If
End Sub

Now do this
Select the developer tab, select VBA, double click on the name of the worksheet you are in (It's under project VBA on the left)
In the module that appears, paste what you copied, now because the does not equal sign won't show
what is typed in this line of code ~ If Range("C32")  Range("AA5000").End(xlUp).Offset(0, 0) Then
After this part of the code ~  If Range("C32") put the insertion blinker in and type the does not equal symbols ~ Shift comma and Shift fullstop. Now close (exit VBA)

Now in cell AA1 type Graph. Cell AA2 should show what is in cell C32, now select both cells AA1 and AA2
Go to the insert menu and click the table icon.

Now go to the insert menu and click the column graph, then click on the first column icon.
Now go to the menu and click select data, now click on cell AA2, click ok.
That is it. You can now turn on updating for cell C32 and you will have a dynamic chart, resize and
move the the chart to your liking.

When you wish to start again select cell AA2 and enter the value that is in cell C32, then select
all the cells with values in them starting from cell AA3 and down, go to the delete menu, click delete table rows.

Cheers.

Thanks a lot Compound Magic, that's a big help. The column data doesn't seem to be updating though without me clicking somewhere on the sheet. It may have something to do with me using Excel 2003. I've tried tweaking but the sheet keeps crashing. I'll keep trying till I get it though. Thanks again for your time.

Hi Compound Magic, if you're still about. I've tried hopelessly to get the AV column (I've used this column as AA is busy) to update automatically. As mentioned the only way to get the data to update in the AV column is to click on a cell in the worksheet. If you have any ideas, it would save a lot of frustration. Cheers.

Hi dave b,

I have not got excel 2003 and have never had it, so I can't test.

Did you change this formula reference in the name manager from ~
Range("AA5000").End(xlUp).Offset(1, 0) = Range("C32")
to
Range("AV5000").End(xlUp).Offset(1, 0) = Range("C32")

And also the reference in  you sub module from ~
If Range("C32")  Range("AA5000").End(xlUp).Offset(0, 0)Then
To
If Range("C32")##Range("AV5000").End(xlUp).Offset(0, 0)Then (with the Does not equal symbols inserted Where my hash marks are)
Cheers

I have changed the references in my version of excel 2007 and tested it with Gruss updating it's spreadsheet
and all is working fine.

Sorry I can't be of further assistance.

Cheers.

dave b

Update

All is not working fine!!

It's now telling me "A formula in this worksheet contains one or more invalid references"

So I am having a play with it to see if I can fix/find the problem.

dave b

Still no joy, so have made a query on MR Excel to see if they can help.

Will post again when/if I get some answers.

Hi dave b,

Got my answer to my post on Mr Excel which explains why it was working for me in my own code but when hooked up to
Betting Assistant it didn't.

http://www.mrexcel.com/forum/showthread.php?t=589096

Sorry about that.

Well I tried!
Cheers

Thanks for all your help and effort Compound Magic - it's appreciated!

I was thinking could a macro be written that when "C32"  the last number in range "AV" then cells "D32" then "E32" are chosen, thus acivating the update in column "AV"? Not sure how the macro would be written at the moment though.

Cheers.

..."C32" does not equal the last number...

dave b

Will be offline for quite a while but I have some ideas I am going to try.
Had a google and found this that may be helpful. Using a change event to trigger a macro as in this thread ~
http://www.ozgrid.com/VBA/run-macros-change.htm

Cheers.

Hi dave b

Finally solved the problem and have got it all working.

I don't know where your Cell C32 is getting its information from, but this cell must be triggered
by excel and not by an API call.

Have posted all the code that makes it work in Mr Excel ~
http://www.mrexcel.com/forum/showthread.php?t=589096

You would have to change the references to suit.

Cheers.

Hi Compound Magic,

I've tried replacing the old code with the new code (changing "M" to "AV" - I think that's all I have to do for the new code?) but nothing is happening. Column AV is not even updating if I click on the sheet.

I take it that I don't need to use the first bit of code in Mr Excel? Inputs to my workbook are via API but then get reworked several times within Excel to produce C32.

I have run out of time now and won't be able to look at it again for a day or two. I'll have another go when I have more time.

Thanks again for your help. I'll let you know how I get on when I try again. Cheers.

I've not been able to get this working, column AV not updating with the new code. Thanks again for your help Compound Magic.

Dave,

    did you get a solution?

If not, you could PM me your email address and I will send you a spreadsheet that collects and stores the information from a designated cell as it changes.

Gitmo,
Thanks for your kind offer. PM sent.
Cheers

Sorry for delay in getting back to you spreadsheet sent now.