New Related Question - WAS: Wierd Formula Request

In regards to the former discussion (http://tinyurl.com/762vq62) which
worked great by the way (Thanks to all who helped!), how could I
change the Case statement to populate a cell based any change in
value, not just a specific text entry?

A current example based on specific text:

      Case "SENT", Range("Q" & n) = ""
       Range("Q" & n) = Format(Date, "mm-dd-yyyy")

Thanks in advance for your help!

magmike
0
magmike7 (50)
12/7/2011 8:11:20 PM
excel 39879 articles. 2 followers. Follow

6 Replies
375 Views

Similar Articles

[PageSpeed] 20

On Dec 7, 2:11=A0pm, magmike <magmi...@yahoo.com> wrote:
> In regards to the former discussion (http://tinyurl.com/762vq62) which
> worked great by the way (Thanks to all who helped!), how could I
> change the Case statement to populate a cell based any change in
> value, not just a specific text entry?
>
> A current example based on specific text:
>
> =A0 =A0 =A0 Case "SENT", Range("Q" & n) =3D ""
> =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> Thanks in advance for your help!
>
> magmike

You need to clarify what you want.with more explanation.
Send your file with a complete explanation and before/after examples
to dguillett1        @gmail.com





0
dguillett11 (103)
12/7/2011 11:54:17 PM
On Dec 7, 5:54=A0pm, Don Guillett <dguille...@gmail.com> wrote:
> On Dec 7, 2:11=A0pm, magmike <magmi...@yahoo.com> wrote:
>
> > In regards to the former discussion (http://tinyurl.com/762vq62) which
> > worked great by the way (Thanks to all who helped!), how could I
> > change the Case statement to populate a cell based any change in
> > value, not just a specific text entry?
>
> > A current example based on specific text:
>
> > =A0 =A0 =A0 Case "SENT", Range("Q" & n) =3D ""
> > =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> > Thanks in advance for your help!
>
> > magmike
>
> You need to clarify what you want.with more explanation.
> Send your file with a complete explanation and before/after examples
> to dguillett1 =A0 =A0 =A0 =A0@gmail.com

Don,

The complete code and explanation was at the link given related to
that post chain. However, I will post the result code of that post
here:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim n As Long, s As String
  On Error GoTo enditall
  If Target.Column =3D 5 Then  '1 is column A
    Application.EnableEvents =3D False
    n =3D Target.Row: s =3D UCase$(Target)

    With Range("N" & n)
      If IsEmpty(.Value) Then
         .Value =3D Format(Date, "mm-dd-yyyy")
      End If
    End With

    Select Case s
      Case "IN", Range("O" & n) =3D ""
       Range("O" & n) =3D Format(Date, "mm-dd-yyyy")


      Case "QUOTE", Range("P" & n) =3D ""
       Range("P" & n) =3D Format(Date, "mm-dd-yyyy")

      Case "EMAIL", Range("P" & n) =3D ""
       Range("P" & n) =3D Format(Date, "mm-dd-yyyy")

      Case "SENT", Range("Q" & n) =3D ""
       Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")


      Case "REQ", Range("R" & n) =3D ""
       Range("R" & n) =3D Format(Date, "mm-dd-yyyy")


      Case "DONE", Range("S" & n) =3D ""
       Range("S" & n) =3D Format(Date, "mm-dd-yyyy")
    End Select


enditall:
    Application.EnableEvents =3D True
  End If
End Sub

One additional feature I am seaking here, is that when the cell in
question $E(?) (i.e. E5 or E435 - for this example we will use E5) has
any change whatsoever, that T5 would then populate with today's date
marking the last date a change was made to cell E5.

In the above code, specific columns populate the date based on a
specific word. For example, when the word "DONE" is typed into E5,
then S5 populates with the date on which that value was entered into
E5 and then always remains the same. The first time any value is
entered into E5, N5 is populated with that day's date, showing the
first date a value was entered into E5. Now I am designating T5 as a
field that will show the last date a change was made to the value of
E5.

Hope that helps,

magmike
0
magmike7 (50)
12/8/2011 4:49:50 PM
On Dec 8, 10:49=A0am, magmike <magmi...@yahoo.com> wrote:
> On Dec 7, 5:54=A0pm, Don Guillett <dguille...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Dec 7, 2:11=A0pm, magmike <magmi...@yahoo.com> wrote:
>
> > > In regards to the former discussion (http://tinyurl.com/762vq62) whic=
h
> > > worked great by the way (Thanks to all who helped!), how could I
> > > change the Case statement to populate a cell based any change in
> > > value, not just a specific text entry?
>
> > > A current example based on specific text:
>
> > > =A0 =A0 =A0 Case "SENT", Range("Q" & n) =3D ""
> > > =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> > > Thanks in advance for your help!
>
> > > magmike
>
> > You need to clarify what you want.with more explanation.
> > Send your file with a complete explanation and before/after examples
> > to dguillett1 =A0 =A0 =A0 =A0@gmail.com
>
> Don,
>
> The complete code and explanation was at the link given related to
> that post chain. However, I will post the result code of that post
> here:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> =A0 Dim n As Long, s As String
> =A0 On Error GoTo enditall
> =A0 If Target.Column =3D 5 Then =A0'1 is column A
> =A0 =A0 Application.EnableEvents =3D False
> =A0 =A0 n =3D Target.Row: s =3D UCase$(Target)
>
> =A0 =A0 With Range("N" & n)
> =A0 =A0 =A0 If IsEmpty(.Value) Then
> =A0 =A0 =A0 =A0 =A0.Value =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 =A0 End If
> =A0 =A0 End With
>
> =A0 =A0 Select Case s
> =A0 =A0 =A0 Case "IN", Range("O" & n) =3D ""
> =A0 =A0 =A0 =A0Range("O" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "QUOTE", Range("P" & n) =3D ""
> =A0 =A0 =A0 =A0Range("P" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "EMAIL", Range("P" & n) =3D ""
> =A0 =A0 =A0 =A0Range("P" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "SENT", Range("Q" & n) =3D ""
> =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "REQ", Range("R" & n) =3D ""
> =A0 =A0 =A0 =A0Range("R" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "DONE", Range("S" & n) =3D ""
> =A0 =A0 =A0 =A0Range("S" & n) =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 End Select
>
> enditall:
> =A0 =A0 Application.EnableEvents =3D True
> =A0 End If
> End Sub
>
> One additional feature I am seaking here, is that when the cell in
> question $E(?) (i.e. E5 or E435 - for this example we will use E5) has
> any change whatsoever, that T5 would then populate with today's date
> marking the last date a change was made to cell E5.
>
> In the above code, specific columns populate the date based on a
> specific word. For example, when the word "DONE" is typed into E5,
> then S5 populates with the date on which that value was entered into
> E5 and then always remains the same. The first time any value is
> entered into E5, N5 is populated with that day's date, showing the
> first date a value was entered into E5. Now I am designating T5 as a
> field that will show the last date a change was made to the value of
> E5.
>
> Hope that helps,
>
> magmike

I  would write it like this and put in the SHEET module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Or Target.Column <> 5 Then Exit Sub
    Cells(Target.Row, "M") =3D Format(Date, "mm-dd-yyyy")
    On Error GoTo mymsg
     Select Case UCase(Target)
        Case "IN": x =3D "O"
        Case "QUOTE", "EMAIL": x =3D "P"
        Case "SENT": x =3D "Q"
        Case "REQ": x =3D "R"
        Case "DONE": x =3D "S"
      End Select
    Cells(Target.Row, x) =3D Format(Date, "mm-dd-yyyy")
    Cells(Target.Row, "T") =3D _
   Format(Application.Max(Cells(Target.Row, "M").Resize(, 7)), "mm-dd-
yyyy")
    Exit Sub
mymsg: MsgBox ("Not there, How about using data validation")
End Sub
0
dguillett11 (103)
12/9/2011 8:14:56 PM
On Dec 8, 10:49=A0am, magmike <magmi...@yahoo.com> wrote:
> On Dec 7, 5:54=A0pm, Don Guillett <dguille...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Dec 7, 2:11=A0pm, magmike <magmi...@yahoo.com> wrote:
>
> > > In regards to the former discussion (http://tinyurl.com/762vq62) whic=
h
> > > worked great by the way (Thanks to all who helped!), how could I
> > > change the Case statement to populate a cell based any change in
> > > value, not just a specific text entry?
>
> > > A current example based on specific text:
>
> > > =A0 =A0 =A0 Case "SENT", Range("Q" & n) =3D ""
> > > =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> > > Thanks in advance for your help!
>
> > > magmike
>
> > You need to clarify what you want.with more explanation.
> > Send your file with a complete explanation and before/after examples
> > to dguillett1 =A0 =A0 =A0 =A0@gmail.com
>
> Don,
>
> The complete code and explanation was at the link given related to
> that post chain. However, I will post the result code of that post
> here:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> =A0 Dim n As Long, s As String
> =A0 On Error GoTo enditall
> =A0 If Target.Column =3D 5 Then =A0'1 is column A
> =A0 =A0 Application.EnableEvents =3D False
> =A0 =A0 n =3D Target.Row: s =3D UCase$(Target)
>
> =A0 =A0 With Range("N" & n)
> =A0 =A0 =A0 If IsEmpty(.Value) Then
> =A0 =A0 =A0 =A0 =A0.Value =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 =A0 End If
> =A0 =A0 End With
>
> =A0 =A0 Select Case s
> =A0 =A0 =A0 Case "IN", Range("O" & n) =3D ""
> =A0 =A0 =A0 =A0Range("O" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "QUOTE", Range("P" & n) =3D ""
> =A0 =A0 =A0 =A0Range("P" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "EMAIL", Range("P" & n) =3D ""
> =A0 =A0 =A0 =A0Range("P" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "SENT", Range("Q" & n) =3D ""
> =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "REQ", Range("R" & n) =3D ""
> =A0 =A0 =A0 =A0Range("R" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "DONE", Range("S" & n) =3D ""
> =A0 =A0 =A0 =A0Range("S" & n) =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 End Select
>
> enditall:
> =A0 =A0 Application.EnableEvents =3D True
> =A0 End If
> End Sub
>
> One additional feature I am seaking here, is that when the cell in
> question $E(?) (i.e. E5 or E435 - for this example we will use E5) has
> any change whatsoever, that T5 would then populate with today's date
> marking the last date a change was made to cell E5.
>
> In the above code, specific columns populate the date based on a
> specific word. For example, when the word "DONE" is typed into E5,
> then S5 populates with the date on which that value was entered into
> E5 and then always remains the same. The first time any value is
> entered into E5, N5 is populated with that day's date, showing the
> first date a value was entered into E5. Now I am designating T5 as a
> field that will show the last date a change was made to the value of
> E5.
>
> Hope that helps,
>
> magmike

Guess my ans didn't go thru so repeat\

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Or Target.Column <> 5 Then Exit Sub
    Cells(Target.Row, "M") =3D Format(Date, "mm-dd-yyyy")
    On Error GoTo mymsg
    Select Case UCase(Target)
      Case "IN": x =3D "O"
      Case "QUOTE", "EMAIL": x =3D "P"
      Case "SENT": x =3D "Q"
      Case "REQ": x =3D "R"
      Case "DONE": x =3D "S"
      End Select
    Cells(Target.Row, x) =3D Format(Date, "mm-dd-yyyy")
   Cells(Target.Row, "T") =3D _
   Format(Application.Max(Cells(Target.Row, "M").Resize(, 7)), "mm-dd-
yyyy")
    Exit Sub
mymsg: MsgBox ("Not there, How about using data validation")
End Sub



0
dguillett11 (103)
12/9/2011 8:18:06 PM
On Dec 9, 2:14=A0pm, Don Guillett <dguille...@gmail.com> wrote:
> On Dec 8, 10:49=A0am, magmike <magmi...@yahoo.com> wrote:
>
>
>
>
>
> > On Dec 7, 5:54=A0pm, Don Guillett <dguille...@gmail.com> wrote:
>
> > > On Dec 7, 2:11=A0pm, magmike <magmi...@yahoo.com> wrote:
>
> > > > In regards to the former discussion (http://tinyurl.com/762vq62) wh=
ich
> > > > worked great by the way (Thanks to all who helped!), how could I
> > > > change the Case statement to populate a cell based any change in
> > > > value, not just a specific text entry?
>
> > > > A current example based on specific text:
>
> > > > =A0 =A0 =A0 Case "SENT", Range("Q" & n) =3D ""
> > > > =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> > > > Thanks in advance for your help!
>
> > > > magmike
>
> > > You need to clarify what you want.with more explanation.
> > > Send your file with a complete explanation and before/after examples
> > > to dguillett1 =A0 =A0 =A0 =A0@gmail.com
>
> > Don,
>
> > The complete code and explanation was at the link given related to
> > that post chain. However, I will post the result code of that post
> > here:
>
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > =A0 Dim n As Long, s As String
> > =A0 On Error GoTo enditall
> > =A0 If Target.Column =3D 5 Then =A0'1 is column A
> > =A0 =A0 Application.EnableEvents =3D False
> > =A0 =A0 n =3D Target.Row: s =3D UCase$(Target)
>
> > =A0 =A0 With Range("N" & n)
> > =A0 =A0 =A0 If IsEmpty(.Value) Then
> > =A0 =A0 =A0 =A0 =A0.Value =3D Format(Date, "mm-dd-yyyy")
> > =A0 =A0 =A0 End If
> > =A0 =A0 End With
>
> > =A0 =A0 Select Case s
> > =A0 =A0 =A0 Case "IN", Range("O" & n) =3D ""
> > =A0 =A0 =A0 =A0Range("O" & n) =3D Format(Date, "mm-dd-yyyy")
>
> > =A0 =A0 =A0 Case "QUOTE", Range("P" & n) =3D ""
> > =A0 =A0 =A0 =A0Range("P" & n) =3D Format(Date, "mm-dd-yyyy")
>
> > =A0 =A0 =A0 Case "EMAIL", Range("P" & n) =3D ""
> > =A0 =A0 =A0 =A0Range("P" & n) =3D Format(Date, "mm-dd-yyyy")
>
> > =A0 =A0 =A0 Case "SENT", Range("Q" & n) =3D ""
> > =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> > =A0 =A0 =A0 Case "REQ", Range("R" & n) =3D ""
> > =A0 =A0 =A0 =A0Range("R" & n) =3D Format(Date, "mm-dd-yyyy")
>
> > =A0 =A0 =A0 Case "DONE", Range("S" & n) =3D ""
> > =A0 =A0 =A0 =A0Range("S" & n) =3D Format(Date, "mm-dd-yyyy")
> > =A0 =A0 End Select
>
> > enditall:
> > =A0 =A0 Application.EnableEvents =3D True
> > =A0 End If
> > End Sub
>
> > One additional feature I am seaking here, is that when the cell in
> > question $E(?) (i.e. E5 or E435 - for this example we will use E5) has
> > any change whatsoever, that T5 would then populate with today's date
> > marking the last date a change was made to cell E5.
>
> > In the above code, specific columns populate the date based on a
> > specific word. For example, when the word "DONE" is typed into E5,
> > then S5 populates with the date on which that value was entered into
> > E5 and then always remains the same. The first time any value is
> > entered into E5, N5 is populated with that day's date, showing the
> > first date a value was entered into E5. Now I am designating T5 as a
> > field that will show the last date a change was made to the value of
> > E5.
>
> > Hope that helps,
>
> > magmike
>
> I =A0would write it like this and put in the SHEET module.
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Target.Count > 1 Or Target.Column <> 5 Then Exit Sub
> =A0 =A0 Cells(Target.Row, "M") =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 On Error GoTo mymsg
> =A0 =A0 =A0Select Case UCase(Target)
> =A0 =A0 =A0 =A0 Case "IN": x =3D "O"
> =A0 =A0 =A0 =A0 Case "QUOTE", "EMAIL": x =3D "P"
> =A0 =A0 =A0 =A0 Case "SENT": x =3D "Q"
> =A0 =A0 =A0 =A0 Case "REQ": x =3D "R"
> =A0 =A0 =A0 =A0 Case "DONE": x =3D "S"
> =A0 =A0 =A0 End Select
> =A0 =A0 Cells(Target.Row, x) =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 Cells(Target.Row, "T") =3D _
> =A0 =A0Format(Application.Max(Cells(Target.Row, "M").Resize(, 7)), "mm-dd=
-
> yyyy")
> =A0 =A0 Exit Sub
> mymsg: MsgBox ("Not there, How about using data validation")
> End Sub- Hide quoted text -
>
> - Show quoted text -

Thanks. Curious though, what does this line do different from the
rest:

Format(Application.Max(Cells(Target.Row, "M").Resize(, 7)), "mm-dd-
yyyy")

Also - How do I turn off the validation rule? I get your error
whenever I do not use one of the code words in the script, which is
not necessary. I use other words in column 5 that I do not require a
date stamp for. The purpose of the new column is a catch all for when
the last action happened. I don't want to have a column for every
option.
0
magmike7 (50)
12/12/2011 3:40:24 AM
hi,

i think you look for "Case Else"

Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else
[elsestatements]]
End Select



-- 
isabelle


Le 2011-12-11 22:40, magmike a �crit :

>>>>> In regards to the former discussion (http://tinyurl.com/762vq62) which
>>>>> worked great by the way (Thanks to all who helped!), how could I
>>>>> change the Case statement to populate a cell based any change in
>>>>> value, not just a specific text entry?
0
isabelle
12/12/2011 5:48:15 AM
Reply:

Similar Artilces:

asap utilities question
I have Msft. Excel Vsn 2002 and latest vsn. of ASAP Utilities. I used ASAP Utilities to merge single rows of data together so that more than one row will fit on a single line of text. My question now, is, is it possible using ASAP Utilities to separate the data that has been merged back into single rows of data? All information welcomed! Aaron ...

Isinteg Warning Message Question
I ran isinteg -pri -test alltests. I came up with 506 warnings. There are basically 4 types of warnings. They are below. Are these something I need to worry about? What should I do? thanks stewart sschwartz@nal.usda.gov ================================== Warning: MsgFolder 165 (Fid=0001-0000000D1864, Mid=0001- 0000000D559D, Inid=0001-000000F33436): PR_READ_RECEIPT_REQUESTED(0029000B) prop in Messages table and MsgFolder table do not match. Warning: MsgFolder 4 (Fid=0001-0000086DC911, Mid=0001- 00000436F972, Inid=0001-000004A583F1): Error JET_errRecordNotFound seeking to INID for this ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Setting up a new e-mail account
I'm trying to set up my yahoo account so that I receive my e-mail messages in my Outlook Inbox. I went to Accounts, and servers, and put in mail.yahoo.com for incoming mail (POP3) and smtp.yahoo.com for outgoing mail (smtp). When I click on the send/receive button I get an error message. Dave <anonymous@discussions.microsoft.com> wrote: > I'm trying to set up my yahoo account so that I receive > my e-mail messages in my Outlook Inbox. I went to > Accounts, and servers, and put in mail.yahoo.com for > incoming mail (POP3) and smtp.yahoo.com for outgoing mail &...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Dotfuscator question
Hello Everybody !!! When i create programm using C# i must understand that my programm from exe - module can be converted to the source file by Reflector. In Order do not allow do this i must use dotfuscator, but i did not find any free normal dotfuscator. May be someone tell me what can i do cause now as think we have interesting paradox. We 've got free technology but if we want to protect our products we must buy expensive commercial dotfuscators. Alex Dmitriev You should post this message in a newsgroup which discusses managed code. This newsgroup does not have much managed traff...

question on the rules wizard
When clikcing on the Rules Wizard, Outlook locks up - consistently. Has anyone got a remedy for this? Thank you, rich rpage@concerto.com ...

OE questions
Ok, I have two questions: 1. With Thunderbird I can use this with a hotkey manager to compose a new message with Tbird "D:\Program Files\Mozilla Thunderbird \thunderbird.exe -compose" -- how can I do the same for Outlook Express and Outlook 2003? 2. When I go to load Outlook Express I get this error message, followed by the error message below it: http://i180.photobucket.com/albums/x296/gwar_1/Untitled-3.png how can I fix this and use Outlook Express? Thanks! posted to the outlookexpress newsgroup via crosspost --=20 Peter Please Reply to Newsgroup for the benefit of others Re...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

HQ Licensing question V.2
I have a client running HQ client at 8 stores + HQ server at the warehouse. They would like to be able to use transfers from the warehouse to the stores, so I would have to setup a new PC running Store Ops and a new database, however, in order for the transfers to take place, I need a HQ Client License correct? Do I also need a POS key? I appreciate any insights you may have. Thanks in advance Hi Phil yes - you'll need an HQ Client license so it can exchange with your headquarters - "export" the wh database as you would any store from HQ no - you won't need a PO...

MS Project 2007 question
Hello, I am developing a simple schedule with no resources but 2 tasks out of 100 are hazardous and I would like to make the schedule so that when these tasks occur, no other tasks can occur. In other words, even though all tasks are moving around as the schedule is being developed, I would like to make sure no other tasks can be scheduled wherever these 2 hazardous tasks fall. These 2 tasks are serial to every other task in the schedule. Is there a way to do this? Thanks for your help. Mike Create two milestones. Use milestone 1 as the successor task to all tasks th...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

IM Add New Batch
GP 10 on 2003 Terminal Server. How do you create a Payables Batch in the IM in preparation for importing to the Payables Transaction destination? Alternatively, how must GP be set up to ensure that the “Add New Batch” rule works correctly? With the Add New Batch rule set the IM crashes (see my post Integration Manager 10 Crashes). Additionally the characteristics of the Add New Batch rule do not match the required information which I understand to be Origin and Checkbook ID. I figured there might be some default settings for Origin and Checkbook ID in the setup somewhere but was not a...

Help! charts disappearing, new charts crashing powerpoint
When i open a presentation the charts vanish...i have restarted, they come back. I try to edit a chart, and it crashes powerpoint after a few clicks. What can I do...whole afternoon going crazy! If you are using v 2007 there is a hotfix which is meant to cure some chart crashing problems http://support.microsoft.com/default.aspx?scid=kb;en-us;976479&sd=rss&spid=11264 -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "UKExcelgeek" wrote: > When i open a pres...

Moving exchange 5.5 to a new server exchange 2003
Good day all... how do I move exchange 5.5 to a brand new exchange server 2003. The new server will be in a new organization. Any info would be greatly appreciated. Thanks, Ray http://www.microsoft.com/usa/webcasts/ondemand/2340.asp http://support.microsoft.com/default.aspx?scid=fh;en- us;exch2003 Patrick Rouse Microsoft MVP - Terminal Server >-----Original Message----- >Good day all... how do I move exchange 5.5 to a brand new >exchange server 2003. The new server will be in a new >organization. Any info would be greatly appreciated. > >Thanks, >Ray > >. ...

Hopefully Simple VBA questions
Using VBA, I want to go row-by-row and get the values from certain cell in each row so I can run some tests on them, then put the result of th tests back in certain cells. This is how I am set up so far, can you help me with my questions i CAPS below?: Sub Example() Dim i As Long, c1 As Long, c2 As Long c1 = 0 c2 = 0 For i = 1 To 120 (HOW DO I MAKE THIS DYNAMIC TO INCLUDE NEW ROW AS THEY ARE ADDED?) If Cells(i, 2).Value Then 'start flag is true (HOW DO I MAK VBA GO TO A DIFFERENT WORKSHEET WITH THE Cells(I,2).Value STATEMENT? If Cells(i, 1).Value = "E" Then c1 = c1 + 1 E...

Help with percent formula beginner
Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary 10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be $36.00 (percent decrease). Can you please help me out with the formula? Also it seems like if I type in % in a cell and I delete it and type another number and I don't want a % in there it gives it to me anyway. Can I make this stop? Thanks so much Ted Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B1")) Is Nothing Then Wi...

Combo Box initial values question
Does anyone know how to make a combo box show a value when a sheet opens? Mine are always blank when I open them until I select a value. thanks tp Hi Teepee, Try something like: Me.ComboBox1.ListIndex = 0 --- Regards, Norman "teepee" <teepee@noemail.com> wrote in message news:4645ed29$1@newsgate.x-privat.org... > Does anyone know how to make a combo box show a value when a sheet opens? > Mine are always blank when I open them until I select a value. > > thanks > > tp > > thanks for trying. says 'invalid use of me keyword.&...

Newbie
I have a spreadsheet that is little more than a list of PO #'s that shows what orders are done and ready to ship for a particular customer. We upload the file every day to a website where the customer can view it whenever they want. So far, everything's fine. I use the "=TODAY()" function so that when I open and modify the listings, the date gets automatically updated. The problem is, when the customer accesses the file, the date also updates on THEIR end and the file looks more current than it really is. Is there a way to maybe lock in the date ONLY when I save the file...