Lottery Syndicate Sheet To Show Members Behind In Payments |
Lottery Syndicate Sheet To Show Members Behind In Payments - Excel |
|
Hi All,
I've created a spreadsheet for a lottery syndicate (20 members), the cells in column A has the Member Names (1st name in A2)and the cells in row 1 has the weekly dates (1st date "12/10/2010" is in B1).
When a member makes a payment ($10/ week), I open the spreadsheet and write "10" in the which ever the next empty weekly cell is.
I'd like to be able to tell at a glance which members are falling behind in their payments and have just come across "conditional formatting".
The formula needs to see If the cell for a given date is empty (I've not entered 10 in it) and if it's also within 28 days of today then I'd like the entire row (not just the cell) to change red.
I cant get it to work, any ideas ?
This seems a great feature and I hope it can do what I want here, I'll just have to play around until I stumble on the right formula.
Jack
I've created a spreadsheet for a lottery syndicate (20 members), the cells in column A has the Member Names (1st name in A2)and the cells in row 1 has the weekly dates (1st date "12/10/2010" is in B1).
When a member makes a payment ($10/ week), I open the spreadsheet and write "10" in the which ever the next empty weekly cell is.
I'd like to be able to tell at a glance which members are falling behind in their payments and have just come across "conditional formatting".
The formula needs to see If the cell for a given date is empty (I've not entered 10 in it) and if it's also within 28 days of today then I'd like the entire row (not just the cell) to change red.
I cant get it to work, any ideas ?
This seems a great feature and I hope it can do what I want here, I'll just have to play around until I stumble on the right formula.
Jack
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Similar Topics
A previous poster explained what I also need; there were no responses,
so I thought I would try again.
Does anyone know of an Excel template--or a small stand-alone
program--that will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or low-cost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.
Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
with
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
principal
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all
entries."
Thanks very much.
so I thought I would try again.
Does anyone know of an Excel template--or a small stand-alone
program--that will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or low-cost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.
Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
with
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
principal
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all
entries."
Thanks very much.
From limited experience I know that excel calculates dates via serial numbers.
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
Hello, please help me out with this one.
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!
I'm trying to format a worksheet to show how many days overdue certain tasks are, and running into a couple of issues. The sheet is set up with Due Dates in Column A, Days Overdue in Column B, and Date Completed in Column C. My first thought was just to use as simple a formula as possible in Column B =Today()-A2. This worked alright for tasks that already had Due Dates assigned, but if Column A was blank I'd get a high number in Column B. I also realized that tasks will continue to show as overdue even after I enter the completed-on date in Column C. Is there a relatively simple way to tackle this formula? Thanks in advance for suggestions. (I've attached an example with the column headings I want to use.)
I often use if statements to return empty cells, for example:
=IF(a1=0,"ERROR","")
The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.
Is there a way to designate, in such a formula as above, to return a TRULY empty cell?
Thanks
=IF(a1=0,"ERROR","")
The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.
Is there a way to designate, in such a formula as above, to return a TRULY empty cell?
Thanks
I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),"d") What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.
Hello
I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.
I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc.
When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result.
I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before.
Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.
There's about 50+ cells that need referencing and I got to get this done for work.
Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time.
I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.
I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc.
When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result.
I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before.
Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.
There's about 50+ cells that need referencing and I got to get this done for work.
Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time.
In my pivot table I have a lot of cells without any data. The report looks like garbage with all the (BLANK) sohowing up in the report.
I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white -did not work
I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.
any Ideas???
Thanks
I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white -did not work
I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.
any Ideas???
Thanks
Hi there,
I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!
In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:
=Sheet1!A3 (or whichever cell it is)
That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).
If anyone could point me in the right direction, I'd be very very grateful. Thanks.
I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!
In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:
=Sheet1!A3 (or whichever cell it is)
That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).
If anyone could point me in the right direction, I'd be very very grateful. Thanks.
In Excel there is a difference between cells that are blank (= "") and that are empty. You can make blank cells empty by selecting them and pressing the delete key, but I need a fast way to do this on all blank cells in a large worksheet. The only way I have found is with a VBA macro that loops through every cell, tests for '.Value = "" ' and then uses the '.Clear()' function, but doing this on 30 columns x 10000 rows is far too slow. Any solutions?
(The reason I need to do this is for importing into Access, the database treats empty cells as NULL which is what I want. Blank (but not empty) cells screw the import process up.)
(The reason I need to do this is for importing into Access, the database treats empty cells as NULL which is what I want. Blank (but not empty) cells screw the import process up.)
I have a formula in cell C1 (=A1/B1). I want to copy this formula down to row C20, however rows A2 to A20 are empty so C2 to C20 show (#DIVO!). Is there something I can put in the formula to make column C be blank until the data is enter into Columns A & B?
Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?
I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.
Thanks in advance!
I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.
Thanks in advance!
I have a template that automatically populates the date field with the
current date when opened, using today(). Users then Save As to have an
archive copy of their spreadsheet. However, when they go back to access
their saved sheet, the date changes from the created date to the current
date. How do I stop this in Excel?
I know that Word lets you do this by changing the code from {DATE} to
{CREATEDATE}, but I can't find a similar setting in Excel.
Please help and thank you!
current date when opened, using today(). Users then Save As to have an
archive copy of their spreadsheet. However, when they go back to access
their saved sheet, the date changes from the created date to the current
date. How do I stop this in Excel?
I know that Word lets you do this by changing the code from {DATE} to
{CREATEDATE}, but I can't find a similar setting in Excel.
Please help and thank you!
Hi all,
I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!
I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.
I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)
Any help you can give would be much appreciated.
Cheers,
Kevin
I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!
I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.
I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)
Any help you can give would be much appreciated.
Cheers,
Kevin
I am trying to automatically capture and record the date of a cell's last change in value (date stamp). I have an item price list and if a particular cell gets updated I want to automatically record the date of change of that cell. I realize that after I change cell A1 I could tab to cell B1 and enter Ctrl+; but if I have a hundred new prices to enter I don't want to do that (plus me or the data entry person might forget).
For instance,
A B
1 $32.17 8/17/08
2 $43.20 9/12/09
If I go in and update cell A1 to $33.98 today I would like cell B1 to read 2/16/10. But I don't want cell B2 to change.
Thanks
For instance,
A B
1 $32.17 8/17/08
2 $43.20 9/12/09
If I go in and update cell A1 to $33.98 today I would like cell B1 to read 2/16/10. But I don't want cell B2 to change.
Thanks
Today I ran into an odd problem. I typed in values for column A rows 1 through 10 then values for column B rows 1 through 10. then in column C, I made the formula C1=A1/B1. The math was correct it showed 542 in the C1 cell. So I dragged that formula down and it showed 542 in all column C cells which is not correct. And when I went to check to see if the formula was correctly dragged it was. For instance, the formula in C2 is =A2/B2 however the value of that cell showed 542 which was not the correct math/value.
But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.
This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.
Thanks for any tips on this.
But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.
This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.
Thanks for any tips on this.
Hi,
I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row.
So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted.
I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns("B:D") but it deletes everything.
Code:
I'm sure it's something simple... like me !
Any help much appreciated
Wrightie
I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row.
So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted.
I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns("B:D") but it deletes everything.
Code:
Sub Step4()
On Error Resume Next
Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
I'm sure it's something simple... like me !
Any help much appreciated
Wrightie
Hi was wondering if anyone can help.
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
Any help would be very much appreciated
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
Any help would be very much appreciated
Hello,
I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:
I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.
I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?
If somebody could help me out that would be great.
Thanks,
Randy
I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:
I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.
I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?
If somebody could help me out that would be great.
Thanks,
Randy