Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    All the Custom Formats You Want-Almost (Excel any)

    Hi All,

    A discussion on this post on Special Formatting for Output made me realize a way to get around the limit on the number of custom formats you can have. A number of posts have addressed this limitation.

    Mostafa Sherif asked the question on that post:
    What does it mean to have the number format as $B$1? Does this mean that the number format is the same as the format of cell $B$1 or does it mean that the format is entered in cell $B$1 as a string?

    Mostafa is a friend of mine and had asked me the question. More generally, this dealt with:
    =TEXT(numeric-string,format)
    but particularly of a form
    =TEXT(B1,B1)

    Sammy B answered:
    The second: "the format is entered in cell $B$1 as a string," in this case B1 contained '0.00000E+00 (the single quote entered it as text).

    So, if you enter a bunch of formats into a spreadsheet as Sammy B indicates, then it is possible to do something like:
    =TEXT(expression,INDIRECT(cell-reference))

    which allows you to define your own custom formats directly in the spreadsheet rather than the Format | Cell | Number | Custom dialog.

    Of course, you don't need to use the INDIRECT function but can reference a cell directly. But use of INDIRECT gives more power. And if you need the cell where =TEXT is entered to be a value, then just use
    =VALUE(TEXT(.....

    Now there do seem to be some limitations as to the format that you can enter into a cell. You can enter multiple parts, like true custom formatting allows, conditions, even text for display as part of the cell. But it looks like you cannot enter a color into the cell. Haven't explored other aspects.

    I don't think I'd suggest this as a general approach because formating a cell using Format | Cell and choosing Custom is more intuitive. But just thought I'd mention this. Of course, there is a limitation based on the number of cells you can put formats into but that number is something like...well, it's a big number (much bigger than the about 250-300 custom formats that Excel allows).

    Fred

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: All the Custom Formats You Want-Almost (Excel any)

    You could tack your routine onto one of your shortcut menus (using VBA in personal.xls). That lets you quickly drop it into the spreadsheet of your choice. Personally, I use the Worksheet Tabs menu (the arrows at the bottom left hand corner). As I don't go anywhere near the 250-300 mark, it's just a quick way of resetting the definitions (and choices) in the Style dialog box.

    HTH
    Grüße

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: All the Custom Formats You Want-Almost (Excel any)

    Hi,

    I'm not sure if I understood how your post related to mine. But is there something I'm missing about your posting. Mine had nothing to do with Worksheet tabs in the lower left corner nor with Styles. I also didn't really provide a routine of any kind nor was I looking for a solution to a problem. I was offering a way of doing something that I had discovered with custom formats that appears to have been a limitation from other posts.

    But I'd like to know where you were going in case I may have missed something that could be useful to me.

    Fred

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: All the Custom Formats You Want-Almost (Excel any)

    Fred,

    I found your post interesting enough to consider how to manage so many Custom Formats readily and simply.

    Provided you have the luxury of always starting with clean workbooks (or having an enforced House Style), then you could build these Custom Formats into your default template. House Styles are common for documents, but I have seldom seen anything resembling an enforced House Style for numbers. This gives rise to the alternative of importing the Custom Formats from their spreadsheet store into the workbooks that pass through your hands. Rather than constantly opening a source spreadsheet, one could store the formats in the spreadsheet itself. This would require a "routine" - hence the reference to a macro.

    In turn, I am not clear as to what distinction you are drawing between a Custom Format (dug out of the Format Cells option) and a Custom Style. They both draw on the Format Cells dialog box. To a greater or lesser extent, anyone gives a Custom Format a name. With a name it can be stored and accessed by a macro - as a Style (or as a Named Range). If the same 250-300 limit also applies to Styles, provided Custom Formats.xls is a one sheet workbook, the "routine" macro could import the entire file.

    <pre>Windows("Custom Formats").Activate
    Sheets("Formats").Select
    Sheets("Formats").Move Before:=Workbooks([Target]).Sheets(1)</pre>

    The macro would need to store and access the name of the active workbook.

    Turning to the reference to shortcut menus (Workbook Tabs), one could just as easily use a custom icon on a toolbar. I prefer to keep "one-off" (per workbook) macros as icons on an out of the way shortcut menu, rather than tripping over them when I am looking for more heavily used features on toolbars - or hunting for them in the Macro dialog box.
    Grüße

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: All the Custom Formats You Want-Almost (Excel any)

    Hi,

    I'm beginning to see the linkages.

    From my knowledge, styles offer a richer set of capabilities than formats do. In addition to saying how the digits appear (assuming a numeric cell for the moment), a style allows you alot more - bold font, borders for the cell, etc. But I'm not aware of any limit on how many styles one can have (nor have I seen the subject ever raised - which may be because not many people use Styles in Excel).

    As far as a house styles/formats for the numbers, I was thinking that maybe the desired styles might be stored in a special "format" worksheet perhaps in Personal.xls or some other template, or a separate workbook like your VBA has. Then you'd be able to enforce the use of formats for numbers - perhaps by using a macro that presented a user form of styles to choose from that was limited to those on the "format" worksheet.

    I agree with your comment on keeping one-off macros outof the way.

    So I'd be curious to see how you put this all together.

    Fred

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: All the Custom Formats You Want-Almost (Excel any)

    Styles go back to (at least) Excel 3. One thing that surprises advanced users from time to time is that the formats for Currency, Comma and Percent picked up by the default icons are NOT hard-coded. Thus they will pick up whatever's currently in the Style dialog box as, say, Percent - even if it's Polka-Dot alphas <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Customising numbers in Styles will only add to the Custom Number Formats; so that the 225 limit (for non-defaults) you mentioned in your post this post.

    A list of heavily used Styles can be defined up front (see my first post) and accessed as a Customised Pull-Down Menu or a Sub-menu on a Shortcut Menu.
    Grüße

Posting Permissions

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