Hi
can someone please help me?
lets say, I owe 15462 in the bank, currency dont matter here, that is what I owe right now, but I want to have a cell in the frontpage with the amount left, so can I make a line called =remaining-each month
the amount should then each month be substracted from the new month and so on, until the amount is 0
can this be done?
the second page in the spreadsheet has a post with monthly pays to the bank
hope some one can help
Ole
Last edited by o-madsen; 11-30-2009 at 12:40 PM. Reason: wrong title
=Orig_Value-Sum(Sheet2!A:A)
where Orig_Value is cell containing your original 15642 amount.
And Sheet2, column A contains your monthly payments.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
thank you very muchOriginally Posted by NBVC
one thing though, I would like to make the line substract the next month every month, is that possible? so each month the spreadsheet will automatically update itself?
Ole
the math line should be fortgoing of it self so to speak, so the frontpage will show the amount left right now
hope this helps
Ole
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
2010.xlsx
I have translated a few of the cells to english
Try in C3:
=SUMPRODUCT(--(('Budget i banken'!$B$1:$M$1&" 1, 2010")+0)<=DATE(2010,MONTH(TODAY()),1),'Budget i banken'!$B$29:$M$29)
or
=SUMPRODUCT(--(("1 "&'Budget i banken'!$B$1:$M$1&", 2010")+0)<=DATE(2010,MONTH(TODAY()),1),'Budget i banken'!$B$29:$M$29)
and in C4:
=SUMPRODUCT(--((Indtægter!$C$1:$N$1&" 1, 2010")+0)<=DATE(2010,MONTH(TODAY()),1),Indtægter!$C$14:$N$14)
or
=SUMPRODUCT(--(("1 "&Indtægter!$C$1:$N$1&" , 2010")+0)<=DATE(2010,MONTH(TODAY()),1),Indtægter!$C$14:$N$14)
depending on how you store your dates in your system.
You will have to translate the Sumproduct and Date functions to Dutch.
The attached may translate it for you once you open it, but if you get #VALUE error, you might need to fix the Indtægter!$C$1:$N$1&" 1, 2010")+0 to "1 "&Indtægter!$C$1:$N$1&" , 2010")+0 or something similar for it to understand your date format.
I think you misunderstand me, or it might be me who dont explain it good enough
see G2 on the first page, it comes from P9 on the second, but that math is at the end of the month which it should not be
How about:
=C2-IF(YEAR(TODAY())=2010,SUMPRODUCT(--(('Budget i banken'!$B$1:$M$1&" 1, 2010")+0=DATE(2010,MONTH(TODAY()),1)),'Budget i banken'!$B$9:$M$9))
which will start taking affect in 2010.
it returns the message "the formula you typed contains a error".
See attached, G2.
I am truly amazed at how you did that, there is sure alot to learn in excel
thanks for your help
Ole
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks