Path: csiph.com!usenet.pasdenom.info!news.albasani.net!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail From: Dave Symes Newsgroups: comp.sys.acorn.misc Subject: Eureka spreadsheet question. Date: Wed, 15 Aug 2012 21:43:21 +0100 Organization: Triffids Leg Press Lines: 78 Message-ID: <52bfcd4863dave@triffid.co.uk> References: <52bb25d3c0dave@triffid.co.uk> <52bb2c6f3bbrian@bdebenham.co.uk> <52bb5b9fc3dave@triffid.co.uk> <52bf3f2945dave@triffid.co.uk> <26364ebf52.david@david.wisnia.net> <52bf7d76dddave@triffid.co.uk> <9baca5bf52.JohnyBoy@john.bryan86.fsnet.co.uk> X-Trace: individual.net LHapV8+zU/Oz6U3axlgLkQjxPm4ZpDOQrB2GpGkKOnC67K5rVrWyWmRm3UcJjlEhg0 X-Orig-Path: triffid.co.uk!dave Cancel-Lock: sha1:YQEV5KwPRCxxv/gogkO+VJHwIgM= User-Agent: Pluto/3.04e (RISC-OS/6.20) NewsHound/v1.52-32 Xref: csiph.com comp.sys.acorn.misc:6107 In article <9baca5bf52.JohnyBoy@john.bryan86.fsnet.co.uk>, John Bryan wrote: > > On 15 Aug 2012, Dave Symes wrote: > >> In article <26364ebf52.david@david.wisnia.net>, > >> Dave Wisnia at home wrote: > >>> In message <52bf3f2945dave@triffid.co.uk> > >>> Dave Symes wrote: > > [snip] > >>>> Are there any methods in a sheet whereby when the debt becomes > >>>> aged of n number of days, the answer in the cell or the > >>>> cell/colour automatically changes. > >> > >>>> For example: When the age become 31 days, then the cell flashes or > >>>> maybe changes colour to Red. > >> > >>> If the number 31 is in cell D5 for example, in the cell next to the > >>> answer (E5) put =if(D5>31,"OVERDUE!",""). Can't answer re background > >>> colour change - this will probably need a macro. > >> > >> Thanks for the thought... But. I already have an array formula in the > >> form of... > >> > >> {=IF(Dll>=31,"Debtor",IF(D11<31,"Okay"))} [Snippy] > Eureka's IF function is > =IF(logical_test,result_if_true,result_if_false) > Your line above can be simplified to: > =If(D11>=31,"Debtor","Okay") or even simpler IF(D11>=31,"Debtor",0) > The second one would require the Options-Window-display zero option > clear and the Debtor would only appear when over 31 days. You could > set the cell font colour to red for more effect. > When in Okay condition nothing shows but when in Debtor condition the > word Debtor would be in red. > Going on from Tony's reply Eureka allows negative numbers to displayed > in {Red} or [Green} or [Blue} etc using the Format-Number option: > #,###0.00:{Red}#,###.00 OR #,###0.00:{Green}#,###.00 etc Colours need to be specified in Square brackets. You can also define your own 0.00[Red] for example, but how to get it to do something useful? ` > You could use a formula in D11 such as: > =If(Today()-B11>=31,C11*-1,C11) Sorry that doesn't do anything here... Possibly because both B11 and Cll are empty cells. The only occupied cells in this test sheet are: All with Invoice date. 1/7/2012 Dll with =Today()-A11+1 E11 with {=IF(Dll>=31,"Debtor",IF(D11<31,"Okay"))} I've noted your shortened form. =If(D11>=31,"Debtor","Okay") > This would display the cell contents black when less than 31 days > and red when over 31 days the cell contents have been made negative so > that the amount would subtract from any calculation carried out using > this cell. The problem is not getting it to display in [Red] but change from Black to Red when the debt becomes aged. > Hope this is of some help Thanks Dave -- Dave Triffid