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.

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

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))

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!

Hi LucianC - try entering this info:

Start Date in A1

End Date in B1

=DateDif(A1,B1, "D") in C1

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

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 repliesI 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?

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!

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.

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

4 more repliesCould 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.

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

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

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

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?

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

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.

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.

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

10 more replies

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!

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 repliesHi

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

=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

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.

Select your data, then Format > Conditional Formatting.

Condition 1; Formula is:

=$A5<$D$2

Click Format > Patterns > red colour > OK.

Click OK.

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.

13 more replies

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!

8 more replies

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?

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

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

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 repliesI 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!

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 repliesHi 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")?

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

Rollin

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.

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 repliesHi,

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

6 more replies

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 !

*/

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

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.

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

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

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

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.

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

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

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

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

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

4 more repliesOur 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.

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.

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

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

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

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

Data-->Filter-> Choose Y

Enter Formula =SUBTOTAL(3,B1:B100)

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

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!

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.

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?

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

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

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?

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!

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

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

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

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.

6 more replies

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

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

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.

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

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?

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

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

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.

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

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

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

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

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

1 more repliesI 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?

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.

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

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.

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 repliesI 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?

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

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.

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

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.

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

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

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 ?

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?

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?

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

Should help ya.

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 repliesHello 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

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.

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

Code:

If Range("F16").HasFormula = True Then

'Your Code if true

Else

'Your Code if false

End If

Rollin

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

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

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.

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

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.

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!

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?

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.

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

What is the best way to determine through code if a 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.

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

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.

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

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!

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

Insert an Excel Worksheet Object?

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.

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 repliesThis 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

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 repliesI 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?

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.

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

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.

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

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.

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