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
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.
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.
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"?
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.
Calculate Number And Amount Of Payments
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
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.
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)?
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
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.
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.
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:
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.
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.
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?
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?
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!).
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?
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.
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.
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?
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...............
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...
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?
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?
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
MACRO Saveas Keeping Current File Open
I have the following Macro that I run assigned to a Button.
If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then
If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then
ActiveWorkbook.SaveAs Filename:="\gamingntcompanyFLOAT MASTERFloat_Sheet_" & Format(Date, "_YY_MM_DD")
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?
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
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:
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.
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.
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:
CREATE TABLES LIKE BELOW?
ABC D E F G H I J K
1 NameA 0XX15930777PS101300PS9
What I need is this end result:
CREATE TABLES LIKE BELOW?
ABC D E F G H I J K
1 NameA 0XX15930777PS91200PS10PS101300PS9
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?
Track Changes On Another Worksheet
I have a list of items that I keep track of when they were last used.
Item# Last Used
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.
Item#1 5/3/05 7/23/06 8/27/06
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 From All Worksheets
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