Computer Support Forum

Solved: Using excel to count a specific date range

Question: Solved: Using excel to count a specific date range

I am trying to build an excel sheet to tell me my future manning levels, (for example, how many workers will I have 6 months from now). I attached (or at least tried to) a very small scale "example sheet" of what I am talking about. It basically will have one cell to put a start date in for which month to start gathering data. This is followed by the months following the start date going out a year. The data comes from a separate sheet and the data is based off of a column with dates that I am suppose to receive inbound and a column with dates of personnel departing. I am trying to take the month that I need the data for, count the personnel that should have arrived by that date and add it to those that have not departed by that date. Every formula that I have tried counts every cell to include the empty ones. This is the last formula that I tried =SUM(IF(Manning!$C$3:$C$14<=H3,1,0))+SUM(IF(Manning!$D$3:$D$14>=H3,1,0)). I have also tried COUNTIF formulas. How do I get it to count only the cells that have a date in it that falls with in the range? Sorry if it is confusing but hopefully the attached sheet will help explain. I am using an XP machine.

Relevance 100%
Preferred Solution: Solved: Using excel to count a specific date range

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Solved: Using excel to count a specific date range

I put in a check to put in a ZERO if there is an empty cell.

=SUM(IF(Manning!$D$3:$D$14<=H3,IF(Manning!$D$3:$D$14="",0,1),0))+SUM(IF(Manning!$F$3:$F$14>=H3,IF(Manning!$F$3:$F$14="",0,1),0))

3 more replies
Relevance 84.97%

I want to know an exel formual for the following - Any help would be great

Date Range
01/12/2013 - 03/14/2013

How many days from the date range 01/22/2013 - 02/21/2013 are in the above date range

So answer for the above would be 31

Thank you for your help!
 

Answer:Excel 2010 - Count # Of Days In A Date Range That Are Part of Another Date Range

Hi LucianC - try entering this info:
Start Date in A1
End Date in B1
=DateDif(A1,B1, "D") in C1
 

2 more replies
Relevance 77.14%

Hi,I just want to know if its possible to do a macro wherein I can select the dates to be read in my report. For instance, I have a Table (below) where column A is the Date, column B is the name of Agents, Column C is the Section and the Column D is the Error Category. A window will pop-up where in I can select the date range the the macro will read.Usually I have a monthly report and I keep on sorting and filtering the Table if my boss is asking for a weekly report. The report usually are:How many errors does each Agents received on this particular dates?What section has most errors in this particular dates?What are the most common errors in this particular dates?What are the most common error of each agents in this particualr dates?What are the most common errors of each section in this particular dates?Date Name Section Error Category21-Nov Peter Delta Name21-Nov Jack Alpha Age21-Nov Mike Bravo Address21-Nov Cris Alpha Name21-Nov Sam Charlie Name21-Nov Noah Charlie Name21-Nov Jack Charlie Name25-Nov Mike Charlie Comment25-Nov Cris Delta Comment25-Nov Peter Alpha Comment25-Nov Jack Bravo Comment25-Nov Mike Alpha Name25-Nov Cris Delta Address29-Nov Jack Alpha Address29-Nov Mike Bravo Age29-Nov Cris Alpha A... Read more

Answer:Date range Count using macro in Excel

re: "I just want to know if its possible to do a macro wherein I can select the dates to be read in my report."Yes, it's possible.Posting Tip: Before posting Data or VBA Code, read this How-To.

5 more replies
Relevance 76.27%

I need to work out a formula that will allow me to count the number of products despatched between two dates. The dates are in cells A1 and B1, and the other cells with dates of despatch in are in cell C1 to C100. I want to have another cell which basically counts the number of items despatchedbetween the dates in A1 and B1. Can anyone help?
 

Answer:Count number of cells with value between date range - Excel

I'm sure there may be a better way to do it but one, quick and dirty way would be to use column D with a formula that tests each "C" cell to see if it falls between A1 and B1. If it does, have the formula print an "X" and then simply count the "X's".

D Column Formula: =if(and($c1>=$a$1,$c1<=$b$1),"X","") -- Put formula in cell D1 and drag through D100
Cell D101: =countif($c$1:C$100,"X")

A simpler way could also be to put a formula in cell C101: =subtotal(9,$c$1:$C$100) -- After you place the formula, you then apply a filterto cells C1:C100 testing for greater than/or equal to A1 and testing for less than/or equal to B1. Apply the filter with the C1 through C100 highlighted and view the "COUNT" in the status bar.

I hope this helps!
 

3 more replies
Relevance 89.79%

In Excel, I need to do a count based on 2 criteria. Criteria 1 looks for A, criteria 2 looks to see if A falls with in a selected date range.

Answer:A count on 2 criteria, 2nd is a date range

Thanks for letting us know what you need.If you need some help with that, feel free to ask.

4 more replies
Relevance 89.79%

Could some one please help me with the formula for this problem?I need to count the result based on 2 criteria.The first criteria looks for a name match and the second criteria looks for a match within a set date range, eg. the month of Feb.The source data for the name match is in column B from B2:B1000.The data for the date is in column C from C2:C1000.E22 Contains the date 2/1/10E26 Contains the date 2/1/10F26 Contains the date 2/28/10This is the formula I have been trying to use but haven't be able to make work.=COUNTIFS(B2:B1000,$E2,$C$2:$C$1000,E22>=E26<=F26)I'm sure the problem has to do with E22 and the way I am trying to discribe the range but after trying numerious combinations and expressions I'm still not able to get it to solve.On a seperate sheet I was able to successfully use the following formula =IF(B3=1*(D3>=40210),AND(D3<=40237))to give a TRUE or FALSE answer. Column B was reduced down to 1 or 0 for the name match and column D was the dates.I could use this to solve my problem but I wouild wind up with an incredibly large sheet of data to cover the 12 months and the 20 names for which I need to pull data. This doesn't seem at all practical especially when it seems possible to reduce it all down to 1 line of formula if I can just get it right.Any assistance would be greatly appreciated.ThanksA.

Answer:A count on 2 criteria, 2nd is a date range

I figured it out myself=COUNTIFS($B$2:$B$1000,$E2,$C$2:$C$1000,">="&E$24,$C$2:$C$1000,"<="&E$25)

2 more replies
Relevance 89.79%

I have a sheet with two columns. Column A contains dates formatted as Dates (4/25/2005) and it appears in the formula bar as "4/25/2005 3:52:52 PM". Column B contains numbers, both positive and negative. What I am trying to accomplish is to count the occurrences of positive numbers in column b as they occur by month and year. In other words, I want to see how many times a positive number occurred in March of 2007.

I have tried using the COUNTIFS function but it shows an error. I believe my error is in how I'm writing the date range into the formula but since I am a neophyte, it could very well be in how I'm asking it to count positive numbers.

I would sure appreciate some help.
 

Answer:Trying to Count Occurances in a Date Range

7 more replies
Relevance 88.15%

Good afternoon all,

Long time no seek help!! (cos I learnt so much last time )
I have a worksheet containing a list of stock i.e. Col A = Date; Col B = Product Description; Col C = Amount of stock

How can I (hopefully using a macro) end up with a report containing the information in Col A; B and C but sorted/sub-totalled as follows :-

Stock older than 18 months
Stock held in store for 12 - 18 months
Stock held in store for 6 - 12 months
Stock held in store 0 - 6

Does this make sense - Is this possible?

Thanks if you are reading this !

Moll
 

Answer:Solved: Excel Date range

16 more replies
Relevance 86.51%

Please Help - ASAP - Thank you in Advance.
(I did also post this under Business Applications, before realizing it should probalby go here - sorry about that)

I'm just STUCK on this one last bit of my code to finish off this macro for a meeting today

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

Maybe I'm making this sound too difficult... at this point all I need is:

Columns A-E, K, P, S-U, W, AB-AC, AE-AH, AJ-AK, from the "TargetData" (Defined Range) on Customer Targets worksheet of Test1, to Paste into Columns A-S of the Build Worksheet

This should be easy right? I'm having a mind block...
 

Answer:Excel Macro - Move Specific Columns from Range to New Range - ASAP If Possible

There have been a bunch of views, but not replys or suggestions. Am I not including the right information? Is this not posted in the right place? Can someone please help me?
 

2 more replies
Relevance 86.51%

Please Help - ASAP - Thank you in Advance.

I'm just STUCK on this one last bit of my code to finish off this macro.

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

I think I've attached a sample of the code, and some test workbooks I've been using.

Any advise would be helpful. I know this should be easier than I'm probably making it...
 

Answer:Excel Macro - Move Specific Columns from Range to New Range

16 more replies
Relevance 85.69%

