INTHEBLACK October 2021 - Magazine - Page 73
CLICK HERE TO ACCESS
03
04
Extra material, a podcast
and a video from
Neale Blackwood CPA
Having created one formula, you can copy
and paste it to the other sheets. Once these
formulas are in the destination sheets, you
can link to them from the index sheet. This
captures the sheet name on the index sheet.
INDEX SHEET FORMULAS
Formulas to enter in the index sheet are the
same as the ones above, but they link to
another sheet, the destination sheet. In the
examples below, I have used cell A1 in the
instructions sheet.
SUBSCRIPTION VERSIONS
=LET(c,CELL("filename",Instructions!A1),
RIGHT(c,LEN(c)-SEARCH("]",c)))
All version formula
=RIGHT(CELL("filename”,Instructions!$A$1),LEN
(CELL("filename”,Instructions!A1))-SEARCH
("]",CELL("filename",Instructions!$A$1)))
You would need to create this formula
for each sheet whose name you want to
automatically update in the index sheet.
05
HYPERLINKED IMAGES
name formula. You can then link to that
cell from the index sheet. When the name
changes, the formula automatically updates
the index sheet.
Alternatively, you could enter the sheet
name formula in the index sheet and link to a
cell in the destination sheet. This will extract
the destination sheet name. The downside of
this technique is that the formula becomes
quite long. I have provided examples of both
formulas later in this article.
EXCEL VERSIONS
In practical terms, there are currently two
versions of Excel.
1. The subscription version, called Microsoft 365
or Excel 365, which has lots of new features.
2. All the older versions, which will not be
updated with the new features.
Below are two versions of each of the
two formulas mentioned above, one for the
subscription version and one for every other
version of Excel.
DESTINATION SHEET FORMULAS
These are entered in any cell in the destination
sheet. The same formula works no matter
which sheet or cell you enter it into.
Subscription version
=LET(c,CELL("filename",A1),RIGHT(c,LEN
(c)-SEARCH("]",c)))
All versions
=RIGHT(CELL("filename",A1),LEN(CELL("file
name",A1))-SEARCH("]",CELL("filename",A1)))
Earlier, I suggested including a hyperlink to
the index sheet in all the other sheets. One
way to do this is to use an image. Excel has
access to many types of images in the Insert
tab and the Illustrations section.
Let’s say we want to use a boomerang icon
to symbolise “going back” or “returning”. We
can add the icon to the sheet, right-click the
icon and choose Link. This is also how you
create a standard link in a cell. The keyboard
shortcut to insert a Hyperlink is Ctrl + k.
As shown in Figure 05, click the Place in
This Document icon on the left, choose the
index sheet and click OK.
Clicking the boomerang icon will now return
you to the index sheet. Once created, you can
copy and paste this icon onto other sheets
and the link will work. This link will break if the
index sheet is renamed.
ENTERING A FORMULA IN EVERY SHEET
In the companion video to this article, I share
a technique that can speed up destination
formula entry. The technique can also be used
for other tasks, and it enables you to quickly
enter the same formula in the same cell in
every sheet (or selected sheets) in a file.
intheblack.com October 2021 73