Table Contents and the related section's printed page number

jdash

New Member
Joined
Mar 14, 2011
Messages
18
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.
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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...
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,222,245
Messages
6,164,794
Members
451,917
Latest member
WEB78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top