Hi! We see you’re using an ad-blocker. We’re fine with that and won’t stop you visiting the site.
But as we’re losing ad-revenue from this then why not make a donation towards website running costs?. Or you could disable your ad-blocker for this site. We think you’ll find our adverts are not overbearing!
Posted: Sun Mar 30, 2014 10:41 pm Post subject: Spreadsheet help required.
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.
Posted: Sun Mar 30, 2014 10:44 pm Post subject: Re: Spreadsheet help required.
AliOnHols wrote:
...... and it's result imported. ....
Eat's, Shoot's and Leave's. I know _________________ Garmin Nuvi 2599
Android with CamerAlert, OsmAnd+, Waze & TT Europe.
TomTom GO 730, GO 930, GO 940 & Rider2.
SatMap Active 10 & 20.
Joined: Apr 04, 2006 Posts: 10118 Location: Bexhill, South Sussex, UK
Posted: Mon Mar 31, 2014 8:25 am Post subject: Re: Spreadsheet help required.
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)
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.
Joined: Feb 27, 2006 Posts: 14902 Location: Keynsham
Posted: Mon Mar 31, 2014 3:12 pm Post subject:
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
Joined: Feb 27, 2006 Posts: 14902 Location: Keynsham
Posted: Mon Mar 31, 2014 10:32 pm Post subject:
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!!! _________________ Dennis
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.
Posted: Today Post subject: Pocket GPS Advertising
We see you’re using an ad-blocker. We’re fine with that and won’t stop you visiting the site.
Have you considered making a donation towards website running costs?. Or you could disable your ad-blocker for this site. We think you’ll find our adverts are not overbearing!
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
Or you could disable your ad-blocker for this site. We think you’ll find our adverts are not overbearing!
Hi! We see you’re using an ad-blocker. We’re fine with that and won’t stop you visiting the site.
But as we’re losing ad-revenue from this then why not make a donation towards website running costs?. Or you could disable your ad-blocker for this site. We think you’ll find our adverts are not overbearing!
Forum FAQ
Search
Usergroups
Profile
Log in for private messages
Log in
