Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I am making a report, and it consists of a column with companies, and a date. The date is formatted in various ways, based on information submitted by the company. How can I combine the two columns and have the date automatically repeat the form shown? For instance we have


    Company A May 8, 2010
    Company B 01-Apr-07
    Company C July 2007

    I would like to combine these into one cell, and have the date formatting remain as initially shown.

    (I think that this has been asked before, but I cannot find the proper search technique to find it.)
    [I have been here for years; I had to get things restarted]

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    I couldn't get this done w/o a lookup table and even then there are limitations. However maybe the attached workbook will help.

    Regards
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Oh, my! The contortions we must go through if we don't do it the way Microsoft deems proper!

    You would think by now that the results/formatting could be copied, and not the base date number. After all, this has been a nuisance since VisiCalc (or at least Lotus 1-2-3 from 1983).

    But thanks. I never thought of a lookup table. And, I see the limitations.
    [I have been here for years; I had to get things restarted]

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    You know how it is...fixing problems - no sell --- New Feature - Sold! At least that's the way MS sees it or this would have been fixed long ago.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The joys of XLM - see attached.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    A thumbs up for you!
    I tried using Cell() in my formula and couldn't get it to work that's when I went to the lookup table. Reverting to the old XL macro code is just sheer genius and a very long memory. Well Done!

    For those of you who might not be able to figure out what Rory did see graphic below.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by RetiredGeek View Post
    and a very long memory.*
    I'm older than I look.**


    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I thought you were aging backwards (and would end life as a twinkle in your father's eye... )

    Steve

  9. #9
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Rory,

    A thumbs up for you!
    I tried using Cell() in my formula and couldn't get it to work that's when I went to the lookup table. Reverting to the old XL macro code is just sheer genius and a very long memory. Well Done!
    OK, it took me a while to find the NAME listing, and I see I have a lot to learn about that. But WHERE do I find the actual macro? GET.CELL is its name, yes? But when I look in the Macro listing, I only see the macros that I created (in 2003 incidentally) I would like to parse it so I can see what is happening.

    And, would you be so kind as to explain the actual commands. I see the =GET.CELL(7,INDIRECT("RC[-1]",0)) listing, but I fail to understand any of it.
    ---- What does the 7 describe?
    ---- I am unfortunately far too weak in formulas. I have read the instructions for INDIRECT, and they make sense up to a point, but I cannot figure out the RC[-1],0 references.

    And thanks to all
    [I have been here for years; I had to get things restarted]

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    There is no macro! Get.Cell an OLD function from Excel Version 4.0, which is still supported. So it is all in the definition of the Name! I just happen to have an old printed {yeah they used to do that} reference called "Function Reference Microsoft Excel" c1992! We're talking old here... The entry for Get.Cell covers 2.5 pages.

    From page 192:


    The 7 = Number format of the cell, as text (for example, "m/d/yy" or "General").

    When I get a chance I'll scan (scanner on wife's computer) the pages and post them.
    I scanned then OCRed the pages and edited them in word to correct the OCR errors and formatting. Hope I caught all the errors.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    JohnS0603 (2015-08-15)

  12. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can also download a file called Macrofun.exe from MS which has the help files for the old XLM functions.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. The Following User Says Thank You to rory For This Useful Post:

    JohnS0603 (2015-08-15)

  14. #12
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    You can also download a file called Macrofun.exe from MS which has the help files for the old XLM functions.
    Ah, if that were only the case. My machine is tied down tighter than a drum by IT folks. I have the office suite, and a few other minor tools (Zip, screengrabber, and a few other things) and that is it! I cannot even run an .exe file that requires no resources or installation.

    But, that old function, being part of 2007, is accessible to me.
    [I have been here for years; I had to get things restarted]

  15. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You should have a word with IT then and explain that they are just Help files zipped up. Can you download .zip files?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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