Feb 10, 2012

TOCing Excel [40/365]

'Where are your updates?!' A friend, dear critic and voracious reader asked me.
Since I did feel guilty about lagging so far behind in blogging that I decided I owe my readers - YOU, an apology.

I've been as busy as a rat scrapping coconut off the husk.
That is if you want to term doing what you love as work.

I was faced with a dilemma at work. I could never resist a good ol' challenge and I was not about to start doing it now.I had to organize a really huge MS Excel file. The spreadsheet had about 300 tables with text in them. Not your typical spreadsheet, you see. I had to figure out a way to navigate to individual table head without having to scroll down. I had to create a Table of Content (TOC). Easier said than done.
Considering how Excel was never meant to have a TOC or 600 pages of text, I knew I was staring at something beyond the ordinary.

Once I'd decided that I had to create a TOC, I set about transferring the tables from the Excel spreadsheet to a Word document. I created an heading style using a customized style template, and created a TOC. That was the easy part.
I tried importing the Word document to Excel but the converting it to a CSV file only robbed it off its formatting.
But then I hit upon the idea of bookmarking the individual tables on the spreadsheet. That would have worked if we had only one table in a sheet. Considering that we had 300 tables, this could made updating the sheet and managing the document a herculean task. But this is went I stumbled across how every heading that was linked to the TOC within the Word document. Every heading when copied from the Word document had a reference point named '_TocXXXXXXXX'.
What I did next was to create a separate list with just the headings and hyperlink it to the 'Place in the document'. Voila!
I had a fully functional TOC within Excel!

There are some flaws to this as well. But I guess the flaw is also its greatest utility.
When copying the heading from the Word document and pasting it to the Excel spreadsheet, the value gets stuck to the cell. Now, this means if you delete the heading for any reason, you will still have the '_TocXXXXXXXX' value no matter what else you input in the field. But this also means that you can insert cells/rows/field above it and the heading retains the '_TocXXXXXXXX' value.

Now, my next challenge is to do is to create a customized footer within Excel that will show the Headings within the particular page and creating a specialized macro for formatting tables within Word.
Back to the drawing board.
Related Posts Plugin for WordPress, Blogger...