Getting total number of sheets (Sheets), or ID of this sheet (Sheet). The two new formulas Sheet() and Sheets() can do well, yes not perfect, we still need to have function to list sheet names, but it is a start
Category: Lookup and References
Lookup and references functions, comes with Excel
FORMULATEXT to show formulas
Back in the day, we used to need to write a full macro (also called User-Defined-Function) to allow users to show the formula from a certain cell
Not any more
New function IFERROR, finally
We have already gotten used to use VLOOKUP, MATCH and other functions that generates errors inside an IF
But, we do not need that anymore
We can use the new guy, IFERROR instead, which will show the result if it did not generate error, otherwise will show something else if it resulted an error.
OFFSET+MATCH
One of the power of combining functions in one cell is using the best of these functions
An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP.
Why do we want to do that?
VLOOKUP will search in the left-most column in a table to get a value from a column on the right. But this is not the situation all the time
We needed several times to locate a row by using a column on the right, then get the value from a column left to it.
This is when VLOOKUP can not help us.
MATCH fumction, just a simple search
To do a search in a column or a row, MATCH is the function to look for It is very simple, you set the value to look for and where to look, then of course there is some another option to set how to do the search. So, we are looking for 87 in column…
Links between two workbooks – dynamically using functions
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
Read More “Links between two workbooks – dynamically using functions” »
VLookup
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
Extract current (Active) workbook name
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 =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-6) 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,…
Why I ran from =INDIRECT() function
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
So…
If you do
=INDIRECT("G4")
It gives you the value in G4
Notice that “G4” has the double-quotations, means
=INDIRECT("G"&SUM(S1:S4))
Get column name (columnname as A,B,C, etc) as input inside cell
Excel already has the formula ROW()
=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
=Column()
Read More “Get column name (columnname as A,B,C, etc) as input inside cell” »
Fx to cut long column into two columns
Convert one long column into two
This set of formulas will get the items after row 45 (for example) from column A into the column B starting from B1 to have two 45-rows next to each other instead of one 90-rows column
This was the answer for this question
Yahoo! Answers question http://answers.yahoo.com/question/index?qid=20081225182043AAc7ZEB
Show two columns in one page as four columns using functions only. This will help you to show the next x number of rows for a certain column to the next of the first one to reduce the number of pages to print.