How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Saturday

Dynamic Reference in Table of Contents in Google Sheets

Posted by   on

Using the functions Hyperlink, Address, Row, and Column, we can get a dynamic reference in a table of contents in Google Sheets.


The purpose of this method is to update the hyperlinked cell or range dynamically within the formula.


Why is this workaround required?


You may see the syntax of the Hyperlink function below.


HYPERLINK(URL, [link_label])

In this, the URL argument is not dynamic.


I mean, the specified URL, i.e., URL of a cell range or a cell address, won’t get updated dynamically in the formula.


That means the default reference in a table of contents formula will be an absolute reference if we use the Hyperlink function in the usual way.


Here are two examples.


How to Get Dynamic Reference in Table of Contents in Google Sheets


Dynamic Hyperlink to a Cell


I have this HYPERLINK formula in cell A3 that hyperlinks to cell C3.


=hyperlink("https://docs.google.com/spreadsheets/d/1gU1FCt....57&range=C3","Jump")

Note:- To get the link (URL)of cell C3 to use in the formula, right-click on the cell in question and select “Get link to this cell.”


Let’s see what happens when I delete column B and click on the hyperlink.


Usual Hyperlink Formula - Non-Dynamic

It’ll jump to C3 only!


We can make this reference in the hyperlink dynamic. So when we click on the link, it will take you to B3 instead of C3.


You can follow the below tips to get a dynamic reference cell address in the table of contents in Google Sheets.


Steps:


We must replace C3 in the URL part of the formula with a dynamic cell reference that involves the functions ADDRESS, ROW, and COLUMN.


The current cell reference is a text string. So it won’t update when you move the source cell.


Replace C3 with =address(row(C3),column(C3),4) as below.


=hyperlink("https://docs.google.com/spreadsheets/d/1gU1FCt....57&range="&address(row(C3),column(C3),4),"Jump")

Dynamic Hyperlink Formula

In your table of contents, you may want to link to a different sheet/tab.


In that case, include the corresponding sheet name with the cell reference in the Row and Column part of the formula.


E.g.


=address(row(Jan!C3),column(Jan!C3),4)

Dynamic Hyperlink to a Cell Range


We need to play around with the above Address formula to get a dynamic reference range in a Table of Contents in Google Sheets.


Example:


I have a total of four tabs in a Sheet as below, and their names are “Sheet1,” “Jan,” “Feb,” and “Mar.”


Dynamic Reference in Table of Contents

In Sheet1!B4:B6, I have created a Table of Contents.


Cell B4, B5, and B6 link to the range B2:C5 in their respective tabs.


I’ll show you how to hyperlink to a range in the “Jan” tab in cell B4.


Go to the “Jan” tab and select the range B2:C5.


Right-click and select “Get link to this range.”


Use that range to code a Hyperlink formula in cell B4 in “Sheet1” as below.


=hyperlink("https://docs.google.com/spreadsheets/d/1R2RB89nMxvGv....481962&range=B2:C5","Jan")

Now let’s use the Address formula to make dynamic reference in cell B4 in the above Table of Contents.


For that, replace B2:C5 with the below formula.


=address(row(Jan!B2),column(Jan!B2),4)&":"&address(row(Jan!C5),column(Jan!C5),4)

The final formula will look as follows.


=hyperlink("https://docs.google.com/spreadsheets/d/1R2RB89nMxvGv....481962&range="&address(row(Jan!B2),column(Jan!B2),4)&":"&address(row(Jan!C5),column(Jan!C5),4),"Jan")

To dynamically link to other sheets in the Table of Contents, follow the above steps.


Pros and Cons of Using Dynamic Reference in Table of Contents


The above workaround method has its pros and cons.


Pros:



  1. Single-cell reference. – Cell automatically adjusts when you move the source cell by inserting/deleting rows or columns or cut-paste the cell to a new row/column.

  2. Cell range – Cell range automatically adjusts when you move the source cell range by inserting/deleting rows or columns or cut-paste the cell range to a new range.


Cons:



  1. Single-cell – Nothing notable.

  2. Cell range – You should take care to move the whole-cell range. It may make your dynamic range messy if you only move the first or last cell in the range.


That’s all.


Thanks for the stay. Enjoy!


Hyperlink Resources

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!