Excel:set indent level based on value of a cell 2

[stick1]

I am able to extract an indented BOM from our PLM software for placement into Excel 2003. The tab-delimited BOM file has the indents shown via a column of values. I would like to indent certain columns in Excel based on the value in the indent level column that has been imported.

Example:
A B C
Level Part No Description
.1 0001 Part1
..2 0002 Part2
..2 0003 Part3
...3 0004 Part4

I would like to add indents in columns B & C; 1 space for a Level column cell value of .1, 2 spaces for a value of ..2, etc.

Has anyone tried anything similar?

Thanks

 

[handleman]

Does the indent column actually have the dots in it, or just a number?

I doubt you will be able to do it directly with that cell, because text alignment isn't one of the things available in Conditional Formatting. You could build up the indents with spaces in an additional column with a formula like

=REPT(" ",A2)&B2

If your indent column actually has those dots in it, then your formula would have to be


=REPT(" ",LEN(A2)-1)&B2

Of course, this formula would go in your first blank row. You would then hide the actual data rows.

-handleman, CSWP (The new, easy test)

 

[joerd]

You can run a little macro after import, something like:

Option Explicit

Sub AlignColumns()
Dim c As Range, a As Variant

Set c = [A2]

Do While Not IsEmpty(c.Value)
    'get the value in the first column and strip the decimals
    a = CVar(Replace(c.Value, ".", ""))
    'you can also replace the decimal points in the first colum:
    '  c.Value = Replace(c.Value, ".", "")
    '
    'set indents of the first and second columns.
    'assume that these are formatted as "Text"!
    c.HorizontalAlignment = xlLeft
    c.IndentLevel = a
    c.Offset(0, 1).HorizontalAlignment = xlLeft
    c.Offset(0, 1).IndentLevel = a
    'go to the next row
    Set c = c.Offset(1, 0)
Loop
Set c = Nothing
End Sub

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[stick1]

Joerd,

Thanks for the code. I tried it and it works perfectly! A star for you is well deserved!


Cheers,

Stick1

 

[stick1]

Joerd,

One additional question if I may...how do I assign the indents to columns other than the first and second?

Thanks,

Stick1

 

[joerd]

Just add more lines like:
[tt] c.Offset(0, 1).HorizontalAlignment = xlLeft
c.Offset(0, 1).IndentLevel = a
[/tt]but replace the 0,1 by 0,2 and 0,3 and so forth.

The Offset(row,col) method gives you a cell that is (row) rows below the base cell c, and (col) columns to the right.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.