Sat 21 Dec 2019

Tips & Tricks 174 - Create a Desktop Shortcut for Onedrive Documents

By |Saturday, December 21st, 2019|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Sometimes, you might need to create a desktop shortcut to Onedrive documents. Below is a method for Excel documents which can be used for any MS Office document.

First, you would need to find the path of Excel.exe on your computer. (more…)

Mon 04 Jul 2016

Solution - Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?

By |Monday, July 04th, 2016|Categories: Solutions|Tags: , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?

Enter the below formula in B1 as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=HYPERLINK("#Sheet2!"&ADDRESS(MATCH(1,--(MMULT(--((Sheet2!A1:J20)=A1),
TRANSPOSE(COLUMN(A1:J20)^0))>0),0)+ROW(A1)-1,MATCH(A1,OFFSET(Sheet2!A1,
MATCH(1,--(MMULT(--(A1=Sheet2!A1:J20),TRANSPOSE((COLUMN(A1:J20)^0)))>0),0)
-1,0,1,COLUMNS(A1:J20)),0)+COLUMN(A1)-1),"Take me to the Word - "&A1)

The workbook illustrating the above solution can be downloaded from Solution - Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)

Sat 04 Jun 2016

Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)?

By |Saturday, June 04th, 2016|Categories: Challenges|Tags: , , , , , , , |1 Comment

You have a grid of English words in Sheet2. In A1 of Sheet1, you have been given a word. You need to create a Hyperlink through formula in B1 of Sheet1. The Hyperlink which says "Take to the Word - Discover" if the word was Discover in A1. "Take me the Word - Pretty" if the word was Pretty in A1.

1

1

You can download the worksheet related to this challenge from Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)

The solution to above challenge will be published after a month i.e. on 04-Jul-16.