Hi, I'm a beginner when it comes to formula for selecting date ranges! I have tried =SUMIF combos but and hitting a wall. Any help would be very welcome. Pretty basic the request.

2 colums, 'A' with the year (currently as plain number) 'B' with a simple number. There are roughtly 5/8 entries for each year, from 1989 to 2008. I need to sum 'b' for a range of years, (say 1990 to 1995), I can do it for one year value but not more.

Just tired this =SUM(IF(D,">1995",E:E)(D,"<1990",E:E)) but did not work.

[I do have a slightly more complicated request of using real dates and being able on select all for the last 15 years for example and 15 to 20 years, but I think we should leave that for another day}

Any help very welcome, thanks.
 

Answer:Solved: Excel 2003 - Selecting data within a date range

16 more replies
Relevance 84.46%

G'day,

I have another question regarding conditional formatting in Microsoft Excel!

I have a list of cells which contain text specifying fortnight periods. For example, one cell has '19th June, 2008 - 3rd July, 2008', another cell has '3rd July, 2008 - 17th July, 2008', and so on.

I was hoping to include a formula so that when today's date is within the range of one of those fortnight periods, the cell containing the fortnight period would take on a particular format. For example, today is the 1st of July, 2008, so the cell containing the '19th June, 2008 - 3rd July, 2008' cell would take on a red background indicating that it is the current fortnight.

If anyone is able to guide me to a solution, I would greatly appreciate it.
 

Answer:Solved: Conditional Formatting Dependant On Date Range In Microsoft Excel

16 more replies
Relevance 83.64%

