Consulting

Results 1 to 12 of 12

Thread: interesting question

  1. #1

    interesting question

    I have a spreadsheet that i use to show deductions from invoices. The company is very strict about showing specific reasons for the deduction. This workbook consists of four sheets. One is a definition sheet for why the deduction was taken. I would like to eliminate this as the vendors are forever calling confused.

    What I would like to do is be able to type a numer into column I and have it bring back a text message (ie enter the number 4 and the cell would get populated by "excessive")

    Attached the spreadsheet let me know if this can be done.

  2. #2
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    If I understand correctly (which isn't alsways the case) it would be easiest to use data validation on this column and pick the reason from a dropdown list in the cell.
    I've attached an example...
    The list entries have to be on the same sheet so I've hidden the rows (29-45)
    K :-)

  3. #3
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    You want to put a number in column I, and then have the TEXT return to column I?
    ~Anne Troy

  4. #4
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    Quote Originally Posted by Killian
    If I understand correctly (which isn't alsways the case) it would be easiest to use data validation on this column and pick the reason from a dropdown list in the cell.
    I've attached an example...
    The list entries have to be on the same sheet so I've hidden the rows (29-45)
    FYI - A named range can be used to avoid having the data on the same sheet.

    The attached file has some code in the Sheet1 Change Event and in a ModExcuses module. The code as written affects the entire sheet. It can be limited in the Change Event code to whatever relevant range you would need.

    HTH

  5. #5
    not sure i understand what you did

    hi Dream,

    yeah I want to enter a 1 in column I and have it change to a given text.

  6. #6
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    That's why *I* didn't give you a vlookup.
    ~Anne Troy

  7. #7
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    Quote Originally Posted by rcbricker
    not sure i understand what you did

    hi Dream,

    yeah I want to enter a 1 in column I and have it change to a given text.
    I amended the code to act only on Column I now.

    In the Sheet1 code please find:

    [VBA]Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.HasFormula Or IsEmpty(Target) Then Exit Sub
    If Target.Column <> 9 Then Exit Sub
    If IsNumeric(Target) Then
    Excuses
    End If
    End Sub
    [/VBA]

    The Worksheet_Change event will check when the worksheet changes.
    The first If checks for empty or formula contents in the Target cell; it exits if either is the case. The second If checks to see that the target is in column 9 (I); if not, then it exits. The third if checks to see that the target has a numeric value, if so, it calls the Excuses macro in the ModExcuses. The code for this is below:

    [VBA]Option Explicit

    Sub Excuses()
    Application.EnableEvents = False
    Select Case ActiveCell.Offset(-1, 0).Value
    Case Is = 1
    ActiveCell.Offset(-1, 0).Value = "Reason 1"
    Case Is = 2
    ActiveCell.Offset(-1, 0).Value = "Reason 2"

    'enter as many cases as required

    Case Else
    ActiveCell.Offset(-1, 0).Value = _
    "Please call us for further explanation"
    End Select
    Application.EnableEvents = True

    End Sub
    [/VBA]

    The Select Case structure tells Excel to place the "Reason 1" text in the cell above the active cell if a 1 is entered. The change event fires once enter or tab is hit; I assumed enter was being used and that the cursor would be below the data entry cell; if tab is being used, switch the Offset(-1,0) to Offset(0,-1) to move back a column. "Reason 2" is entered for 2, etc.

    As many cases as there are explanations can be entered using this syntax. Simply replace the text Reason 1 with whatever the appropriate explanation should be.

    HTH

  8. #8
    VBAX Regular
    Joined
    Feb 2005
    Location
    North West London, UK
    Posts
    19
    Location
    rcbricker- I'd love to have a look but why do you bother to zip it. Please re-post without zipping. Thanks. Patrick

  9. #9
    as far as i know all attachments have to be zipped.

  10. #10
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    Quote Originally Posted by rcbricker
    as far as i know all attachments have to be zipped.
    The Valid file extensions are: bmp gif jpeg jpg pdf png zip, whle zipped files may not be your cup of tea, Patrick, but they are much more useful than a bitmap or jpeg of a spreadsheet. :rofl

    Zipping the workbooks allows for server space to be used effectively. If you don't have zipping software, try this: Free zip software Of course, the WinZip evaluation version is freely available at their site as well.

    HTH

  11. #11
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    You may use this one too: www.zip995.com
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org

  12. #12
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    We also only allow certain file types to keep bugs out.
    ~Anne Troy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •