# Keeping Track Of Payments

Apr 24, 2009

i am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out

so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage

what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received

i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess

have any of you got any ideas of how i could simplify this and make it look neat and easy

or alternatively is there some other software that may suit this application better?

## Keeping Track Of How Many Times A Number Shows Up

Apr 22, 2009

Say, for example, I have a spreadsheet as follows:

0 1 1 2 3 2 0 1 2
1 2 1 2 3 3 1 0 1

Is there a way to count the number of times a specific number shows up and have excel post it to a different cell? I need to know how many times a certain number came up for a specific row, and how many times.

## VBA Functions - Multiple Inputs And Keeping Track Of Them

Sep 17, 2013

I have several formulas that I use over and over, so I'm creating VBA functions that I can call from a worksheet.

But...when I have multiple inputs going into a function, is there a way to remind the user which input comes next in the same way that Excel does with built-in functions?

For example, when you type in =PMT( Excel brings up the helper text so you know that the order of inputs is (rate, nper, pv, etc....

Is there a way to bring up that same helper text in my own function? So if I have up, down, right, left as inputs and give the spreadsheet to someone else, they can see it's up, down, right left and not right, left, up, down .

## Appropriate Template/worksheet Needed For Keeping Track Of Turnover

Jan 5, 2009

I'm trying to find a simple Excel template to add my sales invoices together for a year...just so I know what I've earnt...nothing complicated.

The Columns I need are;

date - invoice no. - customer - labour - materials - total

I'd like it to add together labour and materials to the total box ( or just labour in the total box if no materials for that job.)

Then each invoice adding up as I add more with subtotal of labour and materials then a grand total.

## Linking 3 Sheets To Summary Sheet Keeping Track And Totals Of Categories

Jul 22, 2014

I have a spread sheet for my checking accounts. There are 3 sheets, BW, QW, and Chase CC. In the 1st 2 sheets row D is named "Category" and the 3rd sheet has the same name but is in column C.

How to link the 3 sheets to a summary sheet keeping track and totals of the categories ?

Example in sheet BW I have a category called "Utilities" and the column to the right of that is the amount. Then ect for other categories.

## Keeping Track Of Scores And Automatically Displays Names Of Players In Descending Order?

Feb 7, 2012

I'm fairly new to using excel and have been trying to create a simple spreadsheet that keeps track of scores and automatically displays the names of the players in descending order. Anyway I have gotten to the point of using hookup to match a value and return that players name. But when two players have the same score it will only return the first found value. I sort the number scores using a LARGE function and it works great. Then I use something like this =HLOOKUP(Y2,B27:K28,2,FALSE) to match that value with the players name. How to return the other players name when the scores are the same?

## Keeping Track Of Data Over Time Even If The Data Is No Longer Visible?

May 20, 2013

I work in a medical facility, and I need to track data I've entered representing patient visits indefinitely over time. I've attached a weekly grid to this thread that I use to record which patients have had visits during the course of the week. It sums their total visits on the right. I need to continually track these because every patient gets an insurance-mandated progress update after 10 visits. Unfortunately, they don't always show up, and the most I can possibly keep track of is two weeks, max, and most patients only get two visits per week. Is there a way to dump these sums into another column indefinitely?

## Keeping Track Of Which Macro Is In Which Module Since You Can't Rename The Module?

Mar 27, 2009

After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?

## Management Of Payments

Dec 18, 2008

First, ignore the colored portions. This is how I kept track of what I had completed in the form as I created it. Clients attend class once a week and pay once a week for their classes. I'm taking this one step at a time. At the initial interview of a particular client we will determine the payment per session due, and we will enter this amount in the column labeled "payment per session. We also need to keep track of how many sessions they have paid for. So in short, I'm looking for the following:

When "amount paid" equals "payment per session" set "minimum payment due" to \$0 and increase "classes paid" by +1

I figured out how to enter the payment and return the minimum payment due to "0" but how can I make the "classes paid" increase by 1 every time the minimum payment equals "0"?

## Payments On Second Tuesday Of Every Month

Feb 16, 2008

I am wondering if anyone could possibly help me figure out an excel function that would allow me to make a list of payments by year (from 2008 to 2015) that are received on the second tuesday of every month.

## Future Value Of Unequal Payments

Jun 13, 2008

Does anyone know a formula that calculates the future value of an unequal payment stream? I am attempting to calculate the value of an investment plan where the periodic payment increases by 1% each period. I can get the answer by using the FV formula for each payment and then adding them up, but would like to be able to do it without having to do so.

## Automatically Converting Non GBP Payments Into GBP Amounts

Feb 12, 2014

I have some sales records that contain GBP payments, along with AUR, USD and EUR.

The GBP payments are not a problem, but a formula which could automatically convert AUR, USD and EUR into GBP payments would save a lot of time and reduce any chance of errors.

I have attached an example of the current layout.

Ultimately, I would like to put the sale in it's GBP amount where the boxes are blank & then a formula that detects either

A) If Original currency (the F column) is not GBP, then multiply the amount in the C column by whatever the exchange rate is in the G column.

B) If the exchange rate column G, has anything other than N/A, then multiply this by the amount in column C.

