• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Excel formula question

I have an amortization table in the first tab. I need to pull information from that schedule into the second tab for a given set of dates. I would like to put a start date and end date field in the second tab, and the information automatically populates.

The start date input field in the second tab is B16, and the end date is B17.
The amortization schedule information needs to be populated from A23 to H23 and down until the end date is reached.

The cells from the first tab that need brought over are A16 to H16. Some of those cells have formulas in them, and they need to be brought to the second tab without causing errors.
Can anybody help give me a formula to work for this please?
Thanks,
0
binaryman101
Asked:
binaryman101
  • 4
  • 4
1 Solution
 
barry houdiniCommented:
In which column is the date? Assuming it's A and that original sheet is called data try this formula in A23 of 2nd sheet, copied across and down as far as required

=IF(AND(Data!$A16>=$B$16,Data!$A16<=$B$17),Data!A16,"")

regards, barry
0
 
binaryman101Author Commented:
that didn't quite work...it was close, but when I attempted to drag it down, the formula only worked on 2 lines.  I am attaching the file so you can see what I am trying to do.  Thanks.  All of the information from "Day" to "Total Interest" needs to be populated in the Report Tab.
Copy-of-Amortization-on-a-Simple.xls
0
 
barry houdiniCommented:
You have the start date as 15/09/2011 and end date as 16/09/2011 so you only got 2 lines because there are only 2 rows that are in that date range - if you change the date range you should see the other rows.......

Note you'll have to format the columns appropriately for date etc.

regards, barry

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
binaryman101Author Commented:
very cool, thanks...is there any way to import the "Day" column over also?   Your help is very much appreciated.
0
 
barry houdiniCommented:
Yes, that column can be included too - just copy the existing formulas in column B into that column too.......
0
 
binaryman101Author Commented:
everything you have given has worked, and i greatly appreciate it.  But i have another thing I would like to do with this.  I will only be pulling approx 30 days each time i do this report, and I would like the cells to only populate from cells 23-60 so that way i can make a simple print out.  The way it populates now, i can't do that.  for example if i want info for the year 2020 the data populates in cell A 30,000.  Can this be done? Thanks
0
 
barry houdiniCommented:
Ok no problem - see the attached

I added a "match" formula in B18, it makes the other formulas simpler - so that one is

=MATCH(B16,Data!B:B,0)

That finds the row with the date from B16, you can hide that if you want (or move it)

Now the formula in A23 is

=IF(ROWS(A$23:A23)>$B$17-$B$16+1,"",INDEX(Data!A:A,$B$18+ROWS(A$23:A23)-1))

copied down to H60

regards, barry
27308482.xls
0
 
binaryman101Author Commented:
mr houdini, you should totally be #1 on the hall of fame!
thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now