# Excel 2003 :: Pulling Data From Amortization Schedule To Debt List

Feb 25, 2013

I am using Excel 2003 and I created an amortization schedule set up for an debt account. I am trying to pull the "Balance Due" from that schedule into another chart based on the current date (these are on two different sheets in a workbook).

For example, this is my 'Amortization Schedule':

Balance
Due
Interest
Rate
This Month's
Interest
This Month's
Payment

[code]....

And I am trying to pull the "Balance Due" from that schedule to place into this chart on my 'Debts' sheet: (based on the current date)

Name
Starting
Balance
Remaining
Balance
Interest
Rate
Minimum

[code]....

For example, if today were 1/15/13, I would want \$3,796.34 from the schedule to go where the "x" is on the chart above. What formula would I use to accomplish this?

Also, on a side note, would there be a formula to have Excel pull the "Payment Date" from the schedule into the "Payoff Date" in the chart based on where the row has a \$0 Balance Due?

## Debt Reduction By One Time Payment In Amortization?

Mar 14, 2009

How to calculate a One time Payment to maintain the Tenure In Amortization?

Ex:-
I havea loan of Rs 1,00,000/- to be paid @ 8%/12 for the first year and then from 13th month , it will be Floating Rate of Interest which would be mostly higher than 8%/12...

I will be paying this loan off in 5 years ( i.e. 60 months)
and the EMI = Rs 2028/- per month..

Lets say, if the Interest goes up to 12%/12 after the 12th EMI then the tenure increases by 5 months from 60 months to 65 months thus adding to the cost of Rs 2028*5 = Rs 10140 more..

Now What amount do I need to pay alongwith the regular EMI's to maintain the tenure of months?

The amount Im referring to is a One-Time Payment added with the EMI for the 13 the mOnth to reduce the Principal and accordingly the tenure?

## Excel 2003 :: Extracting Sub Sheet Of Data From Larger Schedule

Dec 31, 2013

I have sheet containing a schedule of data. It is a record of changes on a project, each change is sequentially numbered and contains a row of data (date of change, whether approved, cost of change etc). Each change is given a cost centre reference (1 of 10 cost centres are being used) depending on the type of change being made.

I need to keep that record of the overall schedule of changes but I also want to extract each of the cost centre categories into a sub table on the sheet to give a schedule of changes against each category.

I have almost achieved what I want but one method leaves blanks in the sub table which I want to avoid and the other method I used to remove the blank lines but needs the first method sheet calculated before it then goes to work and seems rather cumbersome and I suspect there is a much easier method. (I also want it to be compatible with excel 2003 so it can be used across several platforms.)

## Excel 2003 :: Amortization Table - Years Elapsed From Column Of Dates

May 21, 2012

I am working with an amortization table and need to work out the elapsed time to amortize the loan.

Currently the worksheet is working fine and calculates the end date of the loan but the length of the column varies depending on the scenario.

I need a formula to calculate the time that elapses between the first date and last date.

The data starts in cell b13 and the last date could appear in pretty much any cell below that, so the formula will need to look for the last valid entry.

I am using excel 2003.

## Amortization Schedule For Multiple Loans?

Nov 18, 2012

I need to create an amortization table for several thousand loans. I could create a seperate amortization schedule for each loan, but as you can imagine, doing so would take a long time.

These loans have varying interest rates, loan periods, and beginning balances, so it is not as if I could just add the beginning balances together and amortize on that basis.

It seems like this will require a VBA macro, however my programming skills are a little rusty.

## Amortization Schedule: Auto Update Based On Loan Period & Number Of Payments Per Year

Apr 30, 2009

I have uploaded a sample amortization schedule.

1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.

2. Also, if a value is entered in column E, then i require the whole table to update as well.

## Pulling Data From A List

Mar 16, 2009

I am trying to create a spreadsheet which will help us analysis the sale of each of the inventory items.

Col A: Item #
Col B: Description of Item
Col C: Standard COGS
Col D: Price
Col E: Profit %

On Sheet 1(Standard), I listed all 205 items with columns b-e also being filled in.

On Sheet #2(Actual Sold), I want to just enter the Item # and have all the other info on Col B - Col E fillied in automatically.

