×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

Excel:set indent level based on value of a cell

Excel:set indent level based on value of a cell

(OP)
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

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

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)

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

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

CODE

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

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

(OP)
Joerd,

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


Cheers,

Stick1

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

(OP)
Joerd,

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

Thanks,

Stick1

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

Just add more lines like:
   c.Offset(0, 1).HorizontalAlignment = xlLeft
   c.Offset(0, 1).IndentLevel = a
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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources