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

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.

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.

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

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.

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.

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 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.

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.

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