## Using Excel Data To Create A Game Schedule

Jul 11, 2013

I am trying to use Excel to generate a game schedule for my fantasy football league. Right now I handle this task manually but I figured there has to be a way for Excel to work it out.

Let's say I have 16 teams playing 16 games. I have a table written out using numbers to represent the different teams. The x-axis has one team number per column while the y-axis has a week number in each row. The intersection of any column with each row shows the x-axis team's opponent for the week. What I want to do is substitute a team name or abbreviation for the team numbers in the body of the table and then generate a formula (or script? - I'm not sure of the correct terminology) to then produce a listing of the games each week. So the output would look something like this:

Week 1
BNS v ROC
NYL v NFB
WBB v TCF
etc...

Each 3 letter abbreviation represents a team name. This would significantly reduce the amount of manual work I have to do since Excel would generate the game schedule as above for each week and then I take that information and dump it into the software I use to run the League.

## Pulling Unique Data From A List

Apr 16, 2013

I have a generated list that pulls data from a different sheet. I need a formula to pull only unique values from that generated list since some values are list multiple times.

[URL] ......

## Pulling Unique Data From A List

Apr 15, 2013

I have a generated list that pulls data from a different sheet. I need a formula to pull only unique values from that generated list since some values are list multiple times.

## Pulling Data From List If Cell Blank Then Move To Next

Apr 16, 2009

Trying to pull data from a list and if the cell above is empty then pull the data from the cell below and so on........

I have some code for it..but its just a little bit too long and I can't use it

This is what i have so far, trying to simplify it as its way to long to fit in a singel cell....i did think about breaking it down in to collums of either 8 or 6 and having it in chunks that way...but if i did that then i would still have to eventually use this forumla below as i don't really want huge gaps within the form i'm doing.

## Macro For Spotting Text From One List To Another Then Pulling In Data With Vlookup?

Jun 3, 2014

I have a list of active accounts with its relevant data on one sheet, then I have a list of closed/inactive accounts that will be updated periodically by different users. I would like to run a query for the accounts on the closed list to pull in relevant data from the active list,if any, or maybe just highlight the closed accounts on the active list to ensure that the closed accounts aren't active again. Is that possible with a macro? Should it just be a vlookup? The accounts list is going to be a very big file and I am trying to find a solution that won't take up too much time to run.

## Excel 2008 :: Pulling Data From On Sheet To Another

Apr 3, 2013

Expand the explanation for the previous thread? [URL]....

I am trying to do basically the same thing, I have a long list of clients and need to separate them by date they contacted us. I am using Excel 2008 on a mac, which does not allow micros. the previous thread does exactly what i need to do, but i cant get it to work.

## Pulling Data From Multiple Excel Files?

Jun 3, 2013

I am trying to use the following formula to pull out data from multiple excel files in a folder called "Certificate".

='D:SSR Sec and Techcertificate[STUDENT 2.xlsx]Student Record'!\$B\$10

='D:SSR Sec and TechcertificatePath
[STUDENT 2.xlsx] File Name
Student Record'!\$B\$10 Sheet name and Cell reference

The formula works without any problem. I want to replicate the formula to extract the same data in multiple excel files. In this case only the second part of the formula needs to change to "STUDENT 3", "STUDENT 4", "STUDENT 5" and so on. I have created a column in excel with those values. I am trying to use the cell contents in the above "formula", but I cannot seem to replace this value in the formula. Needless to mention that I tried to drag the formula, but it does not work. I am attaching the two excel files.

## Excel 2003 :: How To Use List Or OR Condition In SUMPRODUCT

Oct 30, 2009

How to include a boolean OR in my SUMPRODUCT formulas.

Software: WinXP SP2 and Excel 2003 SP3

I'll try to type in some data in legible format:

......Col B.....Col C.....Col D.....Col E.......Col F
1...NW OH....WMA.....110%....NW OH...(result)
2...NW OH....SIS.......130%
3...SO FL......PRB........92%
4...NW OH....TRO......104%

I want to sum the contents of Column D when Column B = a given text value located in Cell E1 AND Column C = text value #1 OR text value #2 OR text value #3, and plunk the result in Cell F1.

So for example if I have NW OH in Cell E1 and my values to match in Col C are WMA, SIS, or PRB, I need a result of 240%.

I am using dynamic ranges for the contents of Columns B, C, and D: CALC_AFFIL, CALC_TITLE, and CALC_PERC respectively.

I have tried:
=SUMPRODUCT(--(CALC_AFFIL=\$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),CALC_PERC)
=SUMPRODUCT(--(CALC_AFFIL=\$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),--CALC_PERC)

Also tried both the above formula w/o the "--" in front of any array.
=SUMPRODUCT(--(CALC_AFFIL=\$E1),--(CALC_TITLE={"WMA","PRB","SIS"}),CALC_PERC)

Again, the above was tried with and w/o the combinations of "--" in front of arrays.
=SUMPRODUCT(--(CALC_AFFIL=\$E1),--ISNUMBER(MATCH(CALC_TITLE={"WMA","PRB","SIS"},0)),CALC_PERC)

Once more with and w/o "--" combinations.

## Excel 2010 :: Pulling Data From Non-blanks On Sheet

Aug 21, 2012

I have created a comparison sheet and compares my old one from last week to the new one I create for this week.
It populates cells with the differences and shows me the data from the new sheet. But since my they are so massive A5:AZ20,000 so you can imagine how many blanks there are.I want to create an auto summary or something from the non blanks.

Column A has each properties Unique Identifier And the rest is various info for the property. I would like it to return my unique ID and then the columns with new data that has showed up from the comparison some are text and some are numbers.

Also I am using Excel 2010.

## Excel 2003 :: Copy List Without Blank Rows?

Mar 20, 2014

I have a master list of Players on one sheet B2:B72 and in E2:E72 is a column called Playing and in it is "y" or "n". I need to make a list of the Players that are Playing on another sheet without blank rows (I can do it but it leaves blank rows for the Players that have a "n" from column B. I am using 2003

Here is the formula I am using now: =IF(Players!\$E3="y",Players!\$B3, " ") but I get blank rows for the player that are not playing.

## Excel 2003 :: List Duplicates In Separate Column

Jun 14, 2013

Running Excel 2003. I have a list in column B, of numerical codes. What I want to do is find the duplicates, and list the duplicates in column D. Is this possible?

## Excel 2003 :: Ignore Blanks When Sorting A List?

Aug 24, 2012

I am using Excel 2003.

I have a pivot table in sheet1 and references in sheet2 like

Code:
='Sheet1'!A1
and so on to copy the whole thing to make it the source data for a bubble chart.

Now, I want to convert the table in sheet2 into a list via Ctrl+L to be able to sort by names with a dropdown menu. Unfortunately, I have to copy all rows from 1 to 1000 to account for possible increases in the pivot table size. This results in blanks in the list and when I want to sort it, I have 990 blanks before the first data rows show up. Not very neat

## Excel 2003 :: Remove Duplicate Names From A List

Nov 12, 2012

Unfortunately we don't have 2010 at work so I don't have the luxury of the use of the duplicate function.

I'm using Excel 2003 and need to remove duplicate names from a list; what would be the best formula to do this.

I've done a countif to identify how many occurrences appear; any other formula if greater than to get to the object of how many staff I have in the list

## Excel 2010 :: Pulling Cell Values Into URL When Using Get External Data From Web?

Feb 20, 2014

I am using Excel 2010. I am trying to make a spreadsheet where I can type in a date (02-19-2014), and pull in box scores from a basketball website. I have everything set up so that the date is parsed into (3) cells (02 19 and 2014) so that it can easily fit into the URL:

[URL]

As you can see, the month value in the URL is "02" the day value is "19" and the year value is "2014". What I want to be able to do is type in any date I want and return the box scores from that day in a new tab. I have everything done so that the new tab is automatically created and named, so my only issue is that I can't figure out how to input the day, month and year values automatically into the code for the Get External Data pull:

Code:
, Destination:=Range("\$A\$1"))

## Excel 2003 :: Copy Only Selected Cells In Filtered List

Jan 19, 2012

I can't seem to find a way to copy specific highlighted/selected cells in a filtered list column and paste them outside of Excel 2003. i.e. in Notepad.

It seems to copy all the data between what is selected.

My Filtered list shows rows 5, 28, 35, 40 and 56

If I selected A5, A28 and A40 and select copy

If I paste it into Notepad, it adds A5, A28, A35 and A40

However if I paste my selected copied cell into Excel it works perfect and only adds A5, A28 and A40.

## Excel 2003 :: Macro That Will Generate A List Based On Cells Value

Mar 6, 2012

I have a workbook that has 30 tabs in it. Each tab is a report card for students. What I'd like to do is create another tab with a button on it that when I hit the button it will search through each tabs range of D12:D40, D48:D76, D84:D112, D120:D136, J12:J40, J48:J76, J84:J112, and J120:J136. And if any of these cells has an MS in them then this new sheet I have created will list each students name which is in cell E5 and list what they recieved the MS for. This will be in the same row number but in column B. So if cell D12 has an MS in it then this report will list the students name and what's in cell B12.

Windows XP
Excel 2003

## Excel 2003 :: Can Index Through Pivot Table Page Field List?

Nov 1, 2011

I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.

I can't figure out if it's possible to index through the list though. Is it possible?

## Excel 2003 :: Limiting Dropdown List Based On User Selection

Jun 27, 2014

I would like to be able to limit a drop down list's contents based on a user's selection in a different drop down. I am using Excel 2003, and I know how to do this in Access, but I can't figure it out in Excel. Basically, I have a list of themes in one drop down. The user picks a theme, and goes to a second drop down which is a list of sub-themes attributable to that one theme and no other theme.

## Excel 2010 :: Pulling Data Based On Multiple Criteria With Duplicate Values

Mar 14, 2012

I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.

I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).

