Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Keeping Track Of Payments

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?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Keeping Track Of How Many Times A Number Shows Up
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.

View Replies!   View Related
Appropriate Template/worksheet Needed For Keeping Track Of Turnover
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.

View Replies!   View Related
Keeping Track Of Which Macro Is In Which Module Since You Can't Rename The Module?
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?

View Replies!   View Related
Management Of Payments
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"?

View Replies!   View Related
Future Value Of Unequal Payments
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.

View Replies!   View Related
Payments On Second Tuesday Of Every Month
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.

View Replies!   View Related
Calculate Number And Amount Of Payments
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

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

View Replies!   View Related
Search For Payments Made In A Certain Month And Return The Sum
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.

View Replies!   View Related
Can Periodical Fixed Deductions/payments Be Inserted Automatically
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)?

View Replies!   View Related
Investment Calculator: Sheet That Will Take Payments Away From A Starting Balance
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.

View Replies!   View Related
Calculate Future Value Of Monthly Recurring, Annually Increasing Payments
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



View Replies!   View Related
Sumproduct And Sum Array Formula (calculate The Salary Payments To Temporary Employees In A Particular Work Unit)
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:

View Replies!   View Related
Amortization Schedule: Auto Update Based On Loan Period & Number Of Payments Per Year
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.

View Replies!   View Related
Keeping 1st 9 Characters
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).

View Replies!   View Related
Total Occurences: Formula That Shows The Total Payments Recieved For A Particular Month For A Particular Product
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.

View Replies!   View Related
Keeping Macros With File.
When I copy/paste an Excel 2003 file containing macros into an email and send and open into another computer, the macros are gone. How can I ensure the macros go with the file?

View Replies!   View Related
House Keeping Macro
I want to create a kind of "Housekeeping Macro" that will run based on one single piece of information a number.

I want an inputbox to ask me for a number.

Based on that number I want the macro to do the following -

Go to Ws1 - Membership
Seach down column A until it finds the number put into the Inputbox
When it finds it do the following

Go to Column N and enter todays date as a value.
Go to Column Q and Enter No
Delete the data in R:S and U:V and X:Y and AA:AB and AD:AE

Go to Column J This contains the name of another worksheet i.e. Data1

Go to the worksheet Data1

Search down Column C and find all instances of that the the number entered into the inputbox (there could be several).

Go to each instance and delete the number.

View Replies!   View Related
Keeping Info Together While Sorting
I have groups of information I need to stay together, but I'd like to be able to sort them by last name. Today, Tomorrow and Long Term are conditionally formatted (=ISBLANK is green and =ISTEXT is red). As long as the information stays together in a group, that won't be a problem. I have a hidden column A where every cell next to the form refers back to the cell with the last name in it. But when I tried to sort by that, it still seemed to mangle my form. There are no merged cells here. There will eventually be dates, locations (booked) and absent, etc.. filled in. I just want to be able to sort this entire block, without changing anything in it, by last name. There are about 100 of these blocks of info.

LAST NAMEFIRST NAMETodayTomorrowLong TermDATEBOOKEDReason for SubAbsent EmployeeHours is WorkingDayHappyPhone Number:555-5555Cell Number:555-1234Special Skills:Notes:

View Replies!   View Related
Keeping A Number Positive.
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.

View Replies!   View Related
Keeping The Chart Name As The Activechart
I'm looking for when 'Chart 1' appears in the code, I would like it to link to the current chart I've just created.

View Replies!   View Related
Keeping A Running Count
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.

View Replies!   View Related
Removing Duplicate Rows, But Keeping The First One.
I have an excel spreadsheet with approximately 10000 rows. There are approximately 10 columns...

Column A - person's ID number
Column B - name
Column C - Street Address
Column D - City
Column E - State


It being such a large list there are many many duplicates (it was pulled from a bunch of different excel spreadsheets and combined into one via copy and pasting one document into the other).

I figured out a way to have excel highlight the duplicates in a different color... but scrolling through 10k people and deleting the duplicates is obviously very tedious (approx 40% of the spreadsheet is duplicates).

Is there a way to sort out the duplicates (via their ID number, column A) so that there are no repeats? I want to get rid of the 2nd/3rd/4th (etc) occurance of the person's information but I was to obviously keep the first occurance.

