SOLVED

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

Iron Contributor

Hi Everyone.

 

I have a spreadsheet that looks like this:

 

ExcelSpreadsheet.PNG

 

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 adds them as new rows?

So, for example, if I put number of months to 3, it should automatically look like this:

ExcelSpreadsheet2.PNG

How is this possible?

Thanks.

Ian

 

11 Replies
best response confirmed by ianwuk (Iron Contributor)
Solution

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

 

Automatically fill rows with dates based on cell value.png

 

Please find this in the attached file.

Hope that helps

Thanks for replying. Is there any way for the new date cells to be added automatically when I put a number in to cell B2?

Thanks.

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

Hi.

 

My mistake, I meant B2, not B1.

 

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

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 :

 

Capture.PNG

 

Thanks for all the help.

Yes, this is possible!

 

Put this formula in cell B5 and then fill it down:

=IF(A5<>"",ROWS($A$1:A1),"")

 

It's also in the attached file.

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!

Capture.PNG

Hi,

 

With regards to the blank cells, I think that's because you change the values locations!

So please change the formula as well as follows:

=IF(C13<>"",ROWS($A$1:A1),"")

Put this formula in cell B13 and then drag it down.

 

But with regards to the rest, for better accessibility, I would suggest you create another post for it because it's a new question, and it's not related to the main thing you asked here.

 

Regards,

Haytham

Hi @Haytham Amairah, sorry, I have another question please.

For the formula below, how can we edit it so that if the date we put in cell B2 is the 29th, 30th or 31st (i.e. 31st December, 2018) the next date should be last day of the month, for the next date in the list if it doesn't exist (there is no 31st in the following month).

=IF(ROW(A1)>=$B$1,"",EDATE($A$4,ROW(A1)))

i.e. in cell B2 I put 31st December 2018 it should display this in cells A4, A5, A6 etc.:

Cell A4 - 31st December 2018
Cell A5 - 31st January 2019

Cell A6 - 28th February 2019 (not 31st February)

 

Basically, it should take in to account that not all months may have 29, 30 or 31 in them, i.e. leap years.

Is that possible?

Many thanks.

Sorry for the late reply!

 

This is can be done using the same solution in my last reply!

Please check out the attached workbook in that reply again.

 

Automatically fill rows with dates based on cell value #2.png

Thank you so much for all the help!  I got it working.

1 best response

Accepted Solutions
best response confirmed by ianwuk (Iron Contributor)
Solution

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

 

Automatically fill rows with dates based on cell value.png

 

Please find this in the attached file.

Hope that helps

View solution in original post