• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorry not sure how to explain it in a title!

tarynmahon

tarynmahon

Member
Hi,
I'm trying to get a formula that looks up the amount of months remaining on a grant and then automatically adds in as many date columns as necessary.
For instance, I have uploaded a file which has 8 months remaining, so the month columns that I will need populating are Apr 16 - Nov 16.
The data will be updated every month so these months will change, so I though the best way to start the months would be the EOMONTH formula that I have entered in E2 but I'm not sure how to continue it on?
Ideally as well I would like a total column at the end of however many months are remaining.
There will be a tab for each different grant.
Any help will be greatly appreciated.
 

Attachments

  • Chandoo.xlsx
    39.4 KB · Views: 5
Hi tarynmahon,

Since we'll need to know the 8 number in other calculations, I'd first suggest changing the formula in A1 to just return the number, and move the " Months remaining" into B1.
upload_2016-3-18_8-30-46.png

Then, in E2, we can put this formula:
=IF(COLUMNS($E1:E1)>$A$1,"",EOMONTH(TODAY(),COLUMNS($E1:E1)))

Copy as far right as you think will ever be needed.

Since the amount of columns will change, if you want a totals column, I would suggest putting it on the left, rather than the right, as it'll be easier to setup. The formulas in attached don't work on my end since it links to a different file, but hopefully you can see the setup idea.
 

Attachments

  • Chandoo LM.xlsx
    42.2 KB · Views: 5
I know you said it would be tricky to put the total on the right but do you think maybe including IF(cell="","Total" and incorporating a IF(Cell to the left<>"Total","" or will that not work? Not sure how I'd re-write the formula yet.
 
Sorta. While you could get the header to appear correctly, doing something like:
=IF(COLUMNS($E1:E1)=$A$1+1,"Total",IF(COLUMNS($E1:E1)>$A$1,"",EOMONTH(TODAY(),COLUMNS($E1:E1))))

the problem is potentially with the data underneath. I'm not sure what information appears in the "body" underneath the headers...if it's manually entered info, then the above won't work, as you'd have to go back and put in all the SUM formulas. If it's formula based, then you could do it...using a similar IF type structure, like this pseudoformula:
=IF(ISNUMBER(HeaderCell),NormalFormulaHere,IF(HeaderCell = "Total",SUM(Cells to left),"")
 
You must log in or register to reply here.
Back
Top