I tried using Advanced Filter but I must have done something wrong because it shrunk the list too far down.

View Replies!   View Related
Keeping File Size Down (2003)
I am about to undertake a fairly large spreadsheet development project. One of the key requirements is that the size of the spreadsheet be kept to below 4mb, preferrably below 1mb. This is due to an unavoidable restriction.

Although the spreadsheet will not have large volumes of data, it will contain approximately 30 sheets. There are no graphics embedded in the sheet. I imagine there will be a reasonable amount of VBA and/or forumulas in the sheet. Each sheet will basically be a data-entry form with a number of formulas. No sheet will have more than 100 rows.

Without knowing the full details of the spreadsheet, it is difficult for you to give a definite answer as to the size of the sheet, so I am simply looking for some helpful tips in keeping the size down.

Do formulas take up more space than VBA code? When I format cells, should I format the entire row, or just the range? Do features like sheet and cell protection take up a lot of space?

View Replies!   View Related
Keeping A Named Range Dynamic
In the following line of code in my subroutine I name this range
which runs from A2:J2, the problem is later in the routine I delete certain unwanted columns which then shrinks this range. I always want it to be 10 colums wide. How do I make it dynamic with vba?
ActiveWorkbook.Names.Add Name:="TranslateShow", RefersTo:=Worksheets("TemplateTest").Range("$A$2:$J$2")

Can I also substitute the workbook actual name for ActiveWorkbook?

View Replies!   View Related
Keeping Blank Rows When Sorting
I have a spreadsheet that I want to keep the blank rows that seperate the data for asthetic reasons. Can I sort just the rows with data in them and still keep the blank rows where they are?

View Replies!   View Related
Dependent Drop Down List Keeping Old Value
My problem is a dependent drop down list that keeps the previous value when a different value is picked in the first drop down list. The dependent drop down needs to change to empty/blank.

The first drop down list is in cell C3.
The dependant drop down list is in cell C4.
The source in C4: =INDIRECT(VLOOKUP(C3,allnames,2,2))

I had a look at various answers on the internet but somehow cannot get it to work (and that is more of a reflection of my own skills than the writers of those answers!).

View Replies!   View Related
Merging Two Columns And Keeping The Data From Both
There is one crucial feature to the 2007 Excel that has been overlooked.

Throughout all versions of Excel there has always been the feature of
converting text to table, however there is no way to do this in reverse.

There is no way to merge two columns of data and to keep all of the data
without one column overwriting the other. If only there were an automatic
way to merge two columns of data and to be able to place a delimited
character in-between, just like the ‚ÄúConvert Text to Columns Wizard‚ÄĚ, except
in reverse. Currently, the only way to merge two columns of data is to
manually go row-by-row and cut and paste them together. However, for 500,000
rows of data… this is impossible. Or to use a function to merge two columns,
however this requires that the original two columns remain. This is also
unacceptable. If Microsoft really wants to make Excel more functional, how
can this vast improvement be overlooked?

View Replies!   View Related
Keeping The Same Cell When Switching Worksheets
I would like my cursor to stay in the same cell when I switch worksheets. If I'm in B40 on worksheet one, and switch to worksheet two I would like the cursor to be in B40.

I'm using very basic excel functions with no scripting at the moment.

View Replies!   View Related
Keeping Listbox Selections After Calculation
I have been using VBA for 2 weeks and have run into a problem I can't find an answer to from searching various sites. I have a listbox with two columns (one name, one numbers). I have two buttons, one that puts the selections onto a sheet and then calculates the associated numbers and then updates a label with the sum of the selections. If they accept the selections, they can then press submit to record the data in the sheet. My problem is that after they press the calculate sum button the selections on the list dissapear, I need them to remain as they may need to change them based on the sum amount. How can I get the listbox to get the sum as necessary (and display within the userform), but not lose the selections in the process.

View Replies!   View Related
Graph Automatically Keeping Most Recent 18 Months
I have a word document that has all kinds of graphs embedded in it. When I double click the graphs, an excel spreadsheet comes up. The spreadsheet contains quite a few years worth of information. I, however, need it to automatically refresh to show only the most recent 18 months of data on the graph. The documents are in MSOffice 2007.

