## Tips & Tricks 170 - Convert your formulas to Absolute References

If your sheet has plenty of formula and you want to convert them into Absolute references i.e.

One way is that you convert each one of them individually or use some logic for Find and Replace. But all these methods have their own limitations.

Best way is to use VBA method.

1. Make a backup of your workbook.

2. Open your workbook and ALT+F11

3. Locate your Workbook name in Project Explorer Window

4. Right click on your workbook name > Insert > Module

5. Copy paste the Macro code given

6. Go back to your Workbook and ALT+F8 to display Macro Window

7. Run your Macro from here

8. Delete you Macro if the Macro was needed to be run only once.

9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

1 2 3 4 5 6 7 8 9 |
Sub ConvertToAbsolute() Dim Rng As Range, Cell As Range Dim Ws As Worksheet Set Ws = ActiveSheet Set Rng = Cells.SpecialCells(xlFormulas) For Each Cell In Rng Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsolute) Next Cell End Sub |

## Challenge 69 - Need Help in Not Showing Comments

Download the workbook. Whenever I select a cell, the yellow box pops up. I need your help to provide me instructions to remove these.

**The workbook can be downloaded from Challenge 69 - Comments**

*The solution to above challenge will be published after a month i.e. on 20-Feb-2018.*

## Solution - Challenge 68 - Generate a Particular Sequence

Below is a possible solution to the problem **Challenge 67 - Generate a Particular Sequence** -

This consists of 2 logic.

- Generate Triangular number. The formula for which is n*(n+1)/2
- Generate Column Labels (A, B, AA, ZZ....) from numbers, the formula for which is

=SUBSTITUTE(ADDRESS(1,3,4),1,"") where 3 is for column C. So, we need to vary this 3.

The combined formula would be -

=SUBSTITUTE(ADDRESS(1,(ROW(1:1)-1)*ROW(1:1)/2+1,4),1,"")

## Tips & Tricks 169 - Get the Source of a Pivot Table

1. Make a backup of your workbook.

2. Open your workbook and ALT+F11

3. Locate your Workbook name in Project Explorer Window

4. Right click on your workbook name > Insert > Module

5. Copy & Paste the below code in this module

Now, you can call this function like

=GetPivotRange(E5)

Where E5 is any cell in your Pivot Table

1 2 3 4 |
' *** Macro by eforexcel.com ***** Function GetPivotRange(Cell As Range) As String GetPivotRange = Application.ConvertFormula(Cell.PivotTable.SourceData, xlR1C1, xlA1) End Function |

## Excel Quiz 58

## Excel Quiz 58

A quiz based on Excel functions and formulas

## Tips & Tricks 168 - Sum Cells for a Particular Color

This can be accomplished with VBA only. There is no native formula within Excel to support this. This is a fairly small piece of code..

1. Make a backup of your workbook.

2. Open your workbook and ALT+F11

3. Locate your Workbook name in Project Explorer Window

4. Right click on your workbook name > Insert > Module

5. Copy and Paste the below code in the module

6. Save your file as .xlsm

7. Call your macro as

=SumColorCells(A1:C6,255,255,0)

In place of A1:C6, your can put your range.

Next 3 digits are RGB codes. For Yellow, they are 255, 255 and 0...To know the RGB code of any colour, select your cell and click on down pointing arrow on Fill Colour symbol and go to custom to pick up RGB code.

*---- Macro by E for Excel ----*

1 2 3 4 5 6 7 8 9 |
Function SumColorCells(Rng As Range, R, G, B) Dim Cell As Range Application.Volatile For Each Cell In Rng If IsNumeric(Cell) And Cell.Interior.Color = RGB(R, G, B) Then SumColorCells = SumColorCells + Cell End If Next Cell End Function |

## Challenge 68 - Generate a Particular Sequence

Study the sequence below and you need to write a formula which when dragged down should generate the below sequence.

For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only.

You need to generate upto 100 entries.

*The solution to this challenge will be published after a month i.e. on 16-Jan-18.*

## Solution - Challenge 67 - Find Duplicates and Show the Count

Below is a possible solution to the problem **Challenge 67 - Find Duplicates and Show the Cou**nt

Use a helper column in which put following formula -

=LEFT(SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%")),

" ","%",2),FIND("%",SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),

SUBSTITUTE(A2&"-","-","%"))," ","%",2))-1)

In the last column, put following formula -

=IF(COUNTIF($B$1:B2,B2)=1,COUNTIF(B:B,B2),"")

The solution work can be downloaded from **Solution - Find Duplicates and Show the Count**

## Solution - Challenge 69- Need Help in Not Showing Comments

By eforexcel|Tuesday, February 20th, 2018|Categories: Solutions|Tags: Comments|0 Comments

Below is a possible solution to

Challenge 69 - Need Help in Not Showing CommentsThese are not comments but Data Validation Messages..

Select the cell where pop up message is appearing > Data tab > Data Validation > The pop message is in the Input Message > Either you can remove messages or just say clear all.

To remove them in block -

1. Select rows 1 to 7 > Data tab > Data Validation > Clear All

2. Select rows 20 to 22 > Data tab > Data Validation > Clear All