Recent Keyword Activity

I have to say that the questions I have rec'd here, though few, have humbled me. I still think I know excel better than most people I know. But, it seems the people who find me know more than me. Still, let me respond to some recent Keyword Activity...

how to concatenate and keep spaces
Put them in quotation marks. For example:
=Concatenate(a1," ",b1," and then finally ", c1)
IF
A1=CAT
B1=BAT
C1=SAT
Result=
CAT BAT and then finally SAT


keep table array when pasting vlookup formula
Make the table array absolute using $. For example:
=VLOOKUP(a1,source!$A$1:$B$500,2,0)
Whenever you pull down the formula, the table array will remain absolute at $A$1:$B$500.

how to make excel look at a date as text
where the value in A1 is a date, use this formula;
=text(a1,"mm/dd/yyyy")
You can also use m/d/yyyy or mm/yyyy or yyyy/mm/dd or yyyymmdd or whatever date format you want. But, whatever the format you choose, it is now TEXT and will not change to a number if you change the format. Well, you just can't change the format. It's a text field now. The original data, however, isn't a text field. It's still whatever it started out as.

Cool tip of the Day: From the kicking it old school old school of thought, if you want to make any cell absolute in your formula without typing the $, highlight the formula and hit F4.

Helping You Out, part 2

Anonymous said on 1/8/08:

I am in desperate need of help. My firm has a special toolbar they use to make sure all reports we generate are formatted in a specific way. I think it must involve a lot of custom formats because now I keep getting an error message saying "Too many custom formats" whenever I try to do anything. I tried deleting some of the custom formats but that doesn't seem to work. Any ideas?

This one is tough because I think the problem is probably the code that formats your report.  It's probably in Visual Basic for Applications, and your IT group would have to trouble shoot the problem.  Can you tell if the error message is from excel or from the "special toolbar"?

I do have one suggestion, though.  If you try this, please save a copy of your work and perform these steps on the copy, so that you don't lose any of the work you have done so far.

1. Select all entire worksheet (one easy way to do this is to hit shift-ctrl-down arrow-right arrow from cell A1)


2. Select Edit-->Clear-->Formats


3. Now, rerun the script on the special toolbar.

What I'm trying to have you do is clear all the formats so the macros in the toolbars can have an easier time doing their thing.  Without knowing anything, I would guess that you might be working on a sheet that has a lot of formats maybe even in cells that don't contain data.  Clearing out all the formats might all the scripts on the toolbar to do the work it needs to do.

I hope this helps. I suspect that it may not.  If you try, though, please do it on a copy!

Helping you out!

Question:

Anonymous said on 1/7/08:

"I have two fields that I need to compare and bring back data in a third field if they match.

Basically, it's a simple VLOOKUP equation.
But, these two number fields (which are currently both formatted as text) don't always bring back the data, unless I 'retype' the data, then it recognizes that they are the same.
Can you think of why I would need to retype the exact same data for it to recognize it?

I'm bringing in the data via copy/paste, but using field format options to ensure they're both the same."

You're using the field format option to ensure they are both the same, and that is definitely the right thing to do. You may just have to take it a step further.

OPTION 1

When you bring in the data via copy/paste, choose Paste Special and paste as values.  If that doesn't work, go back to the original lookup value, select that column, copy it and paste it special as values (don't paste it somewhere else.  Paste it right where it is).

OPTION 2

If the steps in Option 1 don't work, take it to the next level by formatting the fields so they are exactly the same using the Data-->Text to columns wizard.

1. Select the column that contains the lookup value.


2. Click on Data-->Text to Columns.  This will bring up a "Convert Text to Columns Wizard."


3. The first step is to choose whether your data is "delimited" or "fixed width."  Choose delimited here.


4. Second step is to either choose the delimiter or the width of your text.  Make it as long as your longest data.


5. Third step is to choose the data format.  Make it a number or a text, or whatever makes the most sense to you.

Now, select the first column in your table array and repeat the steps, ensuring that you set up this column of data precisely the same way you set up your lookup value.  You may need to play with the options in each of the three steps.  Choosing delimited always worked for me.  But, that might not work for you.

OPTION 3

Another thing that may be hinking you up are spaces.  If your lookup values should not contain spaces, you can do this:

1. Select the column that contains the lookup value.


2. Hit Ctrl-F (or Ctrl-H to save step 4...either works exactly the same).


3. Enter a space in the "Find What" box.


4. Hit the replace tab.


5. Enter nothing in the "Replace with" box.


6. Hit "Replace All"


7. Repeat these steps the first column in your table array.

The first option might do the trick.  The second option should work all the time (I hope.  It's always worked for me.)  The third option is a little easier, but you have to be careful and you can't use this if your lookup data is suppose to contain spaces.

Let me know if you need pictures, and let me know if this works (or not)!