excel formula - possibly visual basic method David 21.09.2006 20:04:02 I need to set up a quite a complicated spread sheet. This is the senario: say my sales figure for monday is £50 (which is my target). However my actual sales figure for monday is £40. How do i get the cell to show the difference between my forecast(target) and my actual having predefined the cell with my target sales figure so that when i type the actual sales figure in it will automatically show the difference(+/-). How do i predefine the cell with the forecast sales figure for a cell? In addition to this i would like to know how if i hit the sales target do i get the cell to show green. And if the sales target is missed how do i get the cell to show red without having to change the cell each time. What hopefully should happen is that as in the above scenario if for the monday i typed in the actual sales figure as £40, the cell will hopefully show -£10 with the cell baground turning red. If for example i typed in £60 for monday it would show +£10 and the cell would turn green. I hope to repeat this process for the rest of the week with different forecast figures! Many thanks

RE: excel formula - possibly visual basic method Marcelo 21.09.2006 20:22:01 hi David, assuming that you have the actual figures on the B column, the forecast figures on the C column and the variance on the D column. you can hide the forecast column if it's necessary for your proposal. on D column use the formula =b2-c2 and use a conditional format to set the cell red or green: click on format menu chose conditional format, on the drop down chose bigger than - on the box type 0 format as you need color, font color, borders add an new condition chose lower than - on the box type 0 hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "David" escreveu: [Quoted Text] > I need to set up a quite a complicated spread sheet. This is the senario: say > my sales figure for monday is £50 (which is my target). However my actual > sales figure for monday is £40. How do i get the cell to show the difference > between my forecast(target) and my actual having predefined the cell with my > target sales figure so that when i type the actual sales figure in it will > automatically show the difference(+/-). How do i predefine the cell with the > forecast sales figure for a cell? > > In addition to this i would like to know how if i hit the sales target do i > get the cell to show green. And if the sales target is missed how do i get > the cell to show red without having to change the cell each time. > > What hopefully should happen is that as in the above scenario if for the > monday i typed in the actual sales figure as £40, the cell will hopefully > show -£10 with the cell baground turning red. If for example i typed in £60 > for monday it would show +£10 and the cell would turn green. I hope to repeat > this process for the rest of the week with different forecast figures! Many > thanks > > > >

Re: excel formula - possibly visual basic method Gord Dibben <gorddibbATshawDOTca> 21.09.2006 20:55:21 David Without VBA you can have a value or a formula in a cell, not both at the same time. I would stick my target number in a cell, say E1 In A1 I would enter the Monday sales figure of 40 In B1 I would enter =A1-E1 Select B1 and Format>Conditional Formatting>Condition1>Formula is =B1<E1 format to red Add>Condition2>Formula is =B1>=E1 format to green. These formats can be copied to other cells by using the formatting paintbrush. If you wnat to do it all in one cell event code is your friend. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value <> "" Then Excel.Range("B" & n).Value = Excel.Range("B" & n).Value _ - Excel.Range("E1") End If End If enditall: Application.EnableEvents = True End Sub E1 contains your daily target. Column B is where you enter the daily sales figures. Also place the CF on column B cells. Gord Dibben MS Excel MVP On Thu, 21 Sep 2006 13:04:02 -0700, David <David[ at ]discussions.microsoft.com> wrote: [Quoted Text] >I need to set up a quite a complicated spread sheet. This is the senario: say >my sales figure for monday is £50 (which is my target). However my actual >sales figure for monday is £40. How do i get the cell to show the difference >between my forecast(target) and my actual having predefined the cell with my >target sales figure so that when i type the actual sales figure in it will >automatically show the difference(+/-). How do i predefine the cell with the >forecast sales figure for a cell? > >In addition to this i would like to know how if i hit the sales target do i >get the cell to show green. And if the sales target is missed how do i get >the cell to show red without having to change the cell each time. > >What hopefully should happen is that as in the above scenario if for the >monday i typed in the actual sales figure as £40, the cell will hopefully >show -£10 with the cell baground turning red. If for example i typed in £60 >for monday it would show +£10 and the cell would turn green. I hope to repeat >this process for the rest of the week with different forecast figures! Many >thanks > > >