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)

Tue 21 Jun 2016

Solution - Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

By |Tuesday, June 21st, 2016|Categories: Solutions|Tags: , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet

1. Create a Sheet named Project which can hold all the projects. In that sheet, enter following formula in A2 and copy down till row 141 in columns A, B and C (as every sheet can contain a maximum of 20 projects and there are 7 days in a week and you can have a maximum of 20*7=140 rows)

(more…)

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.

Sat 21 May 2016

Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

By |Saturday, May 21st, 2016|Categories: Challenges|Tags: , , , , , , , , , , |0 Comments

This time, challenge is going to be tougher. An user receives a daily sheet which has got project status and you need to prepare a consolidated worksheet for the week everyday.

1. Your sheets are named Day1, Day2.....Day7.

2. One sheet can have a maximum of 20 entries.

3. The entries may not be same everyday depending upon whether a new project has started or a project has finished.

4. You need to get all the consolidation in "Consolidated Weekly Sheet".

One typical day's sheet -

1

You need to write formulas (or VBA) to populate row 3 onwards

1

 A typical answer would look like -

1

The worksheet related to this challenge can be downloaded from Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

The answer to the above challenge will be published after a month i.e. on 21-Jun-16.

Sat 25 Jul 2015

Tips & Tricks 101 - Get Column Name for a Column Number

By |Saturday, July 25th, 2015|Categories: Tips and Tricks|Tags: , , , , |1 Comment

Let's suppose, you have a number in A1 and you want to get the column Name for that.

Hence, if A1=1, you want "A"
Hence, if A1 =26, you want "Z"
Hence, if A1=27, you want "AA" and so on.

The formula to derive the column name would be be -

=SUBSTITUTE(ADDRESS(1,A1,4),1,"")