+ Reply to Thread
Results 1 to 16 of 16

Indent text to extent of hierarchical outline level

  1. 05-20-2013, 09:19 PM #1
    snapfade
    snapfade is offline
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Indent text to extent of hierarchical outline level

    Column A has numbers separated by dots to indicate level like this:

    1.1
    1.1.1
    1.1.1.1
    1.1.1.1.1

    Column B contains a text description like this:

    Description one
    Description two
    Description three
    description four

    Goal: indent values in Column B three spaces for the COUNT OF DOTS GREATER THAN ONE in column A, something like this:

    Description one
    Description two
    Description three
    Description four

    Thanks in advance for your suggestions.
    Reply With QuoteRegister To Reply
  2. 05-20-2013, 10:38 PM #2
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Indent text to extent of hierarchical outline level

    Hi, snapfade,

    maybe like this (data starts at row 1, adjust to your needs):
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you
    Reply With QuoteRegister To Reply
  3. 05-20-2013, 10:38 PM #3
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Indent text to extent of hierarchical outline level

    Hi SnapFade,

    See if this is a fade - it's untested:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  4. 05-20-2013, 10:41 PM #4
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Indent text to extent of hierarchical outline level

    Hi, xladept,

    Iīd loved to see your code if the indention is 16.

    Ciao,
    Holger
    Reply With QuoteRegister To Reply
  5. 05-21-2013, 02:47 AM #5
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Indent text to extent of hierarchical outline level

    Goal: indent values in Column B three spaces for the COUNT OF DOTS GREATER THAN ONE in column A, something like this:
    Hey Holger - your observation is unworthy of you!
    Last edited by xladept; 05-21-2013 at 02:57 AM.
    Reply With QuoteRegister To Reply
  6. 05-21-2013, 11:41 AM #6
    snapfade
    snapfade is offline
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Indent text to extent of hierarchical outline level

    Thanks Holger and xladept,

    After you two kiss and make up I have another question for you: Holger's use of LEN to determine the dots is something I should have thought of but didn't. Now that I see it though, I am wondering if this couldn't be done in conditional formatting though I am not sure how to indent with CF so I turned to VBA. Any thoughts on conditional formatting?

    Also, how and where would this macro run? could it run when a cell in B is updated or would it require a button (hope not) or would it do its work when the page is opened? I guess it would be best as text is entered in column B but I am open to suggestions.

    Thanks again.
    Snapfade
    Reply With QuoteRegister To Reply
  7. 05-21-2013, 01:38 PM #7
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Indent text to extent of hierarchical outline level

    Hi, snapfade,

    I donīt know if there is a solution with Conditional Foprmatting. If you want to use VBA there are two possible way of doing so.

    1. IndentLevel
    Please Login or Register  to view this content.
    2. spaces:
    Please Login or Register  to view this content.
    Only one of the procedures will work behind the sheet you want to monitor (right click on worksheet tab, View code, code goes into the code window).

    Ciao,
    Holger
    Reply With QuoteRegister To Reply
  8. 05-21-2013, 02:05 PM #8
    snapfade
    snapfade is offline
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Indent text to extent of hierarchical outline level

    Thanks, Holger.

    It works beautifully! Thanks ever so much.

    Thanks,
    Snapfade
    Last edited by snapfade; 05-21-2013 at 02:12 PM. Reason: my bad!
    Reply With QuoteRegister To Reply
  9. 05-21-2013, 02:13 PM #9
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Indent text to extent of hierarchical outline level

    Hi, snapfade,

    workbook isnīt saved in a trusted locaation, macros arenīt allowed?

    Please see how the attached workbook is doing.

    Ciao,
    Holger
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 05-21-2013, 02:14 PM #10
    snapfade
    snapfade is offline
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Indent text to extent of hierarchical outline level

    Sorry, Holger; I found the problem and it was me. I edited the response but you were too quick. Thanks again, Holger. Your help was wonderful. tom
    Reply With QuoteRegister To Reply
  11. 05-21-2013, 02:20 PM #11
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Indent text to extent of hierarchical outline level

    Hi SnapFade,

    This time the code is tested and, thanks to Holger for teaching us a better way, the code is flexible:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 05-21-2013, 02:25 PM #12
    snapfade
    snapfade is offline
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Indent text to extent of hierarchical outline level

    Hi xladept,

    Thanks for the effort. I just successfully added Holger's solution but I would like to know more about yours. It is not immediately clear to me how this works or when it takes off. How do I implement and what makes it flexible?

    Thanks again!
    snapfade
    Reply With QuoteRegister To Reply
  13. 05-21-2013, 02:28 PM #13
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Indent text to extent of hierarchical outline level

    Hi, xladept,

    apart from me finding the code hard to read maybe you should use Trim to avoid multiple blanks before the contents in case the macro is run multiple times.

    Ciao,
    Holger.
    Reply With QuoteRegister To Reply
  14. 05-21-2013, 03:18 PM #14
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Indent text to extent of hierarchical outline level

    Thanks for the effort. I just successfully added Holger's solution but I would like to know more about yours. It is not immediately clear to me how this works or when it takes off. How do I implement and what makes it flexible?
    It works in a similar manner as Holger's, we count the dots by the difference in length between the original and dotless strings - (the Do...Loop erases the dots) - It's flexible because if you wanted a different ratio, such as 4 to be able to indent 16 for instance, you have only to change the value of n (red). You'd need to call it with, most likely The Worksheet_Change event procedure:
    In the Sheet
    Please Login or Register  to view this content.
    In the sheet or in a module
    Please Login or Register  to view this content.
    @ Holger - Good Point with the Trim!
    Reply With QuoteRegister To Reply
  15. 05-21-2013, 03:23 PM #15
    snapfade
    snapfade is offline
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Indent text to extent of hierarchical outline level

    Thank you XLAdept. I really appreciate the help. tom
    Reply With QuoteRegister To Reply
  16. 05-21-2013, 03:24 PM #16
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Indent text to extent of hierarchical outline level

    Your welcome!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1