So, if the original currency in column F is in GBP, then I can enter data into the C column, and it will not change, however if the original currency in column F is not GBP, I would like the data in column C to change.

## Calculate Number And Amount Of Payments

Jun 17, 2009

The example:

Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used.

Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00

Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments.

So the sort of result I'm looking for would be like

Assume todays date is 19-04-09

A B C
12-04-09 £5.00 Last Week 4 Payments Value £45.00
12-04-09 £10.00
13-04-09 £10.00
14-04-09 £20.00

## Search For Payments Made In A Certain Month And Return The Sum

Sep 14, 2009

How can I search a range of cells that have a date entered by hand for a certain month and then sum the amounts given in another range of cells. For example, search several worksheets for payments made in September and then return the sum of those payments.

## Can Periodical Fixed Deductions/payments Be Inserted Automatically

Dec 20, 2009

Can periodical fixed deductions/payments be inserted automatically to the individual MONTH sheets of the workbook I uploaded ? To reduce the size of the file, I have left only ONE of the twelve month-sheets - the one for January.

Rephrased :
How to automate the entry of fixed amounts at regular intervals?

My guess is that from time to time, a macro would have to be run
- one of the macro's inputs would be the computer date and the other two inputs would be the value of the entry to be made and the value of the intervening period.

In practice there would be more than one regular entry and the values would be both positive ones and negative ones.
I guess that means one macro for each financial pattern.

Feasible or feesible (a multi-million-dollar-fee task)?

## Investment Calculator: Sheet That Will Take Payments Away From A Starting Balance

Jan 16, 2007

I need to create a sheet that will take payments away from a starting balance. The catch is that I don't know the starting balance. The interest is compounded annually at 3% and each withdrawl is different. My sheet needs to show the interest earned in each year and the account balance like a running sheet, after lets say 5 years there needs to be no money left.

## Calculate Future Value Of Monthly Recurring, Annually Increasing Payments

Sep 19, 2007

follows in paragraph 5 - but first, background!

I have a specific formula (received courtesy of some clever person here at Ozgrid (thanks!)) which I use to calculate the Future Value of a series of future payments that increase at a fixed annual rate and earn interest at a fixed rate.

Here it is: =Pmt1* SUMPRODUCT((1+Increase_in_payment)^(ROW( OFFSET(\$A\$1,0,0,Term,1))-1),(1+Return_on_investment)^(Term-ROW(OFFSET(\$A\$1,0,0,Term,1))+1))

(Example: \$1000 per annum (Pmt1) is invested for 20 years (Term). The interest earned on the \$1000 is 10% per annum (Return_on_investment). The \$1000 increases by 5% (Increase_in_payment) each year - i.e. 19 increases - answer: \$89,632 (rounded))

This formula assumes that the payment is made at the beginning of the period.

Question: I would like to change the formula to use MONTHLY payments made in advance, and interest earned on a monthly basis.

Because I REALLY do not know what the formula does, maybe I could ask for a detailed explanation thereof - maybe even from the person who supplied it to me (I cannot see who did!) - and then I can start fiddling with it myself if answers do not come.

Two previous posts of mine that dealt with somewhat different issues on the same formula are:

Determine Present Value From Future Value

and

[url]

## Sumproduct And Sum Array Formula (calculate The Salary Payments To Temporary Employees In A Particular Work Unit)

Jul 16, 2009

I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.

One method has been using this sumproduct formula:

## 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.

## Total Occurences: Formula That Shows The Total Payments Recieved For A Particular Month For A Particular Product

Jan 7, 2010

I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.

Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.

I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.

## Track Changes

Oct 21, 2009

