Dynamic table of contents in Excel with formulas (no VBA)

Показать описание

Create a table of contents with each sheet's name as a clickable hyperlink taking you to that sheet using formulas only, with the brand new release of Microsoft 365 dynamic Array functions VSTACK and TEXTAFTER, also using the longer time functions, CELL, HYPERLINK and the 3D references formula concept means you only need to list the first and last sheet and every sheet in between will be dynamically included, so if sheets are deleted then its fully dynamic but if new ones are inserted, a small copy & paste adjustment is needed for each sheet.

David Benaim

Рекомендации по теме

Комментарии

Great tips David. Thanks for this!
Couple of thoughts:

1. On your 3D reference you needn't change your syntax manually. Instead you could type "=" into the cell where you want the formula, then select your first sheet with a left mouse click, then hold shift, left mouse click your last sheet and then select the cell(s) in that last sheet and Excel will write out your reference correctly.
2. You could have used a dynamic reference in the HYPERLINK formula that automatically spills your hyperlinks based on the VSTACK array spilled. It would look like this: =HYPERLINK("#'"&C6#&"'!A1", C6#)
So the # references would automatically sort out your 0's without needing to change cell formatting. You would however need to format the spilled references to also look like a hyperlink as Excel doesn't do that automatically.

RenierWessels

Thanks for the tip, it will be helpful.
To complement your solution, you could add a # at the end of the HYPERLINK function's friendly name reference and it will automatically extend to the right number of sheets (e.g. =HYPERLINK("#'" & C6 & "'!A1", C6#))

labonnes

Hey @David I have a tip for you
To select all sheets, Just Right-click a sheet tab, and then click the Select All Sheets option 🤯🤯🤯🤯

LotfiGARZOUN

Cool, Freedom from old method of VBA named manager

rajanghadi

I'm sure you have great content but you are talking so fast I can barely understand you. Slow down a little please.

SandraRotenberg

Dynamic table of contents in Excel with formulas (no VBA)

How to make a table of contents in word

How to Create Table of Contents in Word | Automatic & Hyperlinked

Dynamic table of contents in Excel with formulas (no VBA)

Create a Dynamic Table of Contents with this FREE plugin & GenerateBlocks (Upgrade Your Blog!)

Using Headers to Dynamic Tables of Contents in Microsoft Word

How to Create Dynamic Table of Contents in Excel

The Best Excel Table Of Contents That Automatically Updates

How to Make a Table of Contents in Powerpoint in 60 Seconds

Dynamic Table of Contents in Word (How The Experts Do It)

How to Make Table of Contents in Word

How To Create a Clickable Table of Contents in Microsoft Word

How To Create a Table of Contents in WordPress Posts and Pages Dynamically

How to Create a Table of Contents in Google Docs | Update a Table of Contents in Google Docs

How to create a table of contents in excel with hyperlinks

How to Link Table of Contents to Headings in Word

Add a Dynamic Table of Contents to Static HTML in Next.js & React with Rehype

Create a Table of Contents in Tableau - Static or Dynamic!

Creating a Table of Contents in InDesign

Dynamic Table of Contents in FileMaker - Nick Hunter - Day 1

Dynamic Table of content in React | React Components #3

Create a Clickable Table of Contents in Microsoft Word

Framer Tutorial: Dynamic Table of Contents (CMS) - Zero Code

Complete Guide to Creating Dynamic Table Components on Figma

How to make table captions and a list of tables in word