Home PageFacebookRSS News Feed
PocketGPS
Web
SatNav,GPS,Navigation
Pocket GPS World - SatNavs | GPS | Speed Cameras: Forums

Pocket GPS World :: View topic - Spreadsheet help required.
 Forum FAQForum FAQ   SearchSearch   UsergroupsUsergroups   ProfileProfile   Log in for private messagesLog in for private messages   Log inLog in 

Spreadsheet help required.

 
Post new topic   Reply to topic    Pocket GPS World Forum Index -> Lost Satellite Fix - Off Topic Area
View previous topic :: View next topic  
Author Message
AliOnHols
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Oct 15, 2008
Posts: 1936

PostPosted: Sun Mar 30, 2014 10:41 pm    Post subject: Spreadsheet help required. Reply with quote

I am pulling my hair out over a formula in a spreadsheet I am trying to create.

Cell D4 is a variable number inputted manually. It can be greater or less than it's current value. Cell H4 records D4's maximum value and only updates when D4 is greater than H4, it then retains this value until D4 is again greater than H4. Cells E4, F4 & G4 contain text.

Does anybody know if it is possible to insert a formula into H4 to do this please? And if so, would you share the formula with me please.?

My efforts result in loops which implies to me that the formula is best carried out in a Cell elsewhere on the spreadsheet and it's result imported. I'd like to avoid this if possible.

Thank you.
_________________
Garmin Nuvi 2599
Android with CamerAlert, OsmAnd+, Waze & TT Europe.
TomTom GO 730, GO 930, GO 940 & Rider2.
SatMap Active 10 & 20.
Back to top
View user's profile Send private message
AliOnHols
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Oct 15, 2008
Posts: 1936

PostPosted: Sun Mar 30, 2014 10:44 pm    Post subject: Re: Spreadsheet help required. Reply with quote

AliOnHols wrote:
...... and it's result imported. ....
Eat's, Shoot's and Leave's. I know Embarassed
_________________
Garmin Nuvi 2599
Android with CamerAlert, OsmAnd+, Waze & TT Europe.
TomTom GO 730, GO 930, GO 940 & Rider2.
SatMap Active 10 & 20.
Back to top
View user's profile Send private message
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7037
Location: Reading

PostPosted: Mon Mar 31, 2014 6:40 am    Post subject: Reply with quote

Took you all of 3 minutes to sort and post back Very Happy

If I have formula grief I tend to write a macro so I can watch it run through if necessary.
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Mon Mar 31, 2014 8:25 am    Post subject: Re: Spreadsheet help required. Reply with quote

AliOnHols wrote:
Does anybody know if it is possible to insert a formula into H4 to do this please? And if so, would you share the formula with me please.?
No, you can't do that with a simple cell formula. As you have found, it is because you end up with circular references. Like Kremmen said, a macro to do that, triggered by the sheet 'On Entry' event, would be easy to do, which of course I'll share with you.
What version of Excel do you have Ali?

Function UpdateCell
If Cells(4, 4) gt Cells(4, 8) Then ' Replace the gt with the greater than symbol
Cells(4, 8) = Cells(4, 4)
End If
End Function

Then a routine that sets the code to run when you enter anything on the sheet.

Sub auto_open()
ActiveWorkbook.Worksheets(1).OnEntry = " UpdateCell"
End Sub


I'll do it for you later and email you the sheet if you want me to. You also have to enable macros for the sheet (depending on version)
The Cells cell definition is in the form Cells(Row, Column)
Back to top
View user's profile Send private message
AliOnHols
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Oct 15, 2008
Posts: 1936

PostPosted: Mon Mar 31, 2014 9:57 am    Post subject: Reply with quote

Many thanks for your replies Kremmen and M8TJT.

Doh! *Holds Head in Hands*, I know even less about Macros than I know about Formulas.

Very kind of you to offer to email me a solution M8TJT. It is much appreciated.

Not being a Windows user I don't have Excell. I use LibreOffice Calc 4.0 (I like making life difficult don't I?). I have checked and it can import .xls and .xlsx Excel files.

I'll plod on for a while with the information you have already given me whilst researching macros on the Net.

Thanks again. Ali.
_________________
Garmin Nuvi 2599
Android with CamerAlert, OsmAnd+, Waze & TT Europe.
TomTom GO 730, GO 930, GO 940 & Rider2.
SatMap Active 10 & 20.
Back to top
View user's profile Send private message
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7037
Location: Reading

PostPosted: Mon Mar 31, 2014 10:13 am    Post subject: Reply with quote

Easiest way to get started with Macro's is to record one (in the developer tab) and then play with the code.

You may need to tinker with the settings to get the developer tab to show.

