Workbook Sheet Index that works with Excel online

[Juliansmity]

Hi,

I would like to create an index sheet that creates hyperlinks to all sheets in the workbook that updates everytime it opens or there's a new worksheet added. I can do it using VBA but the problem is this doesnt work with excel online is there a way do it as the workbook is a online shared document with multiple users ?


Thanks

[CK76]

There are ways to do it, though it will require Office 365 subscription with MS Flow AFAIK.
Not sure how it would behave, if you have multiple concurrent users accessing workbook though.

Instead of triggering it on Workbook open. It should be triggered on file created / modified, using OneDrive / SharePoint trigger.

You can find some sample and templates if you search.

[Juliansmity]

There are ways to do it, though it will require Office 365 subscription with MS Flow AFAIK.
Not sure how it would behave, if you have multiple concurrent users accessing workbook though.

Instead of triggering it on Workbook open. It should be triggered on file created / modified, using OneDrive / SharePoint trigger.

You can find some sample and templates if you search.

Hi,

This is my O365 licenses
Attachment 668089

[CK76]

Can't see your attachment.

At any rate, log into your Office.com tenant, and check apps. It is likely that you have license for free version of MS Flow (Power Automate).
While you can't use more advanced connectors (premium). You can use standard ones including Excel.

[Juliansmity]

Hi, Yes I have the free version of Power automate. I've having a play and have set a trigger to getworks when a onedrive file is modified, tested it and works but I'm stuck on from this point.

[CK76]

Flow will be dependent on few things.

First, I'd make sure that index sheet has table. To add rows, it's best to work with table object.
Columns: Name, Link
Link is constructed using Hyperlink function using "#SheetName!A1" as link target.

Then you'd use "Get worksheets" and "List rows present in a table" steps as parallel steps.

Then for each parallel steps add "Select" Data operation action and extract out "Name".

Then you'd check to see if there is more items on "Get worksheets" side than "List rows..." side, using "Condition" step.

If true, then you'd use variable and Do While loop to add row(s) to table.

I'm stepping out now, but will see if I have time later to do more detailed sample.

[CK76]

Here's how I'd do it.

1. As mentioned make sure index page has Table with columns Name and Link.
2. Link formula is...
=Hyperlink("#'"&A2&"'!A1","Link")

3. Back in your flow after "Trigger" event. Add "Initialize Variable" action. Name it "Counter", Type Integer and Value 0.

4. Next Add "Get worksheets" action, and choose your workbook.

5. Add parallel branch, "List rows present in a table" and choose your table for links.

6. Below "Get worksheets" add "Select" data operation. From: dynamic content 'value' from "Get worksheets" step. Add Map, Key = "Name" and dynamic content 'Name' of "Get worksheets" step.

7. Do the same for "List rows..." branch as well.

8. Using "+ New Step" button, merge two branches into single flow. Add "Condition" control.

9. For left side of condition, use Expression. length(body('Select'))

10. Choose is greater than condition.

11. For right side of condition, use Expression. length(body('Select_2'))

12. Leave If no branch blank. Add "Set Variable" step to If yes branch. Choose 'Counter' variable, and value using Expression. length(body('Select_2'))

13. Add "Do until" control. Dynamic content 'Counter' is equal to Expression length(body('Select'))

14. Add "Add a row into a table" action, Choose the link table object. Add following Expression to "Name" column, leaving "Link" column blank.
split(string(body('Select')?[variables('Counter')]),'"')[3]

15. Finally add "Increment variable" step. 'Counter' by 1.

See below for overview of MS Flow.
0.JPG

And condition part expanded.
1.JPG

[Juliansmity]

Sorry to be a pain (really appreciate the help as new to ms flow ), I'm stuck on step 5 as cant seem to select a table, I've attached a jpg of step error

Capture2.JPG

[CK76]

Do you actually have named table?

It can't be just a list on the sheet. You need to select range and convert to table using "Format as Table" button in Home ribbon tab.

Oh and also, the left side of the mapping. Just type the text, no need for it to be dynamic.

[Juliansmity]

Doh!!!

Done that, now I have a problem with the variable on "list rows present in table", step 7, its not showing "Name" for map or key

Capture2.JPG
Capture3.JPG

[CK76]

Which column do you store sheet name in? I assume "Index" use that instead then.

It shouldn't impact downstream process. As that side is only used to check # of rows present in table.

[Juliansmity]

Completed and test run is say successful but it not actually added anything to the Index sheet table

Attachment 668258

Attachment 668259

Attachment 668260

[CK76]

In run history. Go into each step and expand. It will show you what the output is and which path the flow took.

FYI - Your attachment is broken.

[Juliansmity]

Looking at the history details it says this (hopefully the jpg attachment works this time)

Capture2.JPG

[CK76]

So condition wasn't met, and the flow branched to "If no" path and finished processing.

What is your condition argument? Including expression used.

Note: You can click on "..." at top right of the Condition step. "Copy to my clipboard" and paste it into text editor. Use CTRL+F to find "condition" string.
There you'll find "expresstion" followed by json string for the step.