Sunday, January 27, 2008

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.

No comments: