Pages

Double click formula to follow link?

Some Excel users often link several workbooks by using formulas that references a cell in another file.
When there is a need to follow the formulas arround, the least experienced users will open the several linked workbooks manually but most of the medium/high experienced users will just use the double click function.
So, what's this double click function I am talking about ?

Imagine the following scenario:

  • You are working on Workbook_A which Sheet1/Cell A10 uses a value from Workbook_B/Sheet1/Cell C20; 
  • You are working on Workbook_A and Workbook_B is closed;
  • If you double click Workbook_A/Sheet1/Cell A10 with your mouse left button it will open the formula for editing! So, how can you open the source file (Workbook_B) with a double click on Workbook_A/Sheet1/Cell A10 ?
  • In case you are using Excel 2010 or higher go to File > Options > Advanced > Untick the option for "Allow editing directly in cells"
  • Now, go to Workbook_A/Sheet1/Cell A10 and double click it with your left mouse button!
  • There you go! Workbook_B is now open and Sheet1/Cell C20 is selected.

PROS:
This excel "trick" can be quite helpfull for those Excel users that usually work with several linked workbooks because it will save all that time that would be needed to search for the source file, open it and finally find the right cell.

CONS:
Well, there are two cons for this "trick":
  1. While the "Allow editing directly in cells" option is unticked you won't be able to edit the cell just by double clicking it. You have to use the F2 button to edit cells.
  2. If you have a comment as well as a formula, it will open the comment instead! You can overcome this CON with a "floating" comment using Data Validation.



No comments:

Post a Comment