26. September 2016 · Comments Off on Dynamic links between two workbooks · Categories: Lookup and References, STD f(x)s · Tags: ,

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

link1

Which will look like this if try1.xlsx is closed

link2

However, if you open try2.xlsx alone before you open try1.xlsx, you get this message

link3

 

This means now try2.xlsx and try1.xlsx have a link between them

In another word, try2.xlsx liknks to try1.xlsx

Which is ok, but

Except, I don’t like that,

It will take me some time to refresh the link if I have moved that try1.xlsx to another location or rename it.

So, what the solution?

Dynamic link

Which basically, create a link between those two without the mess, and Excel will not recognize it as a link and will stop complaining.

link7

How?

Using the function =INDIRECT(), one of my favorites.

So, in above example, we break in the link inside C12 into pieces, like below

link4

So, C4 has the full folder location of our workbook try1.xlsx

C5 has the file name

C6 has the sheet name

And C7 has the cell

Now, we combine them back into full path as we found it originally into cell C8

link5

By adding the required characters to make it look exactly how INDIRECT expects it

Then, finally, we use it inside INDIRECT

link6

And here is the dynamic link

What is the gain?

Mostly, the flexibility of chagninng the references, if folder got changed (by moving the workbook) or name got change, by renaming it, or sheet, we can just type it in its box and press enter

That particular workbook, has to be open to read the value.

 

What are other gains?

Well, think about dynamically reading numbers from multiple sheets into one, for example, you have sheet per project and you want to collect all these info from all these projects into a ‘summary’ sheet, this dynamic link, can help you build one cell, then copy it to others to apply it there

 

 

26. September 2016 · Comments Off on VLookup · Categories: Lookup and References, STD f(x)s · Tags:

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.

 

Post1499

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

More »

26. September 2016 · Comments Off on CONVERT function · Categories: Engineering, STD f(x)s · Tags: ,

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")

 

convert5

 

convert5 convert4 convert3 convert2

 

03. November 2015 · Comments Off on Workdays – Across months · Categories: Date and Time, Formulas - combined functions, STD f(x)s · Tags: , , , ,

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:

=IF(OR($D$2>E7,$D$3<D7),"",
NETWORKDAYS(IF(D7>$D$2,D7,$D$2),IF(E7<$D$3,E7,$D$3)))

 

14. September 2015 · Comments Off on Extract current (Active) workbook name · Categories: Formulas - combined functions, Information, Lookup and References, STD f(x)s, Texts and Strings · Tags: , ,

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, to distenguish 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)

11. May 2015 · Comments Off on Week number to Month number · Categories: Date and Time, Formulas - combined functions, Logical · Tags: , , , , ,

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

=IF(A5<2,MONTH(DATE(YEAR(TODAY()),1,1)),MONTH(DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+((A5-1)*7)+1))

 

Enjoy

06. April 2015 · Comments Off on Why I ran from =INDIRECT() function · Categories: Formulas - combined functions, Lookup and References, STD f(x)s · Tags: ,

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))

More »

06. April 2015 · Comments Off on Get column name (or headers or address as A,B,C, etc) as input inside cell · Categories: Formulas - combined functions, Lookup and References, STD f(x)s, Texts and Strings · Tags: , , , , ,

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

4-6-2015 9-25-15 AM

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()

More »

17. June 2014 · Comments Off on Week number to Sunday date – Weeknum reverse · Categories: Date and Time, Formulas - combined functions, STD f(x)s · Tags: , , , ,

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:

=IF(B3<2,DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+((B3-1)*7)+1)

B2014-10-02WeeknumReverse

 

 

19. March 2012 · Comments Off on Fx to cut long column into two columns · Categories: Formulas - combined functions, Lookup and References, STD f(x)s · Tags: , ,

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.

Can work also to convert into 3 columns out of 1, etc

Use this formula to grap the first column
=OFFSET(OneCol!$B$1,((INT((ROW()-1)/$A$1)+1)-1)*$A$1+ROW()-1,0)
And use this one to grap the second column
=OFFSET(OneCol!$B$1,((INT((ROW()-1)/$A$1)+1))*$A$1+ROW()-1,0)
Again, all these formulas are linked to cell A1