Good morning folks, I’ll start with some background… I’m currently working on a spreadsheet that will show the total annual payments our fire department will be making each year, for the next 30 years, for budgeting. I have a worksheet that has columns for year purchased (C), annual payment (E), Loan Duration (D), and rows containing the pertinent data for each truck.
A B C D E
1 TRUCK Year purchased Loan duration Annual payment
2 Engine 1 2010 5 $30,000
3 Engine 2 2012 5 $10,000
I have a second worksheet with a table with columns for each truck, and rows for each year, starting in 2010.
A B C D
1 YEAR Engine 1 Engine 2
2 2010 30000
3 2011 30000 10000
4 2012 30000 10000
5 2013 30000 10000
6 2014 30000 10000
7 2015 10000
What I’m looking to do is have the second sheet populated by the data in the first. In my mind a formula like the following would do this, but I cant find anything close.
=fillcells(beginning here, number of cells to fill, direction to fill, value to fill with)
So say engine 1 was purchased in 2010, with 5 annual payments of $30,000, I’d want the second sheet to fill down 5 rows in column C with “$30,000”, starting in cell C2. IE “=fillcells(c2,5,-1,sheet1!$E$2)” If say “-1” equals down.
Doing this would allow me to tweak the loan duration and payment on sheet 1 and see how it changed our budgeting in sheet 2, without having to manually edit the number of cells the annual payment was being filled into.
Any help you can give me would be great, I think I’m getting in over my head!!!
A B C D E
1 TRUCK Year purchased Loan duration Annual payment
2 Engine 1 2010 5 $30,000
3 Engine 2 2012 5 $10,000
I have a second worksheet with a table with columns for each truck, and rows for each year, starting in 2010.
A B C D
1 YEAR Engine 1 Engine 2
2 2010 30000
3 2011 30000 10000
4 2012 30000 10000
5 2013 30000 10000
6 2014 30000 10000
7 2015 10000
What I’m looking to do is have the second sheet populated by the data in the first. In my mind a formula like the following would do this, but I cant find anything close.
=fillcells(beginning here, number of cells to fill, direction to fill, value to fill with)
So say engine 1 was purchased in 2010, with 5 annual payments of $30,000, I’d want the second sheet to fill down 5 rows in column C with “$30,000”, starting in cell C2. IE “=fillcells(c2,5,-1,sheet1!$E$2)” If say “-1” equals down.
Doing this would allow me to tweak the loan duration and payment on sheet 1 and see how it changed our budgeting in sheet 2, without having to manually edit the number of cells the annual payment was being filled into.
Any help you can give me would be great, I think I’m getting in over my head!!!