Hi All (please refer to entry #4 with updated excel)

Thanks for helping.

Thanks to Turbodante, I am able to sum a range of colunm data in a macro (refer below), but now I need to be able to choose how much of the column to sum using a date that it stops at.
So rather than doing the whole table, I need it to just sum, as per the example attached, from July09 to the top of the table. Also each month additional data will be added, so its needs to be able to keep summing the data from the date to the top of the table as new data is entered each month.
Also different sheets will need to pick up the date where it needs to end. This will be in the worksheet as a manual entry (refer cell E18)

Sub Macro1()
Dim bRow As Long
bRow = Range("b4").End(xlDown).Row
Cells.Find(What:="Cell Name").Offset(1, 0) = "=sum(b4:b" & bRow & ")"
End Sub

Thanks for your help

Strybes
 

Answer:Solved: Microsoft Excel 2003 Sum Section using date to determine range help needed

10 more replies
Relevance 83.64%

Excel 2003a = shift (range name)b = 10/24/10 (cell reference)c = 10/30/10 (cell raference)need to count shift with occurance in Date Range 1 if >=b or <=c or Date Range 2 =if >=b or <=cSame date within to seperate range names.Cannot seem to get this to work using SUMPRODUCT or SUM array. Assuming it is a syntax problem, but not sure.Have been struggling off and on for days. Please help!

Answer:Excel 2003 Date Range with Range Names

Please review the How To referenced in my signature line and then post an example of your data.Include some input data and the expected output based on that data.With a better understanding of how your data is laid out we should be able to offer a solution.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 82.82%

Hi
Please could you help me with a problem in Excel 2000.

In its simplest form, one worksheet contains a table with 2 columns - Company Name and Response. In the Response column the value will either be 0 or 5.
EG

COMPANY RESPONSE
Amco 0
Britta 5
Amco 5
Amco 0
Britta 5
etc...

On another worksheet, I want to show a count of the number of 5's and a count of the number of 0's for a pre-selected Company. I can use the COUNTIF function which will show the number of 5's in the whole table, but I can't seem to get it to work with a condition ie. only count the number of 5's for Amco, where Amco is selected in a cell on the worksheet.
I hope this makes sense and I hope there is a solution.
Any help will be gratefully received.
Many thanks.
Janice
 

Answer:Excel - How to look up a value and return a count of a specific value

=SUMPRODUCT((Sheet1!A1:A15=Sheet2!E6)*(Sheet1!B1:B15=5))

Where E6 on Sheet2 Contains the cell with the company name in it. Just change the ranges in the formula above (A1:A15 and B1:B15) to reflect the correct range of cells containing the company names and values on Sheet1. I have attached an example workbook.

You can also customize the reference cell (E6) using Data Validation or a combobox to allow the user to select the reference from a list.

P.S - The formula above only counts the number of occurences of the number 5. Just copy and modify the formula to do the same with 0 values.
Rollin
 

2 more replies
Relevance 82.82%

I have a spreadsheet that shows all of the upcoming events at my work (I've attached a very basic example of the way the spreadsheet is set out).

When an event passes, I'd like the spreadsheet to automatically change the formatting of the rows that contain events that have already passed.

For example, today being 14.12.09, an event for yesterday (13.12.09) would be highlighted in red to show that the day has passed.

If anyone can provide any detail on how to achieve this, I would greatly appreciate it.
 

Answer:Solved: Conditional Formatting Based On Date Range In Microsoft Office Excel 2007

Select your data, then Format > Conditional Formatting.

Condition 1; Formula is:

=$A5<$D$2

Click Format > Patterns > red colour > OK.

Click OK.
 

2 more replies
Relevance 82.41%

HI guys,

I am using Microsoft Excel 2003 and am trying to create a scheduler for my work place.
What I have to do is allow the user to enter data in the input sheet (by input meaning Dates) and then accordingly scan the schedule sheet and find the date entered on the input sheet, following which print the appropriate schedule opposite that date for that particular project.

For Example .....
If a person is working on a project called apple in cell D (effectively making it the 3rd project in the list) and the user enters the project start and end dates as 5th June and 13th June. So what I have to do is scan the Schedule sheet for these dates and enter the appropriate text of Start and End before these dates in the D column (i.e under the respective project).

How to do this I am clueless as I am a complete idiot as far as excel is concerned. SO any idea or any sort of help as to how this can be achieved will help.

The sheet must be automated as far as possible and there are about 25 to 30 projects which need to be updated into the sheet before the final output.

Thanks in advance.
 

Answer:Solved: Formula to find a date and enter schedule in a specific cell in Excel

13 more replies
Relevance 77.9%

Hello,

In Microsoft Excel 2003, I have a column that has dates listed in this format: '01.01.2008', rather than, say, '01/01/2008'. This format is a requirement.

In the column that contains these dates, I have set each cell to have a date format to them as well as a custom setting that replaced the "/" with a ".". The problem, however, is that it can now no longer sort the dates correctly.

Is anyone able to tell me how I can modify or correct this so that I can have the dates listed in the '01.01.2008' format and still sort them as I normally would?

Thank you in advance!
 

Answer:Solved: Sorting Date Range In Microsoft Excel With &amp;amp;quot;.&amp;amp;quot; Seperator

8 more replies
Relevance 77.08%

I'm trying to find a macro which will automatically sort alphabetically by a specific range.

After I filter column A (for a specific reoccurring value) I want the remaining values in columns AC-AH to sort alphabetically by column AD automatically.

I've looked all over the internet but cant seem to find one applicable. I know how to run them but no idea how to create on myself!

Any suggestions?
 

More replies
Relevance 75.85%

I am trying to create a formula using an IF statement that would go of a date. If this date falls between the first half of the month (1-15) then it would calculate storage price off of one rate, and if this date falls between the second half of the month (16-30/31) then it would calculate storage price off of another rate. If this possible?

More replies
Relevance 75.03%

Hi. Im a bit stuck on how to attack this problem in vba and im sure someone has a basic solution for this:

In Sheet1, I have A1: Start Date and A2: End Date
In Sheet2, is the look up sheet. Its sorted by ColumnA:
ColumnA.......ColumnB.....ColumnC...ColumnD
01/02/2008...James........20...........$200
05/02/2008...Jones .......10...........$800
06/02/2008...Mary.........30...........$900
06/02/2008...Bob...........50...........$600
07/02/2008...Jason........10...........$500
08/02/2008...Jackie........20...........$400
etc
etc

I wanted to create a button in Sheet1 that will copy the entire row of data in Sheet2 but only within the date range declared in Sheet1. and place the copied row in Sheet1 starting at position A100.

Eg.
Start Date: 05/02/2008
End Date: 07/02/2008
Click button(vba executes)
Copy all rows from lookup sheet2 within these dates and paste it in Sheet1 A100.

Any solution will be very much appreciated.
 

Answer:Copy rows within date range - EXCEL

Dear Miage
Peace be upon you
What if there is 10 rows (or more) matches the criteria should all rows been copied from A100 to A140??!!!
I think this will be annoying.
Instead I think it is better to
make a category for each row in Sheet2 depending on the date range in Sheet1. (Using Vlookup function, in access 2007 only)
Give the category a name that could be understood (From010102To150302)
apply a filter on Sheet2 so that you can see instantly all the records you want within the range.
If you want more details I am ready, but please post a sample file (just 4 records)
Mohammad ElNesr
 

1 more replies
Relevance 75.03%

I can trigger e-mail from a fixed date, which doesn't work if excel is not open on e.g. 60 days ahead of todays date. How can I trigger an e-mail ONCE, if a workbook is opened at any time between today and 60 days?Many thanks, Mike

More replies
Relevance 75.03%

I am trying to find out the correct way to write a formula in Excel. We are trying to first determine if something falls into a date range, such as between 6/1/2011 and 6/15/2011 in row D. If something does fall into that date range, then we want to add up the corresponding amounts from row E to be summed together for us. Just to give you the correct ranges, we are looking at the dates from D5:D700 and the number amounts from E5:E700. Can someone please help me out? Thanks so much!

Answer:Correct Formula for Date Range for Excel

re: "row D, row E"Just to avoid any confusion, D & E are Columns, not rows.These formulas should get you what you are looking for. Note that these formulas will include 6/1 and 6/15. If by "between 6/1/2011 and 6/15/2011" you mean that you don't want to include 6/1 and 6/15, drop the "equal signs" before both DATEVALUE functions.=SUMPRODUCT((D5:D700>=DATEVALUE("06/01/2011"))*(D5:D700<=DATEVALUE("06/15/2011")),E5:E700)=SUMIF(D5:D700,">="&DATEVALUE("6/1/2011"),E5:E700) - SUMIF(D5:D700,">="&DATEVALUE("6/15/2011"),E5:E700)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 75.03%

Hi All,
Is there a way to use the .Find method (or other) to find each date within a range (eg "1/1/2012" to "2/2/2012")?
 

Answer:Searching for date range using Excel 2003 vba

Can you explain in real life terms what exactly you are trying to accomplish. A sample workbook would also be helpful.

Rollin
 

3 more replies
Relevance 75.03%

Hey guys,I'm working on an Excel Spreadsheet that maps out due dates.You enter the date a document was submitted and it fills out the course that a user has to follow for the remaining timeline of that document.With the due dates, I want to color-code warning dates, and past-due dates.Example:01/01/2012 - Document submittedExcel will show the submission and the due date01/10/2012 - Follow-Up dueSo let's say the C5 is the user input box for the date.C11 is the box that declares the due date for follow-up.C12 is a reference box as to the "status" of it's follow up.I want to box C12 change colors accordingly;Light Yellow - 5 to 1 days before dueYellow (darker) - on due dateRed - any date after dueI used the following conditional formats:Red =C11<TODAY()Yellow (darker) =C11=TODAY()These both worked out fine for me and do what I wanted.I am lost on the light yellow variation though.None of the formulas I tried for the TODAY() function and the date range have worked.Any help if appreciated, Thanks.

Answer:Today Function and Date Range in Excel

How about this:=AND(C11>TODAY(),C11<TODAY()+6)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 74.62%

Hi,

Starting from row 4:
I need the code to copy down the first name,
then starting from column g to cd in that row, it should copy down the mark for a subject (if there is a mark under that subject heading), otherwise skip that that subject.
also copy down that subject's, subject code, which is in the second worksheet.

In summary, If mark under subject for that person, look at subject, compare against code list, copy subject's code, then copy the mark.

Copy down any comment for that person.
Further details in the attached file.

Maybe a loop function or something?
Im very unfamiliar with VB so all i can really do is copy and paste code.

Any help at all would be appreciated.

Thankyou!!

UPDATED

NOTE: I have also cross-posted in another forum, so any updates should also be checked there:

http://www.excelforum.com/excel-pro...-within-a-specific-range-to-notepad-file.html

http://www.mrexcel.com/forum/showthread.php?p=1942476#post1942476

Cheers

 

Answer:Excel - How to export only non-empty cells within a specific range to notepad file?

6 more replies
Relevance 74.62%

Hello... I'm trying to count the total of all even numbers between the given range in Java.

I'm using a for loop, but am not getting the desired output.

// Code here

import java.util.Scanner;
public class SumEveryOtherNum {

public static void main (String[] args){

int num, sum = 0;
Scanner scan = new Scanner (System.in);
System.out.println ("Enter a number greater than 1: ");
num = scan.nextInt();

for (int count = 2; count <= num; count+=2){
sum+=count;
if (num < 1)
System.out.println ("Number entered not valid. Try again");
else
System.out.println("All even numbers added =" + (sum));
}

}

}

/*

If i enter the value 10 at the prompt, my output looks like this.
For some reason i figured "sum+=count" would add each number looped, and just output 30.
It prints this output instead:

Enter a number greater than 1:
10

output:
All even numbers added =2
All even numbers added =6
All even numbers added =12
All even numbers added =20
All even numbers added =30

Im trying to count the values, and for the result to only print the value of 30 such as:

All even numbers added = 30
Thanks for any ideas !

*/
 

Answer:Solved: Java - for loop count range problem

import java.util.Scanner;
public class AnotherTest {
public static void main(String[] args) {

Scanner scan = new Scanner (System.in);

int sum = 0;
int upperLimit;
System.out.println("Enter an integer (Output will add numbers in between 2 and upperLimit): ");
upperLimit = scan.nextInt();

for (int current = 2; current <= upperLimit; current += 2)
sum += current;

System.out.println("Sum of even numbers between 2 and " + upperLimit + " inclusive is: " + sum);
// TODO Auto-generated method stub

}

}

// removed the if else statements from prev version
 

1 more replies
Relevance 74.21%

I need help with a formula. I have monthly invoicing for contract work. The contracts can be 1 - 5 yr. Each year the contract price changes.

I want to be able to invoice on a certain date and have the contract price automatically put into the invoice based on the contract priced date the invoice falls within.

An example:
If invoice date (E12) falls:
Between date range H20-I20, place J20 (Amount) into D21 (amt) cell.
or
Between date range H21-I21, place J21 (Amount) into D21 (amt) cell.
or
Between date range H22-I22, place J22 (Amount) into D21 (amt) cell.

This would go on for a total of five options all based on the invoice date falling within one of the ranges.
 

Answer:Excel Formula for multiple date range options

7 more replies
Relevance 74.21%

I really need help!!! lol... I'm trying to have the document update from a master sheet which I managed to do via a VLOOKUP, but when the reference date on the document is updated, and I want it only to reflect the past 5-days, rather than the whole amount of information. So at all times it will show the last 5 work days and shift the cells automatically accordingly.

So basically the dates will constantly switch back. So that way tomorrow when I have the results of Sept. 29th, Sept. 22 will no longer be depicted, and the 23rd will take it's place; the 24th will take the 23rd's place, and so on. So it would look something like this...

**5 columns each date with it's own column
****After each day, there will only be 5 columns reflecting the last 5-workdays!

Sept. 22 Sept. 23 Sept. 24 Sept. 25 Sept. 26
 

More replies
Relevance 74.21%

Hi,
First, I'd like to say thank you for providing this great service.
I have a HP Probook, running XP. I have a project spreadsheet, gantt style, to track ongoing projects. I am tracking the start date, the due date and the completed date. I have a conditional format set to highlight a range of dates, yellow, on a calendar from start date to due date. The formula is: =AND(I$5>=$D6,I$5<=$E6). I would like to have the yellow blocks change to green when the project is completed. The date completed would be in cell F6. I would also like to have the same range turn red if the project runs pass the due date. The due date is cell E6. I5 is the first cell in the calendar row that runs across the spreadsheet. Any suggestions would be appreciated.

Thanks again
Tom
 

Answer:Excel 2007 Conditional Formating Date Range

9 more replies
Relevance 74.21%

I have a simple spreadsheet TBH for which two columns of data have date fields:

End date and Reminder date

I would like to set a conditional format using the icon sets for the reminder date as follows:

1) If the reminder date is less than 90 but greater than 60 days of the end date then show me a green icon

2) If the reminder date is less than 60 but greater than 30 days of the end date then show me a amber icon

2) If the reminder date is 30 days or less than end date then show me a red icon

I have come across ways of using today and dates in advance but not before...please can someone help me.?
 

Answer:Conditional Formatting for a date range in Excel 2010

Hi

To use the icon set you'll need to calculate the differences in a new column in the spreadsheet and then apply the conditional formatting to the values in that new column. So if your Due dates are a Col A and your reminder dates are in Col B in Col C use =A2-B2 and drag this down to the end of the list. Then apply the conditional format to Col C.

Here is an example spreadsheet.
 

1 more replies
Relevance 73.8%

This is a bit tricky to explain:

I have dates along the X axis and quantities up the Y axis.