If you haven't got Excel though it may work differently.
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
DennisN
Tired Old Man
Tired Old Man


Joined: Feb 27, 2006
Posts: 14888
Location: Keynsham

PostPosted: Mon Mar 31, 2014 3:12 pm    Post subject: Reply with quote

Using Lotus 123 (and I'm sure Excel will do something similar), use THREE cells as follows...

D4 variable input number
G1 @IF(D4gtH4,+D4,H4) (insert Greater than sign in place of gt)
H4 @VALUE(G1)

Use any cell in place of G1.

It's not particularly good - the value in H4 will only ever increase (you can't get it back down to nothing except by temporarily putting a comma at the front of the formula in H4 to make it text, hence value zero). It's not something I would write into a textbook, but it works if you have some idea what you're doing.
_________________
Dennis

If it tastes good - it's fattening.

Two of them are obesiting!!
Back to top
View user's profile Send private message
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Mon Mar 31, 2014 6:53 pm    Post subject: Reply with quote

You get a circular reference error in Excel as cell G1 refers back to itself in cell H1.
What does the VALUE in VALUE(G1) actually do in L123?
Back to top
View user's profile Send private message
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Mon Mar 31, 2014 6:55 pm    Post subject: Reply with quote

Kremmen wrote:
Easiest way to get started with Macro's is to record one (in the developer tab) and then play with the code.
Good way to get started, but you can't record loops or really IF, THEN structure. But you knew that anyway Very Happy
Back to top
View user's profile Send private message
DennisN
Tired Old Man
Tired Old Man


Joined: Feb 27, 2006
Posts: 14888
Location: Keynsham

PostPosted: Mon Mar 31, 2014 10:32 pm    Post subject: Reply with quote

M8TJT wrote:
You get a circular reference error in Excel as cell G1 refers back to itself in cell H1.
What does the VALUE in VALUE(G1) actually do in L123?

It returns the value of G1, rather than being a formula (I originally simply said +G1, which circulared too).

You can stop the circular reference in Excel by Preferences Iterations and change it to 1. But I'm no Excel guru, I hate the damn thing, but don't have any choice since Lotus (IBM owned now) never produced a Mac version (and have not upgraded 123 since 2000 - it's still better than Excel nevertheless!!).

And I'm no longer a lotus 123 guru either. But back along (25 years or more) when I was a bit guru-ish, there were two macros which "they" used to run at lunchtime, first one for two hours, then started the second one for another hour or more. The base details had to be amended and I spent two weeks trying to follow the macros - they were enormous, long things - I managed a couple of printouts of them using a program called Sideways and they were each 12 feet long! Then gave up and instead, simply wrote a set of @DSUMs, and changed the macro to go to a part of the spreadsheet where I wrote "Wait, calculating..." and had it sit around displaying that for two minutes, then printed the results. The whole task took only that two minutes, which I inserted simply because they wouldn't have believed it was instant!!! Laughing
_________________
Dennis

If it tastes good - it's fattening.

Two of them are obesiting!!
Back to top
View user's profile Send private message
AliOnHols
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Oct 15, 2008
Posts: 1936

PostPosted: Tue Apr 01, 2014 12:05 am    Post subject: Reply with quote

HI Everyone, thank you all for your advice, I am not ignoring you.

I am following an on-line guide on creating macros, so far I have learned to print my name and say "Hello". The trouble is I am unable to get the Macros to save and I have been going Boggled Eyed trying to sort it out so I have decided to continue with the rest of the Spreadsheet to get that up and running first and then I can sort the macros out later.

DennisN, That looks like a nice, quick workaround, I have tried it but sadly it doesn't work for me. I have substituted some of the symbols to try alternatives but no go.

Thanks to All.
_________________
Garmin Nuvi 2599
Android with CamerAlert, OsmAnd+, Waze & TT Europe.
TomTom GO 730, GO 930, GO 940 & Rider2.
SatMap Active 10 & 20.
Back to top
View user's profile Send private message







Posted: Today    Post subject: Pocket GPS Advertising

Back to top
Display posts from previous:   
Post new topic   Reply to topic    Pocket GPS World Forum Index -> Lost Satellite Fix - Off Topic Area All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Make a Donation



CamerAlert Database

Click here for the PocketGPSWorld.com Speed Camera Database

Download Speed Camera Database
22.034 (27 Mar 24)



WORLDWIDE SPEED CAMERA SPOTTERS WANTED!

Click here to submit camera positions to the PocketGPSWorld.com Speed Camera Database


12mth Subscriber memberships awarded every week for verified new camera reports!

Submit Speed Camera Locations Now


CamerAlert Apps



iOS QR Code






Android QR Code







© Terms & Privacy


GPS Shopping