Forum Discussion
Repetitive Formulas
Is there a way to copy formulas accumulating totals without retyping each formula. For example we need to total vendor by vendor month by month spending and accumulate those totals in a spreadsheet that totals the annual amounts by vendor. The formula we are using works but must be retyped for each month when the only changes necessary is the name of the month.
11 Replies
As discussed offline, here's the attached workbook with the solution in it. I'm glad we worked it out.
=SUMPRODUCT(--(INDIRECT("'"&$E$4&"'!B4:B60")=$B4),INDIRECT("'"&$E$4&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$5&"'!B4:B60")=$B4),INDIRECT("'"&$E$5&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$6&"'!B4:B60")=$B4),INDIRECT("'"&$E$6&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$7&"'!B4:B60")=$B4),INDIRECT("'"&$E$7&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$8&"'!B4:B60")=$B4),INDIRECT("'"&$E$8&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$9&"'!B4:B60")=$B4),INDIRECT("'"&$E$9&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$10&"'!B4:B60")=$B4),INDIRECT("'"&$E$10&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$11&"'!B4:B60")=$B4),INDIRECT("'"&$E$11&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$12&"'!B4:B60")=$B4),INDIRECT("'"&$E$12&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$13&"'!B4:B60")=$B4),INDIRECT("'"&$E$13&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$14&"'!B4:B60")=$B4),INDIRECT("'"&$E$14&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$15&"'!B4:B60")=$B4),INDIRECT("'"&$E$15&"'!D4:D60"))
For formula lets us use a business name from the summary sheet (e.g. cell B4 is All seasons true value) and searches for a reference in the monthly sheets. When it finds it, it adds the running total so the sheet can adapt to when you make a transaction with that business name.
I'm sure this formula can be much improved by the experts here but in terms of working for you, glad that you have confirmed that it does!
Wishing you all the best.
Cheers
Damien
Hi, to all!
Check this formula applied in workbook:
C4 : =SUMPRODUCT(SUMIF(INDIRECT("'"&$E$4:$E$15&" "&RIGHT($D$1,2)&"'!B4:B42"),$B4,INDIRECT("'"&$E$4:$E$15&" "&RIGHT($D$1,2)&"'!D4:D42")))
D4 : =SUMPRODUCT(SUMIF(INDIRECT("'"&$E$4:$E$15&" "&RIGHT($D$1,2)&"'!B4:B42"),$B4,INDIRECT("'"&$E$4:$E$15&" "&RIGHT($D$1,2)&"'!E4:E42")))
Check file. Blessings!
- Thanks John! That is certainly less repetitive than my own attempt!
- Attached is the spreadsheet. The last time I hit reply it did not give me an option to Browse. I hope when I hit reply this time it gives me that option.
Hi Michael
To assist, I am uploading the file that you have emailed to me so that others can see it.
As discussed, having looked at the spreadsheet and your requirements, I'm not sure exactly what it is that you are after.
Are you looking to total each of the line items in the Annual worksheet against each calendar month of the year?
I would also suggest not including anymore than 1 calendar year in the spreadsheet, then you can template the spreadsheet and easily adjust for future years.
Talk soon.
Cheers
Damien
- Sorry, when I hit replay it sends the message before I can attach the spreadsheet. It doesn't have a brose option just "cancel" or "reply".
Hi Michael
When you hit the Reply button, it should take you to the page shown below. Click onto Browse > Choose files > Open and click Done. before hitting post.
Hope that helps?
Cheers
Damien
- Here is the spreadsheet.
- Yes I Do. How do I send it to you?
Click onto the Reply button and there is a an option to upload the file. Just click onto Browse to begin and ensure that the file is not open when you upload it.
We can have a look and maybe help with the query.
Cheers
Damien
- Hi Michael
Do you have a sample file you can upload for us to see?
Cheers
Damien