View Replies!   View Related
Keeping A Running Total In A Single Cell
Is it possible to have numbers added to the same cell and have excel continue to calculate the addition for me in that same cell......ex: I have the number 8 in cell d2 and I want to add the number 8 to that cell and have excel add the 8 to the previous 8 for a total of 16 in the same cell.....the next time I would add 5, and the total would be 21? Can this be done in a single cell?

View Replies!   View Related
I have 5 columns. The first 4 columns only contain 1 line each, the last column can contain anywhere from 1-6 lines. How can I do a sort and keep all the text in the last column together? By way, it was imputed on separate lines.

View Replies!   View Related
Keeping Info In Forms And Send To A Spreadsheet
How do I keep all the info entered onto a form intact even if it has been entered so that someone can click a back button to review what they have entered? In other words even if a user closes the form is there a way for that info to stay on the form?

Private Sub cmd_Enter_User_Input_Click()
Worksheets("PowerAnalysis").Activate 'Make Power Config_Draft_New 2.xls.xls active workbook before entering data

Range("B2") = TextBox1.Value 'WCID
Range("C2") = TextBox2.Value 'CSA
Range("D2") = TextBox3.Value 'HDT Terminal Address
Range("G2") = ComboBox3.Value 'Tech Type
Range("H2") = TextBox5.Value 'Cabinet Size
Range("K1") = TextBox6.Value 'Existing ONU's
Range("L1") = TextBox7.Value 'PGA Cables
Range("M1") = TextBox8.Value
Range("N1") = TextBox9.Value
Range("O1") = TextBox10.Value
Range("K2") = TextBox11.Value
Range("L2") = TextBox12.Value...............

View Replies!   View Related
Deleting Lines - Keeping Formulas Fixed
With a macro i want to delete some lines in sheet1. In sheet2 however i have formulas that point on sheet1 .. like this


Now when i delete line 2, the result would look like this

Is there a way to make these formulas pointing at the same cells after line two is deleted? And i do not mean a work around, with different formulas. I just want the Formulas in sheet2 to remain unchanged, regardless of what happens in sheet1...

View Replies!   View Related
How Do I Save A Workbook Keeping Only Formatting And The Values
I would like to know if there is an efficient way to save an excel workbook, with multiple worksheets as an .xls. I want the file to keep the formatting from the original file, while only saving the values of the original file. All the numbers in the original file are hyperion driven but the file is going to be emailed to people without hyperion. Is this possible?

View Replies!   View Related
Copy Formulas While Keeping Cell References
I need to copy a bunch of cells that contain formulas without altering the cell references. I know I could change each formula to contain absolute cell references, then copy and paste special with formulas, but this is alot of work, and following that I'd need to change the references back again from absolute to relative in both locations. So, is there a way to quickly copy and paste formulas in multiple cells without altering the cell references?

View Replies!   View Related
Move Data In 1 Column To 4 Different Columns Keeping Same Order
I have a table with one column of data. The data in this column repeats with 4 relevant pieces of information that I want to put in 4 different columns (fields) in a different spread sheet (or the same would work better and I would just delete the first column when done) keeping the same order the data is now in.
The data currently repeats in a regular pattern (i.e. 123412341234 with no other data in between). I would like to do this with a macro. Could someone help write a macro that will do this

View Replies!   View Related
MACRO Saveas Keeping Current File Open
I have the following Macro that I run assigned to a Button.

Sub AskAndDo()
If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
ActiveWorkbook.SaveAs Filename:="\gamingntcompanyFLOAT MASTERFloat_Sheet_" & Format(Date, "_YY_MM_DD")
End If
End If
End Sub.......

View Replies!   View Related
Deleting Duplicate Rows Keeping Lowest Value In Column B
I need the macro for deleting duplicate rows only it should keep one lowest value row.

for e.g.

Column A Column B

TS1234 100
TS1234 50
TS1234 200

Macro will compair dublicates in column A & it will keep lowest value row (i.e. 50 value in Column B), other dublicate rows will be deleted.

View Replies!   View Related
Multiplying By 0 But Keeping Existing Result - Conditional Multiply
I know when you multiple anything by 0 the answer is 0.
But I have a list of numbers I need to multiply out
but want to keep the total even if there is a 0 in the sequence

