Sat 22 Apr 2017

Tips & Tricks 159 - Prompted to "Save File" even if I haven't changed anything

By |Saturday, April 22nd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

When you open a workbook and you change something in the workbook and you close the workbook, it will ask you to save the workbook. But sometimes, even if you have not changed anything, still the workbook asks to be saved even if you haven't changed.

Point 1 - Microsoft has provided the answer to this question at following link -

(more…)

Sat 16 Apr 2016

Article 38 - 10 Features I would like to see in Excel

By |Saturday, April 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Like all of you, I am dependent upon day to day work in Excel. Excel is a software which is like ocean, unlimited in bounds. It has almost all features but it doesn't have few which everybody cherishes. All of us want something or the other. Below is a list of features which I would like Excel to have.

1. Making Volatile Functions Non-volatile on the basis of a Parameter

We all like TODAY(), NOW(), RAND() and RANDBETWEEN() (there are other volatile functions also). They are very useful but suffer a fatal flaw, if something gets recalculated, they always get recalculated also. Hence, if I am using TODAY() to derive today's day and if I open the sheet tomorrow, TODAY() will change tomorrow's date. I wish that all volatile functions should carry a parameter so that they should not recalculated when the sheet is recalculated. For example, we should have something like TODAY(N), NOW(N),RAND(N) and RANDBETWEEN(N) where N
(more…)

Sat 25 Jul 2015

Tips & Tricks 100 - Get Sheet (tab) Name, Workbook Name and File Name through a formula

By |Saturday, July 25th, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

There are many situation while working in Excel that you need to get the name of the sheet. (Note - For formulas to work, the workbook must be saved at least once)

The formula to retrieve file name would be -

=CELL("filename",$A$1)

The formula to retrieve the sheet name would be -

=REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("filename",$A$1)),"")

Note - CELL is a volatile function, hence this will calculated for every change in the sheet.

The formula to retrieve workbook name would be -

=REPLACE(LEFT(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))-1),1,FIND("[",CELL("filename",$A$1)),"")

Note - CELL is a volatile function. Hence, the formulas would recalculate every time, the worksheet changes.