This is a new payment status sheet that my company has put together to use with future clients. There are a few things that we would like fixed but can not figure out ourselves. I have attatched the document along with some notes as to what we can not get to work.
View 14 RepliesI am trying to set up payment record sheet as follows
First Tab: This is the total value of each item, with the payment date in cell C1
Second Tab: Is the payment date of the item
Third Tab: Is the payment due tab
So what I want to do is enter the payment in C1 on the first tab. Then on the second tab I want to enter all the items that want paying on that day, but this will also have previous payment dates shown as a sort of record of what was paid on what day.
On the third tab I want it to show all the payments that correspond to the same date as C1 DATEVALUE
And on the gross tab I want to show all payments to date including the current date.
I have got the payment date to work but I cant get it to show all the previous values.
I have a very large spreadsheet where i have orders, product and line status listed. I neeed to find the line staus of each product in production against the order. I'm using an array sumif formula but since the spreadsheet reachs 10000 rows of information I dosn't alway calculate correctly.
ORDERPRODUCTSTATUS
ABCDAPPLEPICKED
ABCDORANGEON HOLD
ABCDPEACHSHORT
ABCDYAMSSTAGED
EFGHAPPLEPICKED
EFGHORANGEON HOLD
EFGHPEACHSHORT
EFGHYAMSSTAGED
Required Result
Order
ABCDAPPLEORANGEPEACHYAMS
PICKEDON HOLDSHORTSTAGED
I would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month:
Select Creditor:
Enter Payment:
New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
Not sure if this is the correct section for this kind of query but I'd like some assistance with a calculation that I can't seem to figure out.
Essentially it's for calculating night payments for our employee time-sheets. Our staff have very sporadic shifts and are paid extra for working between the hours of 00:00 and 06:00, basically when employee's enter their start and end times I'd like the spreadsheet to automatically calculate how many hours they have worked between those hours, I imagine it's very simple but I cannot figure out which function to use.
To complicate matters, because staff can work shifts which start on one day and finish the next we work on a 48:00 clock basis so its' not only between the hours of 00:00 - 06:00 where they qualify for night payments but also from 24:00 - 30:00 if that makes sense?
I'm currently developing a model for a payment schedule. For example, lets say a customer purchases an item from a store, but the store receives that payment in the following month. I'm looking to develop a dynamic model so that if I change the assumption from 1 month to another term (2,3,4+ months etc.) the model will adjust accordingly.
I've started with a formula
=IF(MONTH(H3)-MONTH(G3)=B14,G11*B13)
But of course this will not work once the term is set to larger than 1 month. With B14 representing 1 month, and G11*B13 being payment information (price * quantity).
I have a list of several hundred loan recipients who are all starting to pay their loans back on different dates. I am able to get the calculation to work so that I just need to put in the start date and excel works out the other 5 or 6 payment dates.The problem is, some people pay back on the 29th, 30th of the month which is giving me a headache when they are supposed to pay in February. The formula I am using calculates the next date of payment to be on the 3rd March (skipping the February payment all together).
The formula I am using is : =DATE(YEAR(A10),MONTH(A10)+1,DAY(A10))
I have a situation where I need to highlight different customers based on what there terms of payment are. Conditions 1 and 3 seem to be working fine but for some reason condition 2 is not working at all. I made sure all names are exactly as listed in the A2 cell....
View 9 Replies View RelatedI have been raken by brain for a simple formula to calculate an Interest Only Payment on a mortage note, example,
sales price = $162000
term = 360
interest = 8.5
I was able to calculate the payment of principle and interest, but I need to validate what would be an Interest Only payment on this?
I used erecord the other day to do my activity statement for the first time (I have just started a home based business which has not yet started trading but I had to send the BAS for the purchases that I had made for the business) and it was quite easy to use and you can send it electronically to the ATO which saves a lot of hassle particularly as I am very not accountancy litterate. However I am trying to develop an expenses/payment spreadsheet similar in function to erecord but that allows me to categorise the inputs.
My headers are:
Date
Cbookref = (drop down validation box) similar to a chart of account #
Category = ie - advertising accounting fees etc.. uses a look up function with cbook ref to populate field
Description .........................
I'm working on a report where i need to find the last date a payment was made by a certain client. The problem is that not all clients have the same amount of payments so I cant just choose a payment number in my formula.
Trishlast payment.xlsx
i have myself a table on the left is all the members names and along the top are 12 dates (1/1/07, 1/2/07 etc).
In another sheet i have a drop down box for the name of the person and a dropdown box to select a date and then a space to enter the amount paid so i need a macro that will find the name and date i selected from one sheet and take the amount paid into the other sheet and insert the amount in the correct place.
If it is unclear what i mean i will post screenshots tomorrow.
How can i get the actual Payment due date from no. of days using macro. i. e.
Posting Date Dues Payment Date
------------ ------ ---------------
22.06.2013 21 days ???
I have a file which student Monthly Loan Payment schedule.
I want to make a list which shows all payment period by installments and payment amount , dates by Student names.
Attached file, you can see all detail and wanted LIST.
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?
I have a capital lease amortization schedule with annual increases to monthly rent that I am trying to solve for an interest rate such that the balance nets to zero at the end of the term. I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month. The present value is known, payment term is known, future value is zero, and the payment amounts increase annually. These assumptions may change for new leases so ideally the solution would be dynamic, adjusting for shorter/longer terms, etc. Here is an example of my assumptions:
Payment start date: 8/1/13
Term end date: 10/31/2025
Rent length in months: 144
1st months rent: $500,000 - payments are due at the beginning of the month and are paid monthly
Annual rent escalation: 3% - i.e. 1st 12 months is $500k/month, 2nd 12 months at $515K, etc.
Beginning NPV: $75M
Ending value: $0
Imputed annual interest rate: UNKNOWN
I'm not sure if this is relevant, but the monthly payment is allocated between principal and interest. Monthly interest expense is calculated as the current balance * (imputed interest rate / 12).
Currently, I've plugged the interest rate such that my ending balance is 0, however I was hoping to calculate it on the fly as opposed to manually plugging it.
how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?
I'm building a real estate payment calculator. I'm using the Payment (=PMT) function in Excel 2003. When I add the information that it asks for in its wizard, I receive the correct numerical figures but with a minus sign in front of it making the result negative, i.e. "-$630.13" instead of "$630.13."
Is this file corrupted or have I put something in incorrectly?
i am trying to get the number of days from last time ticket paid till todate, considering the day & month of the hiring date but the year of the last time paid. say AAA hired on 15-Nov-2001 receives a ticket every 24 months; received last ticket in Nov 2011.
i want to count the number of days between 15-Nov 2011 until 31-Jul-2013 (dd & mm are from joining date yyyy from last time paid) i have a list of 1200 names with different dates of joining and different dates of payments.
I have 2 production unit : A; B
They make payments to suppliers on certain periods : p1, p2..
The payments are on variable and different currencies : EUR, SEK, USD, CHF,AUD..
I explained the sample case table in attached excel sheet. My question is "how can i create the summary table easily and quickly by excel?"
currently I am calculating it by a simple excel formula (vlookup) that I need to change the range for each time. It s very time consuming and easy to make mistakes..
I have a list of employees who's job status may change at the end of a given month. If the status changes, they have a supplemental payment that is tied to their job status and may change as well. But that depends upon certain conditions like their years of service and their union.
I'm trying to write a formula that will update that supplemental payment based upon certain conditions, but I can't quite seem to get it right. A challenge is the table I'm looking values up in has 8 columns and the value could be in any of the columns depending on years of service.
These are the conditions for the lookup:
1. If their union local is the same, they would keep the supplemental payment so long as it is not more than allowed for the title. If it is more, they get the supplemental that corresponds to the new title and the years of service.
2. If the new title is PAA and they have 10 or more years of service, the new supplmental is $600.00
3. If the new title is Clerical Associate II and they have a 6 or more years of service, they get a supplemental that corresponds to their years of service.
Salesman
Sales Amount
Introduced by
sales
introduced
sales
indirectly introduced
sales
Tom
300
none
Tom
300
[Code] ........
I have salespeople who I want to pay a bonus to but I've also offered them a bonus on how much the salesmen they referred to the company sell and this goes like a pyramid down 3 levels.
so I have a list of salesmen next to there name is how much they have sold then who introduced them. from this I need to make a list of each salesman and below all the people he introduced and the people they introduced with the sales in the cell next to them.
To show how it could look I've done an example above of the information I need for tom, as you can see all the data is in the first 3 columns and tom needs to show that he introduced bob & Sue, but because Bob & Sue also introduced someone, tom needs to show he was involved in this as well and jo, terry, mark need to show, preferably in a different column as the bonus for them is lower.
When a date is entered e.g 1/27/2007 The bill payment dates should all be automatically calculated under the “Date” column. Rules for the date
Rule1. e.g. 1 Month—the date should be 1 month after the date entered (as shown by the simple example 1/27/2007 one month is 2/27/2007 in the sheet)
Rule2 if the date entered is the last day of the month, the calculated date should also be the last day of the month provided it is a working day.
E.g 1/31/2007 one month is 2/28/2007. (provided 2/28 is working day)
2/28/2007 one month is 4/30/2007 (provided 4/30 is working day)
Rule3 If the calculated date based on rule1 and rule2 is a non-working day, the date is pushed back by 1 till it becomes a working day. e.g 1/27/2007 two month is 3/27/2007 if 3/27/2007 is Saturday it becomes 3/28/2007 which is a Sunday, another non-working day. So the output should be 3/29/2007
Rule4, under rule3, the date cannot be pushed to the next month. Instead, we push the date forward. In the previous example assume 3/29, 3/30 and 3/31 are all public holidays, by rule3, it will be finally pushed to 4/1. But this becomes another month. So we push forward instead to 3/26 which is Friday. The output is 3/26/2007
Definition of working dates (Monday to Friday excluding the holidays listed in the sheet).........
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
I have some experience with excel, but until now have not ventured into VBA and macros.
I have a workbook which will have the following sheets:
1.Absence Summary sheet - Summarises data from each employee's individual sheet.
2. Template Sheet - A sheet formatted as an absence record sheet, but without data.
3. Individual employee Absence record sheets - Based on the Template sheet.
I have read with interest the various posts and help files on User Forms & Macros, but have got a bit stuck.
My Aim: ....
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.
also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February?
Also rename the new sheet (February) cell B3 the same as new sheet's name (February)
So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.
Is there a way I can compare sheet 1 Column A, with sheet 2 Column A, and then move any duplicates from sheet 2 over to sheet 3.
For instance,
Employee Numbers that match in both sheet 1 and 2, remove that row from sheet 2 to and insert it into sheet 3.
Example, I have a sheet named DATA1, I want to add a new worksheet, copy a certain range from the DATA1 sheet to the new sheet and rename it the same name DATA1 and delete the old DATA1 sheet.
Also, the sheetname will be unknown, the macro must get the sheet name first.
This work is challenging for me, now am doing Comparing the Data between One Sheet to Another Sheet, and Paste into Different Sheet.
I am Manually Comparing the Data between two different sheet, If the Data is matching, or Not matching, those data i have document in other sheet, So i have lots of records available. so manually doing its very difficult, but macro can compare the data. I will give the details of my process and example sheet is attached here....
Data 1.xls == This sheet have multiple tab with data and different order no.(Multiple Tab with thousands of records available)
orderNumberModified.xls == Modified data available. (Compare the OrderNumber to Data1.xls)
Now we have to compare the modified.xls and Data1.xls... For modified.xls have ORDERNUMBER available, that Numbers match with all tabs from Data 1.xls sheet.
If ORDERNUMBERS are matching, Need to copy the entire row from modified.xls To AVAILABLE TAB from Comparedata.xls sheet
If ORDERNUMBERS is not matching, copy the entire row from modified.xls To NOTAVAILABLE DATA tab from comparedata.xls sheet
I have workbook having around 25 sheets and i want macro to merge all sheets except first (summary) in to last sheet ( mergedata ) inpute data will be start from cell A17 in all sheet. also sheets may be add or delete as per requirement. after merge data from sheet name should add after each entry .
attached sample file for your reference.
[URL]