So A1*A2=150 if I then multiply by A3 I get 0, but I need to ignore the cells that have 0.
So for the last example my answer would be 140.

View Replies!   View Related
Move Data Whilst Keeping Formulas Intact
i have data in worksheet 2, a table with formulas using worksheet 2 in worksheet 1, i need to move the data in worksheet 2 to another worksheet, but if i use cut or copy and paste the formulas do not track its movement, so how do i move the data to another worksheet so the formulas know where it went? i thought if you could select the data and drag the data straight into another worksheet but how?

View Replies!   View Related
Summary Sheet Update Keeping Set Columns In Place
using code to take raw data from one workbook <book1> tab <Phase1RawData> and pasting to summary workbook <book2> onto <SummarySheet>. New data is available with each Phase (14 in all) and will eventually fill all cells as indicated in the Summary Sheet Page. However raw data has in any one Phase only the columns up to the Phase its currently at. So Phase 1 will only have Phase 1 column, Phase two will have columns for Phase 1 and 2, Phase 3 will have 1,2,3 and so on (the example Book1 has two example sheets for phase 1 and phase 2 data - so each phase has a column added with the previous columns remaining.)

The problem is in having the summary sheet always show all 14 phases colmns (as in the example attached) irrespective of which Phase is being updated. So if up to Phase 3, insert the raw data available will be colmns for Phase 1, 2 and 3 - but I need ensure after that has been updated only the remaining Phase colums to 14 show. That is, if Phase 1, 2 or 3 etc are now in place, insert the remaining blank Phase columns to, and including Phase 14. Need to do this at end of each phase until 14 is met. The data up to column S is all from the raw data original sheet - Items from Column T I insert independantly as the data is transferred.

View Replies!   View Related
Track Changes
So I got this code from 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

View Replies!   View Related
Clear Cells That Have Numeric Data. Keeping Text And Formulas?
I have a series of worksheets that are formatted for data imported from various scripts.

What I want to do is parse through each worksheet and cell, when the cell has numeric data, I want to clear the cell.

I do not want to clear the cells on the following conditions:
Cell has Text
Cell has Formula
Cell has date, month or time

View Replies!   View Related
Macro To Copy Formula While Keeping Original Cell References
I'm trying to create a VBA macro that will allow me to copy a formula from one sheet to another whilst keeping all the original references.


If the formula on Sheet1 is:

= sum(A1:B6)

then the copied formula on Sheet2 would read


You can do this by cuting the cell, but I don't want to do this, I want to leave the original cell unchanged.

I'm sure there is some simple VBA code to do this, but I can't seem to figure it out.

View Replies!   View Related
Keeping An Added Column's Data Attached To Records After Updating
I copy a table from a data dump dbf file into worksheet 1. I enter a separate column titled "Comments" and use this for status purposes. My problem is that when I refresh the worksheet, my comments don't stay attached to the rows/ records that they were originally entered for.

View Replies!   View Related
Merge Duplicate/Similiar Rows Keeping Data In Same Columns
After sorting and filtering rows with in a set range I will have several rows that are almost duplicates. This is normal and expected due to how the workbook is used. Among these rows also will be several single rows that are not duplicates. It is important that I combine any two duplicates into one row. Example:


1 NameA 0XX15930777PS101300PS9

2 NameA0XX15930777PS91200PS10

3 NameX1159XXP555FBX1545PS9

4 NameB0A1234P123PS101263PS9

5 NameB1A1234P123PS90512PS10

What I need is this end result:


1 NameA 0XX15930777PS91200PS10PS101300PS9

2 NameX1159XXP555FBX1545PS9

3 NameB1A1234P123PS90512PS10PS101263PS9

Itís important that the data in each column stay with in that same column. Also of course it needs to be on the same row with the same person (NameA and NameB). The Columns that would determine if itís a duplicate are D and E Ė. I would need this to be preformed via macro or some easy way so that others will not have a hard time. It will be on a protected Shared Workbook with Excel 2003. I've enclosed a Sample. How can I sort these or accomplish this and maintain the data where it needs to be?

View Replies!   View Related
Track Changes On Another Worksheet
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

View Replies!   View Related
Track Users
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?

View Replies!   View Related
Track Changes & Macro's
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?

View Replies!   View Related
Copyright © 2005-08, All rights reserved