You must already know that when you press Enter after you modify a cell, the cell content will be saved and you jump down to cell below (we can change that behavior by the way, but that is another post).
And you could have already know that if you want to have multiple lines inside a cell, you need to press ALT+ENTER to do that.
But, how can we do that in formula ??
I got a request on how to do that in a spreadsheet, so, the client wanted to convert two columns having two parts of address, Column C has Street address and street name, and column D has city, state and Zip code.
I don’t believe I don’t have this here yet. This is one of my oldest tricks
When we have to do links between two workbooks (One cell in Workbook1 to have value updated from a cell in Workbook2), we usually just do links like this…
Go to workbook1, select a cell, type in ‘=’ then go to Workbook2 and select a cell, then ‘ENTER’
To get something like this
Which will look like this if try1.xlsx is closed
However, if you open try2.xlsx alone before you open try1.xlsx, you get this message
I have been asked for a while now to just put a simple VLookup function, here it is, I know this might be stupid to post it here, but we need to fill requests from all types of visitors.
Vlookup is the most famous function in Excel, some people actually refer to Excel as VLookup.
Even though I do not like that fact, especially when we have other powerful functions like INDEX, OFFSET, INDIRECT and others.
So, what is VLOOKUP?
VLookup is going to search for an item in the left column of your table (or selection), as in the screenshot below, then bring me back the value in the same row where it find that item from another column.
The Green box (Lookup_Value) is what the function will search for, and the vertical green box is where, this is because we selected the range (Table_Array) in Blue as the area of D:G, so VLookup will search in D, this is the V part in VLOOKUP, we should also expect to see HLookup.
Means if item is not found in column D, VLookup will return as #N/A error
Excel 2010 has a lot of new exciting things, one of them is the ‘Engineering’ category of functions, this was usually a plugin we have to add to get, but in Excel 2010, it is already there.
The function ‘Convert’ does what you expect it, converts a number from one metric to another.
Of course, we cannot remember all metric systems and units, so just press F1, or click help to see this list to know how you can convert from one unit to another in any of the systems inside that function
The one I most interested is
=Convert( 45, "mi", "km")
More images …
Get total number of days per month
This was a question from one of my friends, This set of formulas will calculate how many days (Networkdays) in each of the months in the list for a given Start and End dates
If Range of dates are across multiple years, user can copy columns C, D, E and F to fill those years
The months for the list are automatically populated starting from Jan of the year of the start date
The main formula in F7 should be:
I need that more often than I thought
It is basically extract the full workbook name that we are in, with no folders, and with no extension
So, here it is….
And if we want to go 1 step further, I often have the version of the tool as part of the filename, to distinguish of the prior release, so below function will extract the version only, if we ever need to compare of the versions
=MID(CELL("filename",$A$1),SEARCH(" v",CELL("filename",$A$1))+2,SEARCH(".xl",CELL("filename",$A$1),SEARCH("[",CELL("filename",$A$1)))-SEARCH(" v",CELL("filename",$A$1))-2)
Now we need to convert week number to month number
This is exactly the same as week number to date (reverse weeknum)
So, when the week number is in A5, then formula below will get you the month number
I think if there is a single function that would be a huge threat to VBA and macros from user perspective, it would be INDIRECT()
Yes, it wasn’t long enough before I got introduced to OFFSET() back in 1998, and was using it a lot and in my classes, yet once I found INDIRECT, things changed
Along with ADDRESS, ROW and COLUMN and some text tricks like SEARCH, LEFT, LEN, RIGHT, etc, you can do a lot.
You see INDIRECT does a simple job
Converts a text that represent a reference, into reference
If you do
It gives you the value in G4
Notice that “G4” has the double-quotations, means
Excel already has the formula ROW()
Which as we may already know will give us the number of the row we are in, if you add no parameters
However, the COLUMN() is kind of tricky
Because of the fact that cell addresses (or references) are based on the old style of R1C1
This was a request from one of my work managers
She wanted to convert a week number she got in a column into thier dates
Excel already have the WEEKNUM that converts a date into its week number, but the reverse is what needed here
So after my mouth was drooling while she was asking her question, I started a head, and below is the result formula
You just need to place the week number in cell B3, then paste this formula anywhere: