Table Contents and the related section's printed page number

[jdash]

hello,

I have a workbook 13,272 lines long. In one of the columns is the division field in the format "XX XX XX" where the x's are numbers and there is a space between each pair. In the next column is the description of what that section of data is for. For example:

DIVISION DESCRIPTION
33 75 00 HIGH-VOLTAGE SWITCHGEAR AND PROTECTION DEVICES:

​

Unfortunately there are duplicates in the Division column such that there can be more than one "33 75 00" however in each of those cases I only need the first description. Also there are blanks and number of different formats (such as "XXXX") but I only need to look at the ones in the above format.

What I would like to do is create a Table of Contents page that can automatically pull each sequential division and the related description. I would do a backflip if I can also have the page # in which that division is calculated in a separate column.

I have tried to do a VLOOKUP but run into problems in how to skip the blanks and unrelated formats.

This is what I have: =VLOOKUP((IF(OR(DIVISION="",LEN(DIVISION)=4),"",DIVISION)),TOC,2,FALSE)

Where DIVISION and TOC are defined names. What happens is that the IF function is passing the description instead of going to the next cell to check the format to pull.

Please also if anyone knows how to pull the printed page number lmk- I have seen a few VB's that have been posted but I can't seem to tweek them rightly and I'm pretty VB blind.

Any thoughts or help would be much appreciated.
Thanks.

 

[jdash]

I'm thinking another approach might be to create a column in the TOC worksheet that pulls the row # of each division that I need, then do a VLOOKUP on that row # for the description. Similarly- can't seem to get that sequential division thing going on...

 

[jdash]

Another, and perhaps better(?) option is to create a new column in my division worksheet and test each column for the right format. If the right format is listed, place a number in the column. Then I can just VLOOKUP that number...

I have

=IF(OR($K5="",LEN($K5)=4),"",IF(LEN(K5)=8,SUMIF($L$3:L4,">0"),""))

only the sums get too large. Anyway to find the last number in the column and just add a 1 to it?

 

[jdash]

I managed some very convoluted method to get the TOC descriptions out. I'm going to hunt around more for page numbers.

I'd love to know of a simpler way to do it so suggestions still welcome.

Page numbering still TBD

 

[jdash]

For others who may be searching for a solution to getting current page numbers, you may try:

http://www.dailydoseofexcel.com/archives/2004/12/22/page-of-pages-in-a-cell/