When I enter a quantity on a date I need a formula to calculate
a) 3 times the entered quantity and then again 6 times the entered value (easy)
b) for these two values to automatically land on the 6th and 12th month from the date of entry (not so easy)

I need help with (b)

I'm picturing this as an ongoing line graph with new series being added periodically.

Is this possible or am I making this unnecessarily difficult for myself?

Thanks

Corinne
 

More replies
Relevance 73.39%

Is there a way to use a combination of SUMIF and VLOOKUP on more than one sheet?
For example if I want to feed some data into another workbook – is there any way to do this from multiple sheets to one cell/column in a third and separate workbook?
Ie:
To be more clear - I want data to feed into column C or d or f, etc. of sheet 1 from both sheets 2 and 3. Sheet one is in another workbook than 2 and 3. Additionally, I want this data to be from the first to the last day of a month. So that my data from different biweekly paysheets is automatically entered into the monthly report sheet (sheet 1).

 

Answer:Feeding data from separate workbooks in excel - within a date range

Hi, welcome to the Forums.

I guess your post remained ignored so long because noone really understands the question.
I certainly don't. Can you tell it in other words? You should have an example workbook attached as well.

Attaching files to your post can be done with the Manage Attachments option, below the post editor window.

Jimmy
 

1 more replies
Relevance 72.98%

Can anyone help me please?I'm trying to get excel to send an automated email from outlook when a due date comes about in certain cells Desperately seeking help on this Thanks

Answer:Automated emails when cells in excel are at specific date?

Try here:http://www.rondebruin.nl/win/sectio...should give you a starting point.MIKEhttp://www.skeptic.com/

4 more replies
Relevance 72.57%

Our employees can sell such things as vacation time, for example, 1 day of vacation pay but their request must be submitted by a certain time if they are to be paid. We can tell them what day they will be paid based on the date they submit their request. This can be confusing for a supervisor to explain and I wanted to automate it on an Excel sheet.
Example:
1. Employees receive their regular check every Friday as well as a check for any time sold.
2. As long as they get it into us within 7 days prior to Friday they will receive their check the next Friday.
3. If it’s greater than 7 days then it would be the Friday after next before they received the sold time check.
4. For example If an employee requested on May, 12th 13th, 14th, 15th, 16th, or 17th to sell vacation time that check would be received by the next Friday i.e. May 25th. However, if they turned it in on May 18th. 19th. 20th, 21st, 22nd., 23nd, or 24th. they would receive it on June 1st.
5. This pattern continues throughout the year. Note that weekends are included.
6. I think the attached will better explain what I am trying to do.
 

Answer:Formula in Excel for a Date to Remain Constant Based on a Range of Dates

I think there is something wrong in the way you explain it but, could be my interpretation though
I adden a column using the Days360() function which shows the nr of days between the request date and the 'friday' date, maybe you can use that resulty to compute the new value.
 

2 more replies
Relevance 72.57%

Hi, I was kindly helped my AJ on this site to end up with

=SUMPRODUCT(((A7:A18)>=A6)*((A7:A18)<=B6)*(B7:B18))

for the folowing:
2 colums, 'A' with the year (currently as plain number) 'B' with a simple number. There are roughtly 5/8 entries for each year, from 1989 to 2008. I need to sum 'b' for a range of years, (say 1990 to 1995), I can do it for one year value but not more.

but the further problem I have is:
I have a list of 160 rows with the year being the key data field and selecting various ranges say 1995 to 2006.

I need to capture the data in a way that I need to keep historic info for years gone (2006,2007) but then alter the sheet (remove a row if no longer relevant) for current and future years.

I was thinking of having a final column with a "year removed field" and the main formula above being dependant on that for inclusion, make sense?
Many thanks
 

Answer:Excel 2003 - Selecting data within a date range with a further logic field?

8 more replies
Relevance 69.29%

ok heres another one and Im a bit stumped I have a working macro to Find todays date in column A copy and paste it to a second sheet (Todays In) starting at Row 3. the macro moves row by row at the source sheet and pastes row by row at the destination sheet.

here it is

Sub FinalizeReception()
Dim i As Long
Dim iRow As Long
Dim PasteRow As Range
Sheets("Todays In").Visible = True
Sheets("Receiving").Visible = True
Sheets("Todays In").Select
Rows("3:53").Select
Selection.ClearContents
Range("A1").Select
Sheets("Receiving").Select
For i = 5 To Range("A65536").End(xlUp).Row
If Cells(i, "A").Value = Date Then
Cells(i, "A").EntireRow.Copy
Sheets("Todays In").Range("A" & iRow + 3).PasteSpecial
iRow = iRow + 1

End If
Next i
Sheets("Receiving").Select
Sheets("Receiving").Visible = False
Sheets("Todays In").Visible = False

End Sub

Now this is hack and slash programing here I understand verry little of this macro.
here is my delema Ive taken This code and tried to modifiy it to another source sheet. pasting in the same destination sheet but starting at row 56
in order to do this i simply put Sheets("Todays In").Range("A" & iRow + 56).PasteSpecial in a different moduel for a diffirent type of receiveing.

The first time I did this it worked great its where I got the idea in the first place but only in the first modul... Read more

Answer:Solved: Need help Selecting a specific range to paste in

7 more replies
Relevance 68.47%

I have a user form with two combo boxes, an add button and a close button. I would like the users choices from the combo boxes to be added to the worksheet "ReqM" starting with the first blank row on or after row 85, the 2nd choice on row 86 and so on until the users clicks the close button. The following code currently adds the data to the last blank row in the worksheet.

I appreciate any help:

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Req M")

'Need help with this section:

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
lPart = Me.cboEmployee.ListIndex

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboEmployee.Value

.Cells(lRow, 3).Value = Me.cboPosition.Value
End With

'clear the data
Me.cboEmployee.Value = ""
Me.cboPosition.Value = ""
Me.cboEmployee.SetFocus

End Sub
 

Answer:Solved: Add data from a UserFrom to the first empty row in a specific range

15 more replies
Relevance 68.06%

hey,
in column A i have dates when event occured dd/mm/yyyy
column B has Y yes N no P pending

my question is how do i count how many Y's occur in say november

look forward to the answer
 

Answer:Solved: Excel how to count

Data-->Filter-> Choose Y
Enter Formula =SUBTOTAL(3,B1:B100)

The subtotal function will work on the visible cells in a filtered list.
 

2 more replies
Relevance 67.65%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 67.24%

We recently migrated from Office 2003 to Office 2010.

