After doing some searches (and coming up empty) I figured I'd check and see if anyone has suggestions on some code to dynamically create an outline in one column that reflects the indentation level of the second column.
<script language="JavaScript" src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></script><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Excel Outline - Sandbox.xls</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: 12.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb755237"><input onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' value="Copy Formula" name="btCb873980" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb078704"></form><td style="width: 60px;" align="center" bgcolor="white"><select onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name="sltNb935705"><option value="" selected="selected">E1</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="Outline" name="txbFb426622"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="center" width="2%">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>E</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>F</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>G</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>H</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>1</center></td><td style="border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); border-width: 0.5pt; font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">Outline</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">Indented Level</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>2</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">b</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>3</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 1em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">a</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>4</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 1em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>5</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 1em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">d</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>6</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 2em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">d</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>7</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.1.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 3em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">g</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>8</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 2em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">a</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>9</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 2em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">a</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; width: 120pt; background-color: rgb(255, 255, 255);" align="left">Sheet1</td><td> </td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>
Basically, as I enter values / change the indent of column I would like the outline to adjust dynamically.
Here's the code that I have written so far. Issues with it:
a) Moving the indent 'back'/'up' one level is not accounted for
b) If an new row is inserted in the middle of the outline the cells below are not updated.
c) It's probably better to just start over from scratch... (I don't think my 'ReturnTail' function works properly either...)
It would also probably be easier (Preferable?) to write an new Excel function called, "Outline" that would take the arguments, "Previous Outline" and "Indented Values".
E.g.
Cell E3: "=Outline(PrevOutline,IndentedCell)"
Cell E3: "=Outline(E2,F3)"
Thanks for the help!
Dave
(This falls into the "I'd-rather-utilize-VBA-code-in-Excel-than-fight-with-Project"-bucket)
<script language="JavaScript" src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></script><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Excel Outline - Sandbox.xls</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: 12.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb755237"><input onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' value="Copy Formula" name="btCb873980" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb078704"></form><td style="width: 60px;" align="center" bgcolor="white"><select onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name="sltNb935705"><option value="" selected="selected">E1</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="Outline" name="txbFb426622"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="center" width="2%">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>E</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>F</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>G</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>H</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>1</center></td><td style="border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); border-width: 0.5pt; font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">Outline</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">Indented Level</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>2</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">b</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>3</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 1em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">a</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>4</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 1em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>5</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 1em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">d</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>6</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 2em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">d</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>7</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.1.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 3em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">g</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>8</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 2em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">a</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>9</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 2em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">a</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; width: 120pt; background-color: rgb(255, 255, 255);" align="left">Sheet1</td><td> </td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>
Basically, as I enter values / change the indent of column I would like the outline to adjust dynamically.
Here's the code that I have written so far. Issues with it:
a) Moving the indent 'back'/'up' one level is not accounted for
b) If an new row is inserted in the middle of the outline the cells below are not updated.
c) It's probably better to just start over from scratch... (I don't think my 'ReturnTail' function works properly either...)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("F:F"), Target) Is Nothing Then
'Get Previous Value:
IndentLevel = Target.IndentLevel
PrevOutline = Target.Offset(-1, -1).Value
PrevIndentLevel = Target.Offset(-1, 0).IndentLevel
PeriodCount = countsubstr(PrevOutline, ".")
If PrevOutline = "" Then
hierarchy = "1"
'Temporary Bug Fix:
PrevOutline = "0"
End If
'New level indented.
If IndentLevel - 1 = PrevIndentLevel Then
hierarchy = PrevOutline & ".1"
End If
'Step Back Level:
If IndentLevel < PrevIndentLevel Then
StepBack = PrevIndentLevel - IndentLevel
ct = 0
Do While ct < StepBack
PrevOutline = ReturnStart(PrevOutline, ".")
ct = ct + 1
Loop
MsgBox ("PrevOutline = " & PrevOutline)
LastTail = ReturnTail(PrevOutline, ".")
MsgBox LastTail
hierarchy = PrevOutline & "." & LastIncr
End If
If IndentLevel = PrevIndentLevel Then
LastTail = ReturnTail(PrevOutline, ".")
LastIncr = LastTail + 1
Initial = ReturnStart(PrevOutline, ".")
hierarchy = Initial & "." & LastIncr
End If
'If IsNumeric(PrevOutline) Then
' hierarchy = PrevOutline + 1
'End If
'Else: Hierachy = ReturnTail(PrevOutline, ".")
'Target.Offset(0, -1).Value = Target.IndentLevel
'MsgBox (ReturnTail(Target.Offset(0, -1), "."))
'MsgBox hierarchy
Target.Offset(0, -1).Value = hierarchy
End If
End Sub
Function ReturnTail(haystack, needle)
sTemp = StrReverse(haystack)
nPos = InStr(sTemp, needle)
nPos = Len(sTemp) - nPos
ReturnTail = Right(haystack, nPos)
End Function
Function ReturnStart(haystack, needle)
sTemp = StrReverse(haystack)
nPos = InStr(sTemp, needle)
nPos = Len(sTemp) - nPos
ReturnStart = Left(haystack, nPos)
End Function
Function countsubstr(ByVal haystack As String, ByVal needle As String)
Dim pos
pos = InStr(haystack, needle)
If pos > 0 Then
countsubstr = countsubstr(Mid(haystack, pos + 1), needle) + 1
Else
countsubstr = 0
End If
End Function
E.g.
Cell E3: "=Outline(PrevOutline,IndentedCell)"
Cell E3: "=Outline(E2,F3)"
Thanks for the help!
Dave
(This falls into the "I'd-rather-utilize-VBA-code-in-Excel-than-fight-with-Project"-bucket)