So I got this code from http://www.ozgrid.com/VBA/track-changes.htm i followed the directions and pasted it in the workbook module but does not seem to be working. I am thinkning the copy and paste feature is messing something up anyone know what?

I have added a worksheet named Log

## Textboxes Not Keeping Value?

May 28, 2013

I have a big clunky spreadsheet that I thought I'd tidy up by adding a userform to keep the info

There's a combination of textboxes, option buttons, listboxes and a multipage. Everything seems to load correctly, and selecting an item in a listbox correctly fills the textboxes, but when I change the value of the textboxes, only 2 out of 4 holds that value and sends it to the correct cell.

The code that is supposed to send all the values is;

VB:
Private Sub CommandButton1_Click()
'check that a row has been selected
If Range("K3") = "" Then

[Code].....

The culprits are TextBox3 and TextBox4.

I've tried changing the names, I've checked that there are no other textboxes pasted on top, I've even tried adding in a MsgBox line to show the value of the textbox; if the form loads and the box grabs a value of "ABC" from the spreadsheet, and I change it to "DEF", when I hit the button to execute the above code the textbox goes straight back to "ABC".

But it's not an issue with TextBox2 ...

Other parts of the code include a UserForm_Activate block and 5 x ListBox_Click's.

## Keeping Only The Decimal?

Apr 30, 2013

I actually have time entered as a number and decimal.

10.29 means 10 minutes and 29 seconds. How can I get that to only seconds 629?

## Keeping 1st 9 Characters

Jan 9, 2009

I have a coumn starting from A2 onwards that holds data with various characters, all that i require is the 1st 9 characters (These are always numbers).

## Track Changes On Another Worksheet

Aug 6, 2007

I have a list of items that I keep track of when they were last used.

For example:
Item# Last Used
Item#1 8/27/06
Item#2 5/2/07
Item#3 6/30/07

What I would like to do is when I enter a date, it automatically tracks the changes so that I can not only see the most current date but all previous dates if I need to.

The ideal would be to transfer it to another worksheet so that is looks like the original except that is shows multiple dates after it instead of just the most recent.

For example:
Item#1 5/3/05 7/23/06 8/27/06

## Track Users

Dec 15, 2008

We have an excel file that I've developed that people in our department are supposed to be using - not that they want to - but it is an edict from our bosses. Is there a way to track who is actually using that file so I can verify that they are using it rather than just relying on them saying that they are?

## Track Changes & Macro's

Apr 23, 2009

I have a work sheet that has 113 tabs and I use two macros (both written with help from this forum - thanks), one to protect all of the worksheets and the other one breaks the workbook into 113 separate files. I want to use the tracking feature because I will be sending out these budget templates and want to identify the changes when I receive them back.

Problem is once I enable the tracking I can no longer run my macro’s; I get a VB dialog box with a red X and the number 400. Anyone know how to work around this problem?

## Track Changes From All Worksheets

Aug 7, 2007

I have found another user's code that will track changes for a single sheet, if that code is in the sheet. This code uses the Worksheet_Change function. However, I have just stumbled acroos the Workbook_Change function. The workbook function is exactly what I need, since I have multiple tabs that need to be tracked. However, the code does not seem to work. I can switch between the two functions, using the same code afterwards, but with the new workbooks phrase it does not work. I tried placing the Workbooks_Change in the "ThisWorkbook" object in VBA, but it does not seem to work there or anywhere else.

I have attached the file. The code is in the "thisworkbook" function.

For quick reference:

Private Sub Workbook_Change(ByVal Target As Range)
Dim r As Long, OutSht As Worksheet

Set OutSht = Sheets("Log")
r = OutSht.Cells(Rows.Count, 1).End(xlUp).Row + 1
OutSht.Cells(r, 1) = Target.Address
OutSht.Cells(r, 2) = Now
OutSht.Cells(r, 3) = Environ("UserName")
OutSht.Cells(r, 4) = Target.Value
OutSht.Columns("A:D").Columns.AutoFit
End Sub

## Keeping A Number Positive.

Sep 22, 2008

In a basic formula, I need to take one number from another. But I need to keep the answer to zero, not a negative number.

## Keeping A Running Count

Sep 29, 2009

I have a spreadsheet that has numbers in column A (example = 31, 15, 159) I want to count the number (159) each time it occurs and keep a running count of it in say column B... example..... A1 = 159, A2 = 31, A3 = 159, so B3 would show (2) as the number 159 has appeared 2 times thus far.