Just to give some background, (and I dont' know if this is relevant to this error), the Office 2003 excel did not accurately show the correct file paths where files were linked. It would show something in the C drive.

The issue I am concerned about today is this: when some users close files in 2010 (files were created in they sometimes get the error below:

'"The name ABE2, either conflicts with a valid range reference or is invalid for Excel. This name has been replaced with _ABE2"

Can someone assist me with this? I need to stop it from appearing and I am not sure how.
 

Answer:Solved: Excel 2010 v Excel 2003 Error Valid Range

14 more replies
Relevance 67.24%

I'm running XP Pro ver 2002 SP2.
I see no option or way to search for files in an exact date range. Just one day, month, year ....
Is that a "feature" of XP - losing the ability to specify exact dates in a search, or is there a way?
 

Answer:Solved: XP Pro Search for Exact Date Range?

Open Search > All files and folders. Click "When was it modified?". There is a radio button for Specify dates.
 

2 more replies
Relevance 67.24%

Can someone help please.

I need to extract data from a list using a date range - weekly/monthly. I’ve tried using advanced filter but it doesn’t seem to work so obviously I’ve missed some vital step. Ultimately though I really would like to attach this task to a macro button .

So I would like to put the date range in the "addjob" sheet, extracting the data from "Data" sheet and putting the result in "InvoiceData" - can this be done?

I’ve attached an example file in the format it will be in.

Hope someone can assist……..

Moll
 

Answer:Solved: Extract data by date range

11 more replies
Relevance 67.24%

I'm trying to open a file in excel that has over 20,000 records, but excel seems to read only 16,384 and then appears to lose the rest. Anybody have any ideas?
 

Answer:Solved: Excel Record count

8 more replies
Relevance 67.24%

Afternoon,

I haven't used Excel for ages and I require a formula which counts from 2 collumns. I have one collumn for Open and Closed. THe other collumn has 3 types of fruit. Apples, Pears and Bananas. I want the formula to count between the two collumns and tell me how many apples I have in an open status and how many in a closed status.

Thanks for any help!
 

Answer:Solved: Excel - count formula

you can use
=SUM((B2:B11="apples")*(A2:A11="open"))
=SUM((B2:B11="apples")*(A2:A11="closed"))
and then set to an array formula by using
control + Shift + Enter
which will put { brackets around the formula
or if you want to use a reference to the summary sheet cells
=SUM((B2:B11=$A$15)*(A2:A11=A16))
and copy down

see attached
 

3 more replies
Relevance 66.42%

I'm creating a report based on queries that will list, say, total hours per consultant. To get a timeframe from the user, I made an unbound form, and put
Code:
Between [Forms]![frmDates]![cboStartDate] And [Forms]![frmDates]![cboEndDate]
in the query criteria so that it prompts them for the date range. So far so good.

However, my issue is that I'd like to add the results of more than one query to a single report, and only have the user prompted one time for the date range. So one rpt would have hours per consultant as well as hours per project, etc.

I would think that if the above-listed code were in the criteria of both queries, it would prompt for the date range twice, but when I try to build a report from more than one query, it says I can't do it anyway, giving me the error:

"You've chosen fields from record sources wizard cannot connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."

However, the sources are from two queries, so...
 

Answer:Solved: Access 2003 report w/ date range

7 more replies
Relevance 66.42%

I am trying to automate a Summary Report by creating a formula that Counts the number of Items across (3) categories.

The complication I cannot get past is that one of the three categories has a Non-Standard range of values. I created a lookup table to try to make the formula easier - still cannot figure out a Formula to work(may require a function?).

I am trying to aviod a macro that physically seperates the source data into (4) seperate spreadsheets, which then can then have hard-coded formulas based on the four spreadsheets for each Company (CoA, CoB, both CoA & CoB, Neither CoA or CoB).

I wish Excel 2003 supported CountIFS(), but even with Excel 2007, I still cannot figure out how to include the complex Range. Ultimately I want a solution that is compatible with Excel 2003.

Source Data: Sheet 1

Lookup Table(for criteria with Non-Standard range of values): Sheet 2

Summary Report: Sheet 3

 

Answer:Solved: Excel 2003: Complex Count IF

9 more replies
Relevance 66.42%

On the attached spreadsheet, I need a total count of baselined desktops (minus the ones that have been deleted)...my problem is how do I get it to look at one record, mark that that agent had been a baselined one, but is now deleted, so don't count it. I can't just remove the deleted ones, cause I want my customer to know which agents have disappeared. Take a look at the formula in B2 for a general idea. That formula doesn't work cause it just adds everything together. I need it to be smart enough to Total the Baselined Agents and then Subtract the agents that are baselined and deleted per field. Any questions let me know. Look at B2 to get an idea and see if I'm on the right track.
 

Answer:Solved: Excel - Trying to count 2 columns (almost as one) to get data for a 3rd.

6 more replies
Relevance 66.42%

Hi

I have a spreadsheet with a column that has initials entered ie like AB AC AD etc

I sort by data in this column

Is ther a function that will count the entries IE

AB = 3
AC = 20
AD = 12

etc

I use Excel 2007

Thanks for your support

Brian Fowler
Dublin, Ireland
 

Answer:Solved: Excel : How to count entries in a column

7 more replies
Relevance 66.42%

Hi,

I need to provide a summary on a spreadsheet I am working on and want to do a count on how many cells meet 2 criterias.

If you look at the example I've added what I want to do for example is count up how many severity.code 3 (shown in column E) are "resolved" (shown in column D).

I tried and failed miserably at Sumproduct and I know countif is a no go. Can anyone help?

As it's for a customer and I need to add a summary it needs to be posh looking so I can't pivot table it.
Cheers

Gogs
 

Answer:Solved: Count only if multiple criteria met in Excel

9 more replies
Relevance 65.6%

I have an access report that I would like users to be able to enter a start and end date. The report will be filtered to display data that falls between the two dates.

I have already created a form with date fields. I'm having a hard time figuring out how to use the form to filter the report. I also would like the report to display all data if teh user decides not to enter any dates.

How can this be done?

Any help will be greatly appreciated.
 

Answer:Solved: Access 2007/Filter Report By Date Range

There are many ways to do it, but I would set the filter in the OpenReport method when the report is launched, and provide logic to either set the start and end dates to extreme values (like 1/1/1904 thru 1/1/2999), which programming purists would tell you to never do, or not set the filter at all if the user doesn't enter a date.

In the code for the button that launches the report:

dtStart = Nz(Me.StartDate,#1/1/1904#)
dtEnd = Nz(Me.EndDate,#1/1/2999#)
DoCmd.OpenReport "Report1", acViewReport, "", "[MyDate]>=#" & dtStart & "# And [MyDate]<= #" _
& dtEnd & "#", acNormal

--- OR ---

If Isnull(Me.StartDate) or Isnull(Me.EndDate) then
DoCmd.OpenReport "Report1", acViewReport, "", , acNormal
Else
DoCmd.OpenReport "Report1", acViewReport, "", "[MyDate]>=#" & Me.StartDate & "# And [MyDate]<= #" & Me.EndDate & "#", acNormal
End If

The tricky part is getting tripped up by not including the #, designating a date literal.

WearerOfManyHats
 

2 more replies
Relevance 65.6%

When I run a filter on my laptop Excel tells me in the lower left corner how many records are in the filter. My desktop doesn't do that. Does anyone know how to make Excel tell me how many records are in my filter?
 

Answer:Solved: Excel 2000 Filter Record Count

9 more replies
Relevance 65.6%

Okay - still very new to excel formulae so bear with me...

ColA.......Col B
Open..... 23
Closed... 7
Open......4
Open..... 8
Closed... 19

Okay let us assume in C1 I want a single formulae that wil:
a: count the rows that have 'Open' in column A
b: sum the corresponding values in column b

With this example, my visible value in C1 should be 35. I am hoping to not have to 'duplicate' data in another column/area and the sum that new column (such as copying only the 'B' values that have "Open" in the 'A' column and then running a sum off that new column....

I am sure there is an easy way to do this but I keep getting zeros. I have been trying this:
=SUM(IF(A2:A5000="*Open*",B2:B5000,0))...

I have one other formula in D1:
=COUNTIF(AF1:AF5000,"*Open*").. which displays 3....

Thanks in advance

Ric
 

Answer:Solved: Excel - count and sum separate column data

=SUMIF(A2:A5000,"*Open*",B2:B5000)
 

3 more replies
Relevance 65.6%

Apologies if variations on this have been asked many times before, but I've yet to find a workable answer.

I have a table with Job Numbers in column A and Type Codes in column B, among other things. A Job may have multiple lines, so multiple records will have the same Job Number. I need the pivot table to show a count of Job Numbers per Type Code.

So say I have one formula field in column C:
=A2&B2

and another in column D, (replicated down through the data):
=IF(COUNTIF(C$2:C2,C2)=1, A2,"")

This is perfect in the table, I have unique combinations of Job Number + Type Code returning the Job Number and non-unique values returning blanks.

However, in the pivot table, if I add in the "Unique Jobs" field as a Count summary, it counts the blanks as well as the non-blanks, as the cells still contain formulae.

How do I stop this miscount (ideally, without using code)?
Can I substitute the "" in the column D formula with something that won't count?
Thanks.
 

Answer:Solved: Excel pivot table count unique

you have this marked as solved - is that now the case
 

2 more replies
Relevance 65.6%

I'm new to Excel and hope you can help in finding a solution to my problem. Basically, I have a worksheet with a list of people's names in column A, from A1 to A50, with this list being the names of the people working on 50 different projects. I have a second worksheet with another list of names, also in column A from A1 to A100. This list is the names of people who have achieved a certain qualification.

I know some of the names from the first tab appear in the list of names in the second tab, and I'm wanting to count how many of the projects are being worked on by somebody who has the qualification. I assume I'd need to use the COUNTIF function but haven't been successful in getting anything to work. Any suggestions for a formula will be gratefully received.

Thanks
 

Answer:Solved: Excel 2003 COUNT/COUNTIF query

8 more replies
Relevance 65.19%

Hello,
Long time since I posted here.

Basically, I have an Access database which calculates the cost of jobs by multiplying the number of hours worked by the Labour Rate.
The labour rate changes over time, but we need to keep historical data - i.e. When the labour rate changes I can't just go and change it in the query, I have to amend the formula so it uses the correct labour rate for jobs done on certain dates.

At present, in my query (CALC) I'm using nested Iifs, as follows (note: dates are in UK format):

LabourCost: IIf([JobDate]>=#01/09/2005# And [JobDate]<#01/04/2007#,[HoursWorked]*9.52,IIf([JobDate]>=#01/04/2007# And [JobDate]<#01/06/2007#,[HoursWorked]*9.82,IIf([JobDate]>=#01/06/2007# And [JobDate]<#01/09/2007#,[HoursWorked]*9.85,IIf([JobDate]>=#01/09/2007# And [JobDate]<#01/01/2008#,[HoursWorked]*9.93,IIf([JobDate]>=#01/01/2008# And [JobDate]<#24/01/2008#,[HoursWorked]*11.62,IIf([JobDate]>=#24/01/2008# And [JobDate]<#01/04/2008#,[HoursWorked]*11.68,[HoursWorked]*11.73))))))

The liklihood is that these labour rate changes are going to become more frequent, so I want to scrap the Iifs and use a table instead.

I've already set up a (unlinked) table called LabourRateStd with the following fields:
ID (autonumber)
Date1 (Date)
Date2 (Date)
LabourRate (currency)

The dates and the values have been entered as per the Iif statement above. For the final Date 2, I've just entered a date way off in the future.

So, wha... Read more

Answer:Solved: Access query: check date against range in table and return value???

16 more replies
Relevance 64.78%

Searching for files modified in a specified date range (of the past 3 days) gets a search result list titled "date modified", and with the column heading "Date Modified" but the date listed against a file modified today is wrong. The date displayed matches the file creation date & is within the specified range ... but how do I know what the Search is doing?

Can't trust it... !

Answer:File search (by date range) displays incorrect Mod Date in results

That is very strange. Bump for a possible solution, since I can't think of one off the top of my head.

1 more replies
Relevance 64.78%

I need to set up a query that pulls information matching a certain date of the current month and year. I'm not sure about the syntax. Someone help please?
 

Answer:Solved: Query for specific date, current month and year.

16 more replies
Relevance 64.37%

Hi all,I wish to create a macro that when i click a button i can input what month i wish to print out data for. I have 7 columns (A-G).Date Day Activity Income 02/01/2012 Monday Work (8) £48.00 02/01/2012 HMOT £5.00 02/01/2012 Peter Taylor 134024855 HMOT £5.00 02/01/2012 Susan Taylor 134023422 HMOT £5.00 02/01/2012 Steve Davy 13402737404/01/2012 Wednesday Work (8) £48.00 04/01/2012 HMOT £5.00 04/01/2012 Tom Thompson 134027410 HMOT £5.00 04/01/2012 John Ackroyd 134025131 HMOT £5.00 04/01/2012 Aftab Ilahi 134022546Above is an example of the sheet.In column A we have the date, B we have the day, C we have the activity, D we have the income, E is a cell with the previous month date in to track data for the month, F is a name and G is a number.I wish to be able to print all of january for example, rows A-G by clicking a button and entering something like 'january' or '01/12'. This will then select all of the rows with data in based on the date range in column A and print the selection.Please feel free if that doesn't make sense to ask questions.Thanks in advance.

Answer:acro to input date and print based on date range

Below is the code I have for a macro which I can't seem to get to work right.Thanks.
Public Sub PrintMonth()
On Error GoTo Proc_Error

Dim wksCurr As Worksheet
Dim rngTarget As Range

Dim strInput As String
Dim datInput As Date
Dim datStart As Date
Dim datEnd As Date

strInput = InputBox("input start date for month", "Enter date")
If strInput > "" Then
datInput = CDate(strInput)
If datInput > 0 Then
Set wksCurr = ActiveSheet
Set rngTarget = wksCurr.Columns(4)
datStart = WorksheetFunction.EoMonth(datInput, -1) + 1
datEnd = WorksheetFunction.EoMonth(datInput, 0) + 1
With wksCurr
If .AutoFilterMode Then
.AutoFilterMode = False
End If
.UsedRange.AutoFilter Field:=rngTarget.Column, Criteria1:=">=" & datStart, _
Operator:=xlAnd, Criteria2:="<" & datEnd
.PrintOut
.AutoFilterMode = False
End With

Set rngTarget = Nothing
Set rngDate = Nothing
Set wksCurr = Nothing
End If
End If

Proc_Exit:

Exit Sub

Proc_Error:

Select Case Err
Case Else
MsgBox "Error " & CStr(Err) & ": " & Err.Description
Resume Proc_Exit
End Select

Exit Sub

End Sub

3 more replies
Relevance 63.55%

I have an excel worksheet set up for a group of students to track absences. My columns are Student Name, # of absences, # lateness'. Each day I sort in the days absences. After the sort any student that was absent that day will have two consecutive records. What I do now is go through the worksheet manually (400 records) and if the absence code that is on the days absence record is of a certain value, I will add 1 to the # of absences column for that student. This works ok and doesn't take too much time but I know there has to be a way to do it automatically. I have no problem will basic formulas but this has me stumped.

Answer:add to a count based on a range of values

if the absence code that is on the days absence record is of a certain valueSorry, don't follow what your trying to do.Post a small sample of your work sheet, with a before & after if possible.First read this:http://www.computing.net/howtos/sho...It will show you how to post in these forums using the PRE tags.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 63.14%

I have a simple spreadsheet which tracks the cleaning of equipment. One column asks if they have deep cleaned the equipment or not.
The equipment only has to be deep cleaned every third clean but they might deep clean it before that.
The column has data validation applied so that the answer can only be yes or no

How do I count the amount of no's in a column from the last yes?
 

More replies
Relevance 63.14%

Hi All,

I m using Java and Cassandra database and i want to find the count of rows of one column family that will satisfy some specific condition.i m able to fine the count of rows of one column family without applying any condition but i m not getting how to get the count of rows of one column family by applying some condition.
Thanks in advance.
Saggy
 

More replies
Relevance 63.14%

I have a range of cells where I need to count the number of times that a number is not present. I need to do the same for text.

Answer:count the absence of nmber in range of cells

First, it comes across much friendlier if you actually ask for help instead of just telling us what you need.Second, the answer to your question is 1.If a value is not present, it can only be not present once.How could something not be there multiple times?

11 more replies
Relevance 62.73%

The title isn't very good; sorry about that.

I'm looking for a non-VBA method to solve the following problem.

Given a range of values, for which no cell is required, is there a way to determine which cell has the first datum, and which cell has the last datum?

Say the range is cols B:O. Some rows have valid data in B; some rows have no data until E. Is there a worksheet function that supports this kind of thing? Does INDEX do it? My Excel is pretty weak

Thanks for any ideas.

chris.
 

Answer:Solved: First/Last across range in Excel

14 more replies
Relevance 62.73%

Hi All,
I am using LUMIA 1320 that has Lumia Cyan.
Sometimes the time and date is getting reset to a specific date, i.e. 22nd May 2014
Due to this all reminder, alarms and new messages are not coming in sync.
Is this a virus? Does it have come from an app. If that is the case then it questions Microsoft App policies...
Anyone who has encountered this issue and has done something to eradicate it let me know.

Answer:Why is my phones time and date getting reset to a specific date?

First, windows phone can never get a virus. Its the safest platform after iOS . And no, a particular app can't affect the system's time and date. I think your should try to manually set the time and date in the settings. And see if it works.

3 more replies
Relevance 62.32%

I am trying to write a macro which moves down a worksheet with a row-incrementing index (RIndex). Every time I want to go to a new row, the index goes up by one.

I want to be able to copy the data from the first eight cells in the previous row to the corresponding cells in the row I’m working on. How do I reference the range (cells A through H of the previous row) I want to copy using the RIndex number?

I have tried many variations for example Range(Rindex, 1:Rindex,8), Range(“Rindex, 1:Rindex,8”), Range(“Rindex, 1”:”Rindex,8”), and others but I am getting nowhere.
 

Answer:Solved: Incremented Excel Range

Here is something to get you started. It will select the the cells in columns A:H of the previous row of data. You can replace the ActiveCell.Row with your variable. Then just add the code to copy and paste to your new location.
Code:
Range("A" & ActiveCell.Row - 1, "H" & ActiveCell.Row - 1).Select
If you want to copy straight to your new location you can use the code below.
Code:
Range("A" & ActiveCell.Row - 1, "H" & ActiveCell.Row - 1).Copy Destination:= _
Range("A" & ActiveCell.Row, "H" & ActiveCell.Row)
Regards,
Rollin
 

2 more replies
Relevance 62.32%

Has anyone any idea if it is possible in EXCEL to return , say , the letter "X" , if a range of values has an individual value of ZERO in it , or if ALL the numbers are greater than zero , then to sum the range ?

Alternatively , if a range of values has , say , the letter "x" as one of the entries , to display say , the letter "Y" otherwise perform a summation of the values ?
 

Answer:Solved: EXCEL : A range with a TEXT in it or

13 more replies
Relevance 62.32%

I need to compare 2 ranges to ensure they are referring to the same item.

Code:

dim cell1 as range
dim cell2 as range
set cell1 = sheet1.range("A1")
set cell2 = sheet1.range("A1")
msgbox cell1 is cell2

The above msgbox is supposed to return true, but it didn't.
Any other alternative?
 

Answer:Solved: Excel Range compare

9 more replies
Relevance 61.91%

can anyone help me to do that program..

example i enter a month, and i enter a date, then i enter a year,

next i enter another month, date and year.

i need to count of how many year, date and months between the 2 example

can anyone help me?
 

Answer:how can i count date in C++

http://www.codeproject.com/KB/datetime/datetimedisc.aspx

Should help ya.
 

1 more replies
Relevance 61.5%

Need a formula that does the following.if a2:a50 = food then return the value of the cell in the same row.example if the formula searchs and finds a15 equals food then the formula would return f15's value of "good". Any ideasThanksmessage edited by soul_dad_5

More replies
Relevance 61.5%

Hello everyone, I have a problem that I am trying to work on and could use someone with more experience with Excel formulas for this, this is my first post here and I have been really impressed with the solutions I have seen at this forum so thought I would submit my dilemma for guidance. Here it is:

The attached excel file is what I would like to use as my working example and basically I would like a formula which would then seek out at COLUMN C3:C11 all items that meet the statement if =>100000 (but then not <200000 take all the corresponding values in COLUMN I that meet the preceeding criteria and create a subtotal of all items in the formula cell. (I am not clear at all on how to write that formula)

So for example that my accounts 100100, 100200, 100300 would return a subtotal value of 5,771,718.37 etc.

If any of you geniuses out there can help me it would be greatly appreciated, I have spent alot of time trying to get it but without much success.

Thanks,

Stevie
 

Answer:Solved: Excel - Lookup / Range Formula Help

7 more replies
Relevance 61.5%

I have several Excel 2010 workbooks that each have a cell of the same name, e.g., penicillin. I would like code that references that particular cell/range name no matter which workbook is open so that I can use the same code to perform a specific task. I'm thinking that the code would be something like:

Dim WorkbookName
WorkbookName = ThisWorkbook.Name

If Range("WorkbookName!penicillin") = True Then Range("DrugIndex") = 1

The code works if the actual name of the workbook is used (instead of WorkbookName) in the first half of the 'IF' statement so I think that my syntax is not correct when generically refering to the open workbook.

Again, your expertise is much appreciated.
 

Answer:Solved: Retrieve a cell/range name using VBA in Excel

7 more replies
Relevance 61.5%

Hi there

I'm stuck on a single line of code. What I want is the correct syntax for the following:

If Range("F16") does not have a formula then

Code goes here

If Range("F16") has a formula then

Code goes here
By not having a formula I mean anything in that cell other than say a formula. It could be numbers or text. By a formula, I mean a valid formula beginning with an equal sign such as =A1*5+A2*9 or any other type of formula. I could be =Sheet2!A1.

Mario
 

Answer:Solved: Excel VBA - syntax for range has formula

Code:
If Range("F16").HasFormula = True Then
'Your Code if true
Else
'Your Code if false
End If
Rollin
 

2 more replies
Relevance 61.5%

I have been using a spreadsheet now for over a year to help me analyze the stock market. Once I got the bugs worked out it has worked perfectly up until I updated the company symbols a few days ago (new companies go public, others go bankrupt, mergers, go private, etc.).
There are several sheets to this workbook but I need to only discuss two, Evaluate and Data. The Data sheet contains about 7,000 companies, their symbol, name, price, P/E, and on and on, over 68 columns of data per company, most of which I use. The Data sheet is updated almost daily, is alphabetized by symbol, which is used by VLOOKUP on the Evaluate sheet.
On the Evaluate sheet I am using =IF($B6531="","",VLOOKUP($B6531,Data,Q$7383,FALSE)). Column B on Evaluate is the company symbol, row 7383 contains a number that corresponds to the column number on Data for the appropriate value for that symbol, in this case the company name.
All works fine until it gets to row 6864 on the Data sheet, at which point it returns N/A to all the cells on Evaluate. First, it didn't used to do that; I have no idea why it suddenly started. I have updated the symbols on Evaluate before without any problems. I have found a solution, but before I use it I thought I'd ask the forum if there's a better solution. If I specify a range i.e., =IF($B6531="","",VLOOKUP($B6531,Data!A:BP,Q$7383,FALSE)), it works. So, the next question is why and the third question is should I use =I... Read more

Answer:Solved: excel vlookup with and w/o specifing a range

12 more replies
Relevance 61.5%

Hello

I have encountered a problem with what I thought would be a relatively simple vlookup.

I have attached a sample sheet with the problem I am having and will try to explain.....

In essence I have one sheet with a column of account numbers (eg 4000, 4001, 4002 etc) and another sheet showing how these are grouped together eg LOW:4000 HIGH: 4009 TITLE 4000 - 4009 Sales

My current formula does a vlookup for say acount number 4001 and returns the value of "TITLE"

The problem I have though is if one of the account numbers (4900 for example) is not shown on the LOW:HIGH Sheet then the current vlookup formula returns "TITLE" for the last true reference. I need it to identify that 4900 is not in any of the LOW:HIGH ranges

Hopefully quite simple to sort but it is causing me now end of headaches..

If anyone can help I would be very grateful

Thank you

Jeremy
 

Answer:Solved: Excel lookup if value not in LOW : HIGH Range

11 more replies
Relevance 61.09%

I resolved one problem this morning and ran into what is hopefully the final issue.

I have stations that range from 100+00 - 415+00. The top line for each station is an elevation. The bottom line beneath the elevation is an offset. I need to shift all the offsets of the number "0" in say column J, maintaining the format it is in now. Basically shifting each elevation and offset either left or right so the number 0 is in column J.

Thanks.
 

Answer:Solved: Excel: having several specific cells in one row

6 more replies
Relevance 61.09%

Hello! I appreciate any help with this Excel formula that has me stumped.

Is there a way to look down a range of cells in one sheet, and it a match is found, enter a value from a different range of cells?

See attached example.

In the second sheet, for cell B2 -- I want to look at column B in sheet 1 and if it finds the name Joe, I want it to put the associated value from column A (the result should be the word BLUE in the B2 cell on the second sheet).
 

Answer:Solved: Excel - find match, enter value from different range

7 more replies
Relevance 61.09%

Let me apologize up front for my ignorance because I only know enough about Excel to write basic formulas.

I'm working with Excel 2007 and I'm trying to build a reference sheet that I can use to populate an inventory list based on an assembly name. I've attached a sample of the data I want to retrieve. For instance, if I enter the assembly name S30-1H3F, I want it to copy all of the quantities from the matching row (i.e. D11:L11) on the reference sheet to the inventory sheet. I guess I essentially want it to copy and paste the values from one sheet to the other based on which assembly I enter.

Ultimately, there will be a hundred or so possible assemblies and don't want to enter the quantities every time.

Thanks.
 

Answer:Solved: Excel: A lookup that returns a range of values

7 more replies
Relevance 61.09%

Hi,

I have a workbook with 3 columns.

Column 1: Network address
Column 2: Subnet bits
Column 3 An IP address

For each row, need to check if the IP address in column 3 is within the IP range specified by columns 1 and 2.

If the IP address in column 3 falls within the range represented by values in column 1 and 3 need to display TRUE in column 4 of that row. Otherwise need to display FALSE.

Anyone who is able to do this using formulas only is a genius but VBA solutions are also welcome! Thanks!
 

Answer:Solved: Use Excel to check if an IP address is within a given range if addresses

I don't think this is quite so hard as you make out (for formulae), but anyone who wants to help will need more information. What format are you putting the addresses and subnet bits. Better still can you please post a sample spreadsheet?
 

3 more replies
Relevance 61.09%

I wonder if somebody has come accross a solution for the following:
I have a workbook with quite a lot of macro's and functions (vba).
To simplify matters I use named ranges for easy reference etc.

One of these functions adds a number of cell controls with certain sheets, but when I open another workbook, I need these options deleted since they will not function there.

This works perfectly if I change to a worksheet that does not need this within the same workbook, but of course, when I open another workbook the function will not recognize the named range and throws an error (I avoid these by usin the on error ...)

My question is, is there an option or way to hardcode the workkbook's name togehether with the named range when defineing it, so that when that particular range is referred to the code 'understands' where to look for it?

Something like "My other workbook"!Range("NAMEDRANGE")

If you use Refer.Name it only refers to the sheet but not the workbook.

Thanks in advance.
 

Answer:Solved: VBA: Excel Referring to a named range in another workbook

16 more replies
Relevance 61.09%

Hi

I need help from one of you excel gurus out there.

I will see if I can explain clearly what I am trying to achieve.
I would like a macro that searches a worksheet for all debit transactions and credit transactions seperately for a given date and then sums them up. For example if i had two debit transactions on the 17th March, the macro finds the range for all transactions on the 17th and sums up those debits. Again the same for credits.
Ok, here is where it gets trickier, I need it to do it for all days from 1/07 to 30/06. So it's searching to see if there are any transactions for any of those dates and then sums them up.

I appreciate any assistance

Thanks
Strybes
 

Answer:Solved: Excel 2003 Help with sum range of dates found

11 more replies
Relevance 61.09%

What is the best way to determine through code if a range is selected?
 

Answer:Solved: Excel Macro -> Determine if range is selected

For Example, I want to run an IF THEN statement only if specific cells are not the ones the value is being changed or added.

The specific cells do not go in sequence.
 

3 more replies
Relevance 61.09%

Hello everyone,

I am wondering if anyone one would be able to help me. I have only just started getting into advanced excel use of macros and formulas for Excel 07. I have done ok so far except for the following macros formula needed tp change the background colour of a range of cells with more then the three options that conditional formatting allows.
I have a column of cells from C6:C80 which I have made all drop down selections from a list of four text items:
ItemA
ItemB
ItemC
ItemD
I was trying to get a macro that would colour the cells in C6:C80 automatically on selection of the drop down list based upon the four different selections available. And another macro to colour that whole row that has data in it based upon that same selection. The reason why two different ones being that I am unsure of what will work the best.

I know this is on the easy side of macros, but I am hoping someone would be able to throw me a bone none the less

Thanks for any help all.

Ads
 

Answer:Solved: Excel 07 macro to colour cells in a range

Ok turns out I didn't need macro and it can all be done with conditional formatting using the "Highlight Cell Rules". I was under the impression that you could only use three conditions but that was incorrect. Thanks for the help from Sweep at another forum.
 

1 more replies
Relevance 61.09%

Hello I'm having a problem in create a excel macro, that multiply the values from a table(for example the range is (B5:L20)) by the values that are in the column (A5:A20). The result should be place in another sheet.

There is someone who can help me, please.

Regard
 

Answer:Solved: Excel Macro multiply range for a column

6 more replies
Relevance 60.68%

Hello,

I am having an issue with creating a Word 2010 formula that provides a count of the specific values found in the column of a table. The basic details are below.

The column may contain the following distinct values:
Off Duty
Limited Duty
Returned to Regular Work

I would like to create individual formulas (in three different cells) that count the number of times that each string occurs. For instance, the example output would be:

Off Duty: 5
Limited Duty: 7
Returned to Regular Work: 12

I understand how to handle this in Excel using the COUNTIF function. Unfortunately, I don't believe that Word has the same function.

I'm certain that it is easier than I'm making it...I appreciate any and all suggestions!
 

Answer:Count Specific Text Values in a Word Table Formula

I appreciate any and all suggestions!Click to expand...

Insert an Excel Worksheet Object?
 

1 more replies
Relevance 60.27%

Hi,Also, does anyone know how to count the number of cells that contain a particular date in excel, e.g. 31st Dec 2011 across a number of tabs in the same workbook?C.

Answer:Count the number of cells that have a date

Hold down the Shift key while right-clicking the tabs (worksheets) you want to search in (or, if you need to search in tabs that are not adjacent to one another hold, down the Ctrl key instead).Then press Ctrl+F to bring up the search applet. Type in the date (as it appears in the cells), & click on Find All. You'll get the total number of times it appears in all the tabs in the status bar at below left.Here's an example screenshot click here - I searched for something across 5 tabs & it turned out '283 cell(s) found' in the status bar. So the thing I was looking for appears 283 times. G

2 more replies
Relevance 60.27%

This is the formula I have used=SUMPRODUCT(('Engineer sheet'!$L3:$L$1005="ABC")*('Engineer sheet'!$H3:$H$1005="NORTH")*('Engineer sheet'!$G3:$G$1005="TABLE"))-COUNT('Engineer sheet'!O3:O1006)The forth array in Column O is a range of dates, some are blank cells.I am trying to total the number dates in in Column O based on the SUMPRODUCT from the previous 3 table arrays.I use Excel 2003Any help will be appricatedRegards Ken

Answer:i want to count the date in a forth array using sumproduct

re: "I am trying to total the number dates in in Column O based on the SUMPRODUCT from the previous 3 table arrays."I'm not sure what you mean by "trying to total the number dates".Are you trying to have the SUMPRODUCT only return a count of ABC, NORTH, TABLE if there is also a date in Column O?As written, your formula will first calculate the number of rows where ABC, NORTH, TABLE are all present and then subtract the number of cells in Column O that contain any value. There is no relationship between the SUMPRODUCT part of your formula and the COUNT part. They are really 2 independent calculations.What exactly are you trying to calculate?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 60.27%

I have a worksheet where I need a tally of who is doing what during a certain month.In this case, A is the date, B is the person, C-D-E are the types of tasks.So if A (3/2/17) is in March and B (Joe Blow) worked C (3 Triggers) and D (2 Events) andA (3/15/17) is in March and B (Joe Blow) worked C (4 Triggers) and D (6 Events) and E (1 New)then March's worksheet would show F (Joe Blow) G - total (16) with H - Triggers (7) and I - Events (8) and J - New (1)Does that make sense?

Answer:Only count cells if the date is March?

If I understand your post correctly, you have this:
A B C D E F G H I J
1 Date Name Triggers Events New Name Total Triggers Events New
2 3/2/17 Joe Blow 3 2 Joe Blow
3 3/15/17 Joe Blow 4 6 1Put this in H2 and drag it across to J:=SUMPRODUCT((MONTH($A$2:$A$3)=3)*($B$2:$B$3=$F$2)*(C2:C3))I'm sure that you can figure out the SUM formula for Column G. ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 60.27%

Hi all, looking for some assistance to save time with a task. I am required to send an email to email addresses in cell E if the cell value in d is greater than 0. Would appreciate assistance, thanks
 

Answer:Solved: Excel mail a row/rows to email addres in range

Hi

Attached is an example of how you would send emails automatically from your spreadsheet. If your "real" spreadsheet is in any way different from the example the macro will need to be adjusted, Also, I used column H to identify when an email has been sent and told the macro not to re-send to that address(es). If you do want to re-send you would need to delete the notation in column H.
 

3 more replies
Relevance 60.27%

I would like to know if anyone has a simple macro that returns a true if a selected range is not contiguous.

The idea is, I have a workbook with one sheet for every month where tasks, vacation etc are kept and planned.
You can select a range or several ranges (see attached bmp file) and usign the Right mouse button it permits the user to enter a 'V' for vacation in that selected range or ranges

What I am looking for now is if in case the range is not contiguos I want to be able to determine where each different range starts and ends (that I can do with some code I already have for a contiguous range) but now I would like the code to return the three anges

"A4:B5" "C6:E7" "F8:J10"

Then I execute the appropiate code to enter, format or whatever each cell in the separate ranges, the actions will be same but skippng the not selected cells.

Maybe something like not in selection?

It's difficult to attach the workbook I'm using since I don't have an empty one at hand right now, but I'm preparing one.

It's easier to prgram than to explain what I want.

Thanks in advance for any tip
 

Answer:Solved: Excel: need vba code to determine if selected range is contiguous

Just for the info, I FOUND IT!

I found the following code: it selects one cells of the cells in de selected non cntiguous range

Sub UnSelectActiveCell()
Dim Rng As Range
Dim FullRange As Range

If Selection.Cells.Count > 1 Then
For Each Rng In Selection.Cells
If Rng.Address <> ActiveCell.Address Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng

If FullRange.Cells.Count > 0 Then
FullRange.Select
End If
End If
End Sub

Now I added my code to loop until count < 1

Sub Unselectthem()
Do While Selection.cells.Count > 1
UnSelectActiveCell
Loop
Range("B5").Select
End Sub

and in the first one I will replace the red text with my code

If FullRange.Cells.Count > 0 Then
FullRange.Select <<< here my code to invoke the necessary module
End If

I just added this, maybe somebody else is / was looking for something similar.

Thanks anyway.
 

1 more replies
Relevance 60.27%

Hello guys, I just can't work this one out. A simple formula somewhere on the worksheet "=B1-B11" will give us the answer to the contents of B1 minus the contents of B11.

Here's where I am stuck. I want the answer to be B1 minus what is in the cell that is 10 cells down, in this case B11. But I want the be able to change how many cells it will go down to find the cell to subtract from cell B1.

In my example, if I put 10 in cell A1 how do I make so if I change it to 5, the above formula will effectively be "=B1-TheCellThatIsA5CellsDown", in this example the same answer as the formula will effectively be "=B1-B6" if even makes sense?

Thank you in advance.
Aussiemosis
 

Answer:Solved: Excel - Changing the range to include a certain number of cells

8 more replies