For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?

Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1

Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2

## Excel 2003 :: Expanding List To Use In Mail Merge Program To Produce Tags For Direct Mailing

Sep 25, 2012

I have an Excel 2003 list with four columns as shown below:

Zipcode
CRRT
Count
Bundles

85710
C004
693
14

85710
C005
867
18

85710
C006
1021
21

I want to "expand" this list to use in a Mail Merge program to produce tags for our direct mailing. Each bundle contains 50 letters, so in the first line on the above spreadsheet, there will be 13 bundles of 50 and 1 of 43. Currently, I can cut and paste to create the following table, but there has to be an easier way...

Zipcode
CRRT
Count
bcount
Bundle
ibundle

85710
C004
693
50
1
14

[Code] ........

So as I pull this into a mail merge I will get a tag that shows the zipcode, the crrt, the total pieces for that crrt, then number of the bundle for example " 1 of 14" and the quantity in that bundle, for example "50 of 693", then the next bundle tag will print, 3 to a page. I know that here has to be an easier way either in the mail merging process itself or with EXCEL? Am even open to build these with ACCESS to create the tags. The beginning database contains anywhere from 350 to 800 lines of original data, so as you can see the cut and paste is VERY time consuming.

## Excel 2003 :: Match Two Sets Of Data And Display Specific Data?

May 12, 2012

I have two separate worksheets:

I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)

Referral
A
B
C

[Code]....

way to do the calculation using Excel 2003

## Excel 2003 :: Matching Row Data

Nov 28, 2011

I have five columns of data: A & C are time measures; B & D are values associated with each time measure; and E is a matching value column that I want to populate. I am trying to match times and place in col E the Val 2 that matches times 1 and 2. Though many matches lie on the same row, there are many that don't (the real data uses several thousand rows). For example, Val 1 in cell A5 (100) is associated with cell C7 to find a matching value of 4.5 in cell E5. Note, occasionally, there will be replicate values for Val 2 (like cells C9 & C10). I need to match the one further down the list.

Excel 2003ABCDE1Time 1Val 1Time 2Val 2Match203.902.72.73484.7482.12.14545492.33.351006543.34.562003554.472192.71004.57.782015.592196.6102197.7Sheet1

## Excel 2003 :: Autopopulate Data From 1 Database To Another?

Jul 1, 2014

I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......

I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....

Currently what i am trying to do is to get one of the minor files to auto populate an area of the master database. I will try to explain it below...

1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').

2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..

3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed

4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.

5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)

6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....

To make mattters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red