Please can I have some help with excel formulas. Thanks

[mindiawl]

Hi, I wonder if anybody could help me please. Im very good with excel but this one is beyond me.

Please take a look at my screen shot of my spreadsheet.

What im trying to do is capture data at timed trigger points.

For instance I want to use cell F4 as a trigger.

So

in cell L9 i want the info that was in G9 at 1 min before the off

in cell L10 i want the info that was in G10 at 1 min before the off

in cell L11 i want the info that was in G11 at 1 min before the off
And so on.

Is this possible by using an IF formula?? if so, please can somebody help me with the formula?

Thanks

Meurig

[spreadbetting]

Excel IF statements aren't really geared up for that sort of thing as they continually recalculate . You'd be better off using VBA and just have a routine to copy the range G9:G25 (whatever the last row is) to L9:L25 when the time in F4 was 1 min (realistically you'd need to be be plus or minus whatever your refresh rate is just to catch the time).

[mindiawl]

Thanks for this, and chance of the formula I should write for this? Thanks

[spreadbetting]

Have ago with this code I've tried to make it simple so it doesn't get confusing if you haven't used VBA

Press Alt + F11 to bring up the VBA editor and paste it into Sheet 1 or whatever the sheet's named

Code: Select all

Private Sub Worksheet_Calculate()
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event

If Range("F4").Value >= TimeValue("00:00:58") And Range("F4").Value <= TimeValue("00:01:02") Then Range("L9:L60").Value = Range("G9:G60").Value

If Range("F4").Value >= TimeValue("00:01:58") And Range("F4").Value <= TimeValue("00:02:02") Then Range("M9:M60").Value = Range("G9:G60").Value



Application.EnableEvents = True 'Turn on events again
End Sub

Obviously you can copy and amend the If statement to cover your other data points

[mindiawl]

Wow ok. No never used it but will give it a go tomorrow. Please be ready for more questions if u don't mind. Thanks

[spreadbetting]

VBA isn't any more complicated than using excel formulas once you grasp the basics. They'll probably be a load of sample sheets in the forum. Easiest way to learn is usually just by messing around with those to see what various routines do.

Done properly you'd use routines like Case to select your time bands rather than IF, but IF's are easier to understand coming straight from excel formulas. Probably also want to code in routines to clear your data when you change markets and select the last row in Column L to get your range rather than a block to 60, but memory wise they'd be nothing in it and no point over complicating things

[mindiawl]

So very similar to HTML coding?

[LinusP]

This one does everything..

viewtopic.php?f=31&t=8896&hilit=data+capture

This one is better for modifying..

viewtopic.php?f=31&t=2385&hilit=data+capture

[OnGoldWires]

Apologies if I merely add confusion to the thread, but this can be done in Excel without a Macro/VBA.
But you need to grasp the concept of circular references. A circular reference is a formula which refers to its own value. This is something that Excel only reluctantly lets you do. You have to go into:

Options->Formulas->
Tick "Enable iterative calculation"
Change "Maximum iterations" to 1.

Now excel will allow circular references, but it is always a good idea to add a "reset" or "default" value to avoid errors. Otherwise your cell could end up in an endless error loop. I usually name a cell "reset" and include it in my formula.

I have written a formula which will reset to 0 if the reset cell is 1. Otherwise it will display the current until the trigger time and then it will not change until reset.

I have a cell named reset, a cell named time which contains a formula for the time, a cell named racetime which is the off time and a cell named delay which contains the trigger time, which in your case would be a minute.

=IF(reset,0,IF(racetime-time>delay,current_odds,self))

Obviously you might prefer to write a formula in reset so that you don't have to manually reset.

Hope this helps.

[mindiawl]

By doing this it would only affect the spreadsheet I'm working on yes or could it affect all other future spreadsheets?

[spreadbetting]

The VBA code is entered into that workbook only , if other sheets are being opened and closed whilst VBA is firing it can sometimes go across workbooks so it's generally good practice to reference cells exactly.

Obviously I've no idea what you've called your workbooks or sheets so just used things like Range("F4").Value for simplicity.

Usually you just use "With ThisWorkbook" at the start of the routine and end it with "End With". So we're effectively telling the code to only fire on the workbook within which it is written. Obviously workbooks can contain worksheets so to ensure coding applies to the sheets we want it to we reference those correctly as such , assuming our sheet is named Sheet1, - ".Sheets("Sheet1").Range("F4").Value". The dot before .Sheets is important to add as that's what ties it to the ThisWorkbook statement

So all in it'd be like, for code to run on Sheet1 in the workbook (which we could also reference if needed)

Code: Select all

Private Sub Worksheet_Calculate()
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
With ThisWorkbook


If .Sheets("Sheet1").Range("F4").Value >= TimeValue("00:00:58") And .Sheets("Sheet1").Range("F4").Value <= TimeValue("00:01:02") Then .Sheets("Sheet1").Range("L9:L60").Value = .Sheets("Sheet1").Range("G9:G60").Value

If .Sheets("Sheet1").Range("F4").Value >= TimeValue("00:01:58") And .Sheets("Sheet1").Range("F4").Value <= TimeValue("00:02:02") Then .Sheets("Sheet1").Range("M9:M60").Value = .Sheets("Sheet1").Range("G9:G60").Value


End With

Application.EnableEvents = True 'Turn on events again
End Sub

[mindiawl]

Wow your help is fantastic. I'm going to have a look at this later and try and get my head around it. Again thanks ever so much.

[spreadbetting]

No problem, only reason I know some limited VBA is because others have helped me in the past so just passing on their knowledge not mine.

One thing I would say is it's worth spending time to understand the basics early on rather than jumping in headfirst as it will pay off in the long run. So it is worth learning all the options to reference cells within sheets exactly so things only fire when you want them to. VBA offers far more options than formulas and once you start using sub routines to only run when required it can make your sheets faster and more efficient.

The other good thing about VBA is there are so many helpful websites out there it doesn't usually take long to find out where you've gone wrong. There's probably plenty of VBA experts on the forum who are happy to help also.