Excel Help

[wtp]

I am very green at excel but am learning fast. I work for a car dealership that is starting abuy here pay here option.

I have made a loan calculator that shows weekly, bi-weekly and monthly payments. Works great. 2nd work sheet

gets it's info from the first page which also works well. 3rd page is where I would like to have the printable payment

coupons. I have made 1 coupon on page 3 that gets it's info from the other pages and that works well but can't seem

to copy it to the 2nd coupon with out putting in the formulas again. which I could do but if you pay weekly thats like

52 payment coupons just for 1 year little own a 3 year loan that I would have to enter for every coupon. Here is my question.

Is there any way to make 1 and copy it 52 times and have it have the correct formulas but in return say coupon 1, 2, 3 and so on.

Want it to look like a payment book from the bank that has like jan 18 2012 on the coupon and feb 18th 2012 on 2nd coupon

and so on. Don't know if I'm making any sense. Like I said i'm very green. Watched alot of youtube videos and have to say excel

is amazing and alot of fun. Could really use some help. Tried finding a template but no luck. Thanks for any help you might have.

[Kyhl]

Generally, Excel will automatically index any formulas by any rows that they are copied. Not easy to explain but I think that is what is happening to your formulas.

Example.

You put a formula in cell D8 that is =B7+C7.

If you copy that forumla to D9 the new formula will automatically be =B8+C8. Excell will automatically change your formula. Might seem silly but it is a big time saver when you are working with tables.

The simple way around this is to "anchor" your formulas by adding a "$".

Change =B7+C7 TO =$B$7+$C$7 and copy it down.

You will see that the formula no longer changes.

Unfortunately, that does not fix your problem. The fix to your problem is more complicated.

Study the VLOOKUP() function. You may need to create a key in your table that is unique to each record then use a VLOOKUP() to return the value you are looking for.

Another option may be a SUMIF(). That will also require a key in your table.

One of those two should work for you. The difference between them is VLOOKUP will return only one item that matches the key. SUMIF will sum all items that match the key.

[upnorth]

The absolute reference in the cells will work. The date? That is a little more difficult. You will need to put they date in a cell and refer to it in your sheet. Here is a formula I found =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) You then format the cell to show "Jan 18 2012" or something like that.

Are you putting balances on the payment stub.

[wtp]

is there anyway to upload a excel file so You can kinda see what I'm talking about?

[LMITOUT]

If you have a Google email account you can upload it to Google Drive, then make it public and paste the link here.

If you don't have that option you could email it to me and I'll post it.

[wtp]

https://docs.google.com/file/d/0B_kivKyzYG7vNGl0VWtldkdfRUE/edit

[LMITOUT]

It is locked. You'll have to go into the settings for that document and change it to allow access.

After you click on the document in Google Drive there should be a blue icon in the upper right hand corner. Click on that and set the permissions to "anyone with the link". Then copy that link here.

[wtp]

Sorry, new to google drive. Try this https://docs.google.com/spreadsheet/ccc?key=0AvkivKyzYG7vdEN5N3pqdE9yYVVjRkYwUVIxMlhqaHc

[LMITOUT]

No problem. Got it to open.

[wtp]

I am getting it by using the fill feature but have to edit each one. Got 52 done for the weekly sheet so far. I'm thinking 3 yrs worth for the weekly, biweekly and monthly.

[Kyhl]

I made a few changes by adding two tables.

The first table is an index (counter) and month. I used that to VLOOKUP the month that will appear in the due date. Changing the payment number in A2 will change the payment month for that coupon.

You can set the days and months to be whatever you want.

Then I added a named range in G16:G21. Well, actually multiple names for First, Middle, and Last names then a name for each part of the address and phone number.

You can reference any named range within your sheet to anywhere else in the sheet by entering "='Name'"

So to put the Last name anywhere in the sheet just type in the formula '=Last' (without the ticks).

The names can be seen in the formulas in C3:C8, and agin in the same section of the next coupon.

You can copy and paste each coupon and the names and dates will go with.

[wtp]

will check it out, Thanks a bunch