PDA

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. :)