Can this be done with Excel formulas?

Discussion in 'Mac Apps and Mac App Store' started by Dreamkatcha, Jul 26, 2007.

  1. Dreamkatcha macrumors regular

    Dreamkatcha

    Joined:
    Mar 17, 2003
    Location:
    Manchester, England
    #1
    What I'm trying to do is get Excel to insert a set of months under year headings with two columns of data beside them (invoice no. and date of payment) based on the number of payment installments and a starting date I enter into a cell.

    For example...

    No. of installments: 8
    Starting date: 1st July 2007

    ...would create the output...

    2007

    July
    August
    September
    October
    November
    December

    2008

    January
    February

    The two columns next to the month entries can remain blank as they will be filled in by hand on the printouts, though I'd like horizontal lines to appear in the cells to write above.

    Any ideas?
     
  2. bmcgonag macrumors 65816

    bmcgonag

    Joined:
    Mar 20, 2007
    Location:
    Texas
    #2
    Not to point you away from here for most questions, but there is an excellent forum at www.utteraccess.com. These people are experts in everything from Excel to Access and so on.

    They can tell you exactly how to do what you are asking, no doubt.

    best,

    Brian
     
  3. Applespider macrumors G4

    Applespider

    Joined:
    Jan 20, 2004
    Location:
    looking through rose-tinted spectacles...
    #3
    What you're asking isn't an Excel standard function and would probably have to be written in VB.

    If that's all you need to do, you'd be quicker putting in the first couple of dates

    August 2007
    September 2007

    highlighting them and then using the bottom right corner to drag them downwards. Excel will automatically fill in October 2007 and so on.
     
  4. portent macrumors 6502a

    Joined:
    Feb 17, 2004
    #4
    Fixed that for ya.
     
  5. GoCubsGo macrumors Nehalem

    GoCubsGo

    Joined:
    Feb 19, 2005
    #5
    Are you talking about something like a payment schedule?
     
  6. gauchogolfer macrumors 603

    gauchogolfer

    Joined:
    Jan 28, 2005
    Location:
    American Riviera
    #6
    Why exactly do you think that's a fix?Excel can certainly can run Visual Basic. The lack of support for VB is one huge reason that I'll never be upgrading my version of Office on my home Mac.
     
  7. iSaint macrumors 603

    iSaint

    Joined:
    May 26, 2004
    Location:
    South Mississippi y'all, near the water!
    #7
    That's my question. It's rather easy...
     
  8. Dreamkatcha thread starter macrumors regular

    Dreamkatcha

    Joined:
    Mar 17, 2003
    Location:
    Manchester, England
    #8
    Thanks for the helpful replies everyone. Yes, I suppose you'd call it a payment schedule, except it will be used to manually record when customers actually paid say, January's recurring invoice, rather than when they were supposed to. Each company has, or will have, a schedule for each new deal.

    It's a lot of unnecessary hassle if you ask me seeing as all this information is already being tracked with Sage Line 50. I'm not entirely sure why these paper backups are held in such high regard because I've only worked for this company for a week. At this stage it's not a good idea to rock the boat by daring to suggest that a long-standing procedure is a waste of time.

    Anyway I've already setup this spreadsheet to work out half a dozen figures based on the values I enter (aside from the manual figures) so most of the hard work has already been automated. I just thought this extra step would be the icing on the cake, and impress the boss to boot.

    Like you say, Applespider, it's child's play to do the corner dragging thing. I can live with that. :)
     
  9. portent macrumors 6502a

    Joined:
    Feb 17, 2004
    #9
    AppleScript has a future; VBA does not. Writing new VBA code on the Mac is n unwise investment of your time. AppleScript, on the other hand, works fine in Office 11/2004 and will work fine in Office 12/2008. http://www.mactech.com/vba-transition-guide/

    Even on Office for Windows, VBA's days are numbered.
    http://en.wikipedia.org/wiki/Visual_Basic_for_Applications#Future
     

Share This Page