View Full Version : [SOLVED:] interesting question
rcbricker
03-10-2005, 08:36 AM
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.
Killian
03-10-2005, 09:28 AM
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)
Anne Troy
03-10-2005, 09:29 AM
You want to put a number in column I, and then have the TEXT return to column I?
Brandtrock
03-10-2005, 10:34 AM
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
rcbricker
03-10-2005, 11:56 AM
not sure i understand what you did :bug:
hi Dream,
yeah I want to enter a 1 in column I and have it change to a given text.
Anne Troy
03-10-2005, 12:09 PM
That's why *I* didn't give you a vlookup.
Brandtrock
03-10-2005, 12:20 PM
not sure i understand what you did :bug:
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:
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
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:
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
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
patrickab
03-11-2005, 04:11 AM
rcbricker- I'd love to have a look but why do you bother to zip it. Please re-post without zipping. Thanks. Patrick
rcbricker
03-11-2005, 05:48 AM
as far as i know all attachments have to be zipped.
Brandtrock
03-11-2005, 07:10 AM
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 (http://www.camunzip.com/) Of course, the WinZip evaluation version is freely available at their site as well.
HTH
Paleo
03-11-2005, 08:37 AM
You may use this one too: www.zip995.com (http://www.zip995.com)
Anne Troy
03-11-2005, 03:15 PM
We also only allow certain file types to keep bugs out. :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.