Overlapping Graphs

If you want to over lap a bar graph with another, you can try formatting the series option in the graph where by you reduce the overlap gap

 

 

Get URL of a CELL

If you want to extract URL of a Cell, you can use it thru this macro

 

Function GetURL(cell As range, _
Optional default_value As Variant)
'Lists the Hyperlink Address for a Given Cell
'If cell does not contain a hyperlink, return default_value
If (cell.range("A1").Hyperlinks.Count <> 1) Then
GetURL = default_value
Else
GetURL = cell.range("A1").Hyperlinks(1).Address & "#" & cell.range("A1").Hyperlinks(1).SubAddress
End If
End Function

 

Source: http://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel

 

 

re-Check the password entered

If you want to see what is the password that was entered in browser, here are simple steps

 

a)      Right click on the password box and select Inspect element

b)     You will see a text like this

 

 

c)      Change type=”password” to type=”text”

 

 

Magic , you have the password typed on the screen

 

Get Sheet Name or File Name or File Path

If you want to get some data use the following

 

=CELL("filename")

 

Then you can use ‘find’ function to trim and get what ever informaiton you want to extract

 

Trace Circular Reference

In case if you are lost with the values and there is a circular reference, you can check the same using this in the formula tab

 

 

Menu Bar Control

You can enable / disable items in the Menu Bar back in VBA. The following is the Control Number to enable / Disable

 

CommandBar            Control                         ID

   --------------------------------------------------------------------

   Worksheet Menu Bar    &File                        30002

   Worksheet Menu Bar    &Edit                        30003

   Worksheet Menu Bar    &View                        30004

   Worksheet Menu Bar    &Insert                      30005

   Worksheet Menu Bar    F&ormat                      30006

   Worksheet Menu Bar    &Tools                       30007

   Worksheet Menu Bar    &Data                        30011

   Worksheet Menu Bar    &Window                      30009

   Worksheet Menu Bar    &Help                        30010

 

   Chart Menu Bar        &File                        30002

   Chart Menu Bar        &Edit                        30003

   Chart Menu Bar        &View                        30004

   Chart Menu Bar        &Insert                      30005

   Chart Menu Bar        F&ormat                      30006

   Chart Menu Bar        &Tools                       30007

   Chart Menu Bar        &Chart                       30022

   Chart Menu Bar        &Window                      30009

   Chart Menu Bar        &Help                        30010

 

 

Check Formulas

If you want to check which are the cells with formulas and which are not, you can use this simple quick highlighter to show you, you can find this in the Home TAB, right corner bottom

 

Find the nth Occurrence of a Character in a cell

If you want to find

 

A1 = This World is Very Big

 

To find the place of 3^rd I Use the following formula :  FIND("i",A1,FIND("i",A1)+3)

 

Here 3 represent nth occurrence

Convert XLAM to XLSM

If you have any XL Add in file and you want to convert back into a Macro Enabled File, please do the following

 

Go to File properties >> change the IsAddin to  FALSE

 

 

Enjoy editing Macros

Connect Tally from Excel

If you are trying to connect Tally from Excel and you do the following process.

 

First try connecting a new Query

 

 

Select Tally ODBC

 

 

 

 

If you don’t find the same then ODBC is not enabled in the Tally. Kindly enable by doing the following

 

1.      On the Gateway of Tally, go to configuration F12

 

 

2.      Select Advance configuration

 

 

 

3.      Enable the ODBC

 

 

 

 

 

 

 

 

 

Generate Random Names

In Excel you can always generate Random Numbers, but for some reason you need Random NAMES quickly, use this link here

http://listofrandomnames.com/

Reverse the Text in Excel

Use this code

 

Function Reversestr(str As String) As String

    Reversestr = StrReverse(Trim(str))

End Function

 

Forgot Password of Tally Company

In Case if you have the tally data, but do not have the login details, you could do the following

 

a)      Look at the name of the company

b)      See the data till when the entries are there

c)      Create a new company in tally with the same name

d)      From the original tally data folder, copy all the files except “Company.900”

e)      You will be able to now see the company.

 

Note: You will not be able to drill down to the transactions, however PnL and BS can be seen