Forum Discussion

ianwuk's avatar
ianwuk
Iron Contributor
Nov 20, 2018
Solved

How to automatically fill rows with dates based on cell value?

Hi Everyone.   I have a spreadsheet that looks like this:     How can I make it for whatever number I put in B2 it takes the date in A4 and extends it by the number of months from B2 and a...
excel
Formulas and Functions
Macros and VBA
  • Haytham Amairah's avatar
    Haytham Amairah
    Nov 20, 2018

    Hi,

     

    I think the most robust way to do this is as follows:

    • Link cell A4 to the cell B2 by just using this formula in cell A4 =B2
    • Then use this formula in the cell A5 =IF(ROW(A1)>=$B$1,"",EDATE($A$4,ROW(A1)))
    • And then drag it down to whatever limit you want

     

     

    Please find this in the attached file.

    Hope that helps

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Nov 20, 2018

Hi,

 

As the screenshot you attached, the number of months is in the cell B1 not in cell B2!

What is in cell B2 is the start date!

 

So the dates should automatically be filled in cells when you change the number in cell B1.

Please see the attached file in my previous reply to test that!

 

Regards

ianwuk's avatar
ianwuk
Iron Contributor
Nov 20, 2018

Hi.

 

My mistake, I meant B2, not B1.

 

Thank you for the attached file too.  I will check it out.

  • ianwuk's avatar
    ianwuk
    Iron Contributor
    Nov 21, 2018

    Is it also possible to use your formula and modify it so that depending on the number I put in B1 it then, in cell B4, counts up to that number?  I want to use it as repayment numbers.

     

    So, it would look like this if I put 6 in B5 :

     

     

    Thanks for all the help.

      • ianwuk's avatar
        ianwuk
        Iron Contributor
        Nov 21, 2018

        Hi Haytham.

        Thanks for all your help so far.

         

        Here is what I now have.

         

        Do you know why cells B14 and B15 are blank, from your formula in the last reply?

        Also, in cell C20 I am trying to calculate the final balance.

        It needs to be something like this:

        IF B18 = B4 (because that would be the last monthly payment based on the number entered), <IF TRUE> F18 + D18 (outstanding principal + interest), <IF FALSE> B9 (monthly payment amount).

        Is that correct syntax?

        It seems to work if cell B4 is 6, but if I change that to any other number, then it all breaks - how can it be made to dynamically perform all calculations in the sheet based on the number in cell B4?

        Also, in cell H18, because I use 6 months, how can I get the correct number of days in the month if I don't have the next month to compare it to, like my formula?

        Thanks so much!

Resources