Nov 20 2018 04:46 AM
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 adds them as new rows?
So, for example, if I put number of months to 3, it should automatically look like this:
How is this possible?
Thanks.
Ian
Nov 20 2018 05:39 AM - edited Nov 20 2018 05:39 AM
SolutionHi,
I think the most robust way to do this is as follows:
Please find this in the attached file.
Hope that helps
Nov 20 2018 06:39 AM
Nov 20 2018 06:49 AM
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
Nov 20 2018 06:52 AM
Hi.
My mistake, I meant B2, not B1.
Thank you for the attached file too. I will check it out.
Nov 20 2018 04:17 PM
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.
Nov 20 2018 06:46 PM
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.
Nov 20 2018 07:54 PM - edited Nov 20 2018 07:59 PM
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!
Nov 21 2018 07:55 AM
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
Nov 21 2018 07:17 PM
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.
Nov 22 2018 09:43 AM
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.
Nov 23 2018 05:12 PM - edited Nov 23 2018 05:13 PM
Thank you so much for all the help! I got it working.
Nov 20 2018 05:39 AM - edited Nov 20 2018 05:39 AM
SolutionHi,
I think the most robust way to do this is as follows:
Please find this in the attached file.
Hope that helps