Can this be done with Excel formulas?

Dreamkatcha

macrumors regular
Original poster
Mar 17, 2003
167
0
Manchester, England
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?
 

bmcgonag

macrumors 65816
Mar 20, 2007
1,077
0
Texas
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
 

Applespider

macrumors G4
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.
 

Dreamkatcha

macrumors regular
Original poster
Mar 17, 2003
167
0
Manchester, England
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. :)
 

portent

macrumors 6502a
Feb 17, 2004
623
2
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.
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