Table of contents in a large sheet; use of forms

Tomer · 08/02/18

hi,

I use one large sheet to manage many different legal tasks across my team (1,000 rows currently). The sheet is divided to many sub sections/categories, main tasks, sub tasks etc. Wanting to add a new main task to a category, the user needs to scroll down to find the right category which is time consuming.

Two questions please:

Can I create something like a table of contents in the beginning of the sheet, linked to the relevant category in the sheet? (by clicking on it the user to be taken to the relevant category in the sheet)
Can I create a form to be able to add a new main task to the relevant section/category? (was successful at only creating a form that adds a row to the end of the sheet, which then the user needs to cut and paste to the right section).

thnaks

Andrée Starå · 08/02/18

Hi,

You could have a kind of ToC at the beginning of the sheet that shows which rows the different sections start at approximately and use the shortcut (jump to row) to get to that section. You could also use filters to get to different sections.
The form will always be placed on the top or bottom of the sheet and you move it to where you want it to be.

Would that work for you?

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

Tomer · 08/03/18

HI Andree

thanks for this.

1. the row numbers change as I add more tasks and sub tasks under existing categories. filters are great for viewing, but we struggle with adding new task when viewing via a filter.

2. yeah, I thought so. is not really fit for my purpose.

thanks,,

Tomer

Andrée Starå · 08/03/18

Hi again,

Happy to help!

1. What is the problem when you want to add new tasks while in a filter?

2. Depending on your use case you could have different forms with prefilled fields or choose a category so that it will show in the different filters. You could also move the new rows when filtered.

Would that work better for you?

Andrée Starå · 09/17/18

Hi again,

I have a workaround that might help.

Create a Table of content section in the sheet (at the top)
Create a new column (Count)
Use the following formula in the Count column.

=1 + 0 (Copy down the whole sheet)
Create a new column (RowID)
Use the following formula in the RowID column, cell 1. =COUNT(Count$1:Count@row) (Copy down the whole sheet)
Create a new column (Row)
Use the following formula in the Row column.

="CTRL/CMD+G " + RowID@row
Link the different sections of the sheet to the ToC in the at the top with the following formula. =Row16 (for row 16)
Try it by pressing the CTRL/CMD + G combination and type the number from the ToC section that you want to jump to.
You can also combine the Row and Task Name column and hide the Count, RowID and Row columns.

Please see the attached link/screenshot for more information.

https://cl.ly/d71b0a

Depending on your country you’ll need to exchange the semi-colon to a comma in the above formulas.

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

莎伦Isaacson · 06/24/19

Hi there,

Do you by chance still have this sheet that you could share with me? I'm setting up something similar, would like to compare to yours!

莎伦

Andrée Starå · 06/25/19

Hi Sharron,

I have, and I'd be happy to share it with you. Can you email me @[email protected]and I'll share it.

有一个很好的一天!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Table of contents in a large sheet; use of forms

Comments

Categories