TRIM, CLEAN, or Something Else?

[BIG Mike]

I am new to working with Excel and have a question regarding a formula I am
trying to build. Thanks in advance for any input !!

I have a workbook set up where there are 12 monthly worksheets (Jan-Dec)
which contain details on commission income. There is a 13th worksheet which
imports data from each of the 12 worksheets. The purpose of the 13th
worksheet is to give an ‘overview at a glance’ of specific pieces of data.
The problem I am having is in determining a correct formula for the overview
sheet cells.

The overview worksheet has columns with heading labels January-December and
rows labeled Company 1, Company 2, Company 3, etc.

I established my first formula which read:

=IF(SUM(Jan!$L$4:$L$100)>0,SUM(Jan!$L$4:$L$100),"")

Column L contains income entries. This formula worked perfectly for what it
was intended, but did not allow me to separate results by company. So, I
added the SUMIF function to my formula.

=IF(SUM(SUMIF(Jan!$C$4:$C$100,$A3,Jan!$L$4:$L$100))>0,SUMIF(Jan!$C$4:$C$100,$A3,Jan!$L$4:$L$100),"")

Column Jan!C contains the abbreviations for Company 1, Company 2, etc.,
indicating which company paid the commission reported in column Jan!L. Cell
A3 (on the overview sheet) contains the abbreviation for the company I want
totals for on this specific line. To my surprise and delight, this also
works perfectly.

The problem I have is that when I enter the company abbreviation on the
monthly worksheets, I will be working quickly and if I inadvertently add a
space before the abbreviation or a space after the abbreviation, it will not
exactly match cell A3 and will not tabulate the numbers correctly.

I looked at the text functions and I see there is the CLEAN function and the
TRIM function. I believe one of these (probably clean) is the function that
will help me, but I have not been able to find a way to insert it into my
existing formula and make it work.

PLEASE HELP !!!

 

[Gary''s Student]

Here is an approach that will automatically correct entries made to cell A3.
Install the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Range("A3")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
r.Value = Trim(r.Value)
Application.EnableEvents = True
End Sub

After the installation, any entry you make in A3 will be "trimmed"
automatically.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[BIG Mike]

Hello and Thank you.

I followed your instructions. I THINK I entered it correctly, in the
correct place, but am not sure since it is not working as I would like. One
thing that is different is that I do not want the macro to apply to cell A3.
Cell A3 will not be edited; it is a constant. The cells I will editing and
want the data trimmed are cells C4:C100. So I changed your macro as follows
and added it to the monthly sheets:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Range("C4:C100")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
r.Value = Trim(r.Value)
Application.EnableEvents = True
End Sub


Any ideas?