[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Excel formula question

Posted on 2011-09-14
8
Medium Priority
?
249 Views
Last Modified: 2012-05-12
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
Comment
Question by:binaryman101
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36538687
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
 

Author Comment

by:binaryman101
ID: 36538820
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36538956
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:binaryman101
ID: 36539066
very cool, thanks...is there any way to import the "Day" column over also?   Your help is very much appreciated.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36539136
Yes, that column can be included too - just copy the existing formulas in column B into that column too.......
0
 

Author Comment

by:binaryman101
ID: 36546150
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 36546281
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
 

Author Closing Comment

by:binaryman101
ID: 36549179
mr houdini, you should totally be #1 on the hall of fame!
thanks,
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question