• 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.

looking for a fill formula....

JoshVV

New Member
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!!!
 
given your example: if the initial data are on Sheet1!A1:D3:


Truck year purchased load duration annual payment

Engine 1 2010 5 30,000

Engine 2 2012 5 10,000


on sheet2, use this in A1:C

YEAR Engine 1 Engine 2

2010

2011

2012

2013

2014

2015

...


Then paste the following array formula in B2.


Code:
=SUMPRODUCT(IF($A2>=(Sheet1!$A$2:Sheet1!$A$4=Sheet2!B$1)*(Sheet1!$B$2:Sheet1!$B$4),1,0), IF($A2<=Sheet1!$C$2:Sheet1!$C$4 + (Sheet1!$A$2:Sheet1!$A$4=Sheet2!B$1)*(Sheet1!$B$2:Sheet1!$B$4),1,0), (Sheet1!$D$2:Sheet1!$D$4))


Use CTRL+SHIFT+ENTER to enter the formula

Copy B2 to the other cells in your data area.


** wasn't certain about your loan period criteria (e.g. if Engine 1 is purchased in 2010 and period is 5, should payments stop in 2014 or 2015?)  If the lesser number, change the formula where it reads [code]... IF($A2<=... to IF($A2< ...
That will stop at 2014 rather than 2015. Also, change the upper range for all of the $4[/code] values to however far down your rows of items go on sheet1**
 
Or try this in B2:


=IF(AND(VLOOKUP(B$1,Sheet1!$B:$C,2,0)<=$A2),VLOOKUP(B$1,Sheet1!$B:$C,2,0)+VLOOKUP(B$1,Sheet1!$B:$D,3,0)>$A2),VLOOKUP(B$1,Sheet1!$B:$E,4,0),0)


and copy as needed.
 
my partner in crime has since moved things around, Is there a way to attach the actual project for you all to see?


Thanks for all your help so far!
 
Hi JoshVV,


I'm pretty new here but from the few forum posts I've seen, I don't think there is a way to post a workbook. Can you describe how things are different? Is it just a matter of values being rearranged?
 
Joshvv

Try some of the Free File sharing sites, Rapidshare, Megashare, etc
 
here is the link to the shared file, http://rapidshare.com/files/341882831/equip._list.xls.html


the two tables have been merged onto the same sheet, and re-arranged some.


you folks are a great help!
 
1. make sure your 'replacement types' match your headers on the bottom half (e.g. 'Type 1 engine w/ rescue'... as well as all the others).


2. enter the following formula into B23


Code:
=IF(AND($A32 >= INDEX($F$1:$F$17,MATCH(D$21,$G$1:$G$17,0),1), $A32 < INDEX($F$1:$F$17,MATCH(D$21,$G$1:$G$17,0),1) + VLOOKUP(D$21,$G$3:$K$17,5,0)),VLOOKUP(D$21,$G$3:$M$17,7,0),0)


3. copy and paste *special* (formulas) as needed to the other cells in the bottom half


*** note: the formula allows for rows up to 17 in the top portion... you can increase that (change all 17's to whatever) if you expand the list and need more rows up top ***


*** note2: you'll also need a formula to tally up all of the "down payments" for each year (you had down payment for utility trailer in the payment section at the bottom.  I presume you'll want those rolled-up per year in column L

In L23, type this formula =SUMIF($F$3:$F$17,$A23,$I$3:$I$17)
and paste *special* (formulas) down***
 
crap, must have grabbed the wrong cell. ***change all the $A32 to $A23 and change all the MATCH(D$... and VLOOKUP(D$.... to MATCH(B$... and VLOOKUP(B$....


how about this... here is the CORRECT formula for B23


Code:
=IF(AND($A23 >= INDEX($F$1:$F$17,MATCH(B$21,$G$1:$G$17,0),1), $A23 < INDEX($F$1:$F$17,MATCH(B$21,$G$1:$G$17,0),1) + VLOOKUP(B$21,$G$3:$K$17,5,0)),VLOOKUP(B$21,$G$3:$M$17,7,0),0)
 
Back
Top