Copying Into Another Row If Conditions Met
Dec 17, 2009
What I'd like to do is to create a loop that will run through the cells in column B and change the values in column A to the value in column B IFF the current cell in column B does not equal "#N/A" AND the cell above the currently selected cell in column A contains the text "Manhattan". The code should run through each row and the data from the cell in column B if the above conditions are met will be copied into the cell in column A on the same row;
View 4 Replies
ADVERTISEMENT
Apr 13, 2014
I am trying to copy data from one spreadsheet which is updated daily to a Master spreadsheet. But when the data is copied to the master spreadsheet it compares the data and if the data is missing from the daily sheet that row gets moved to a 'achieve' tab within the workbook.
Scenario: We get a daily spreadsheet with outstanding jobs this gets copied to the master spreadsheet. But when copying the data to the master spreadsheet it check to see if todays daily sheet matches the master sheet. If any data is missing this mean the job has been completed yesterday so the row needs to be moved to a tab called archive in the master spreadsheet.
trying to automate process: Copy data from daily spreadsheet to master spreadsheet -> vba/macro - compares data copied in to (master spreadsheet) see if any jobs are no long on the daily sheet = (jobs been completed) -> IF this is the case move row of data to achieve tab in the master spreadsheet.
View 1 Replies
View Related
Apr 16, 2008
I have found several forums with this question but none of them seem work/fit my needs
What I need to do is look in Sheet1 at column 'B' begining at row '15' (row 14 is table header) and if the cell contains "Yes" then copy 3 cell directly to the right (C15:E15 in the case of the first row) into Sheet2 into cells B15:D15 then do the same for row 16 and so on up to row 600
Creating a cut down copy of the Sheet1 which does not contain the row which determines if it should be copied.
View 9 Replies
View Related
Jun 25, 2008
I'm working with a DDE feed of stock quotes (much like this thread: http://www.mrexcel.com/forum/showthread.php?t=322427)
Ideally, I'd like a row of information to be cut & pasted as the values into another worksheet IF an objective condition is hit.
A very simple example: Let's assume I've got the following data:
Book1___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA2=ABCDEF1TimeStockBid VolBid PriceAsk PriceAsk Volume211:10:23MSFT3035.5535.5810311:10:23YHOO50029.8529.920Sheet3 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I'd like to be able to copy the entire line to a new worksheet IF the bid volume is above 400, every time it updates with new data.
Unfortunately, my VBA knowledge ends very shortly after recording a macro & making small tweaks, so I have no idea how I'd set something like this up.
View 9 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
May 15, 2014
I want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:
[Code] ......
The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?
If not, how to copy, then paste?
View 2 Replies
View Related
Dec 12, 2007
I am trying to make the cells Iam am copying paste hidden cells with all formats - seems to work fine other than the security part of them, Iam makeing a sheet for work and just trying to make it were it will not get destroyed by other users-
View 12 Replies
View Related
Apr 27, 2014
i have created a table. when i m editing new row then formulas is copying bu value is not copying ?
View 3 Replies
View Related
Aug 14, 2006
I want to calculate the sum of column with two conditions on different
worksheets,
Example
Data
Stock #QTYMonth
01335208Aug-06
01337402Aug-06
01337392Aug-06
01335400Sep-06
01337100Sep-06
01337102Sep-06
If the stock # is equal to the stock # and Month equal to month
I need to get the result in another sheet as below.
Stock #Aug-06Sep-06
01335208400
01337794202
View 13 Replies
View Related
Mar 13, 2008
what would be the most effective and easiest formula to use if I want to sum a range of numbers meeting 2 conditions (or more).. eg.. sum all the unpaid invoices (condition 1) due on March (condition 2).
View 9 Replies
View Related
Mar 6, 2013
I have the following code:
=SUMPRODUCT(--($A$2:$A$400="OKC"),--($C$2:$C$400="AF33"),$G$2:$G$400)
It works great, but I am needing to now add a 3rd condition, or filter... In addition to it looking for "OKC" and "AF33" I also need it to sort by month. example; "March". I need it to be in code that works on old versions of excel as well...
View 6 Replies
View Related
Feb 15, 2008
Although I am not getting any errors, I am not having success returning proper answers with the formula below. Perhaps I cannot do what I am attempting.
=SUMPRODUCT(IF(AND($K$22:$K$169="A&B",YEAR($G$22:$G$169)-YEAR('Info'!$F$9)=0),(MOD(ROW($R$22:$R$169),2)=0)*($R$22:$R$169),0))
where: K22:K169 are user entered categories from a drop down list
G22:G169 are user entered dates
F9 is a user entered update date
column R are monetary values with first row being a revenue$ second row margin$
I need to summarize every other row (one row is revenue, one is margin) if the two conditions are met.
View 9 Replies
View Related
Apr 28, 2006
I'm (and you are too!) still helping to streamline a charity's spreadsheet. There are two worksheets that this question refers to: "DD Tally" and "List of DD Donors" (where DD stands for Direct Debit). In the DD Tally worksheet we are trying to summarise the information contained within the List of DD Donors. We would like to be able to estimate how much, in direct debit donation, we will recieve in the financial year (currently, April 06 to March 07). We would like this to be seperated into our monthly, quarterly, semi-annual and annual donors.
The challenge is knowing how to search through a column to find the, for example, "Monthly" entries and once these have been found to find the quantity of the donation(s) in the rows with "Monthly" in them, and then, to determine the number of months they have been paying for during the financial year (their first payment dates are listed as 01/MM/YY) - i.e, the difference between the end of the financial year (03/07) and the start (04/06) or between the time they joined during the financial year and the end. Then to multiply the number of months the direct debit has been active during the year by the amount they donate.
View 6 Replies
View Related
Jun 25, 2006
I've been writing a piece of VBA code to process a timesheet for my office.
THe sub routine has a number of variables passed to it, two of which are called CFhrs and Limit. The first contains in integer representing a number of hours. THe latter is a number of hours that can be claimed. In short if CFhrs is less than Limit, the CFhrs should be left alone, but if CFhrs is more than limit then CFhrs should then equal Limit.
First I tried this:
If CFhrs = Limit Or CFhrs > Limit Then
CFhrs = Limit
CFmins = 0
End If
This seemed to be totally ignored when run, and even when CFhrs was greater than Limit, CFhrs was left just the same. So I tried:
CFhrs = IIf(CFhrs < Limit, CFhrs, Limit)
CFmins = IIf(CFhrs < Limit, CFmins, 0)
View 9 Replies
View Related
Jun 19, 2014
I am having trouble with formulas below, when i try to use AND, OR, i having trouble .
=IF(B1=SG,IF(A1="I",0,IF(A1="A",0,IF(A1="P",0,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=LQT,IF(A1="I",150,IF(A1="A",150,IF(A1="P",150,IF(A1="D",44,IF(A1="W",44,IF(A1="O",44,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=MLT,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=IBW,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",10+(C2*15%),IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=JL,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
View 8 Replies
View Related
Feb 15, 2014
I have a table(see attached). I want to sum the total hours worked in Col C. Col B has the number of people on the job (either 1 or 2). I need a condition, I guess , so that if Col b has a number 2 in it, then the adjacent cell needs to be multiplied by 2, then summed into the total hours worked.
I am not sure on how i would write a formula for this. Looking at the sample if I add the total hours from col C at the moment I get a total of 18 Hours but as cell C3 & C5 have a "2" in their adjacent column, the total hours answer should be 27 hours.
View 3 Replies
View Related
Mar 22, 2014
SUMPRODUCT(--(Table[[CAT]:[CAT]]=$C5);--(Month(Table[[DATE]:[DATE]])=G$3);--(Table[[IN]:[IN]]))
place the amount from table column "IN" into a corresponding cell in another sheet. how to add another column (OUT) in the above formula. I wish to summarize both column IN and OUT from table based on two conditions.
Workbook example: [URL]
View 3 Replies
View Related
Aug 13, 2007
I have a table (look at attached file for better info) with TEXT values on Column A (A1:A10) and NUMERICAL values on Column B (B1:B10).
I want to find the MAX value between a value that I set (in cell A14) and the last record (as well as the opposite, between the value I set and the first record).
What this means is that if I set for example D as my value (see attached file for better understanding) I want the formula to find the MAX value between B4:B10 (and another formula that finds the MAX value between B4:B1) based on these conditions
The first and last values (i.e B1,B10) are not included in the result.
The value I set is not included in the result as well.
What this means is that if I set the value D which can be located in cell A4, I want to find the MAX between cells B5:B9 (and with another similar formula the MAX value between cells B3:B1).
The value I set changes every time.
I have created a formula as you will see in the attached file but that works only if I create a copy column A to C and then again I can't find a way to exclude the value I set from my results, so if the value I am looking for happens to be the MAX I get that as a result.
View 14 Replies
View Related
Feb 2, 2009
I have been trying to create a report that involves three conditions, but so far I have had no luck using SUM and IF conditions to do this.
I have attached a file with an example of what I would need. Basically, I would need the "Resolved" and "In-Progress" quantities filled in below the "Country Report" for each respective country.
View 3 Replies
View Related
Mar 13, 2009
What formula could I use to return the following results.
1, 2, 3,4, 5, 6 = Manhattan
7 through 12 = Bronx
31 = Staten Island
View 2 Replies
View Related
Aug 3, 2009
I have a list of item numbers in a column which is Autofiltered. I can go to the autofilter drop down and then to custom and select two item numbers to allow through the filter, however I have about ten item numbers that I want to allow through the filter.
View 10 Replies
View Related
Oct 24, 2009
Working in Excel 2003. I have a VBA code that, if a particular option is chosen from a drop down box, then a message box appears. What I'd like to do is alter this code so that if cell J5 has "Text 1", "Text 2", or "Text 3" then the message box does not appear. Here's my
View 3 Replies
View Related
Feb 11, 2010
I am trying to create a COUNTIF formula which will work with two conditions. If you see the attached spreadsheet you will find the data that I am trying to apply a formula to. I have my data in the table on the right. The table on the left is supposed to show the number which the number of destinations that had a certain range of visitors.
As you will see that there are 3 destinations that had 12 or more million visitors, this was counted using a basic COUNT IF formula but for the rest of my data how can I apply the formula so that the correct number of destinations are counted. For example what formula would be needed to count the number of destinations that have had 8-11.9 million visitors. I am guessing that the formula will have the conditions ">=8" and "<=11.9"?
View 3 Replies
View Related
Jun 25, 2014
I have two different tables, 1 table gives the conditions you need for specific stations. Stations is shown vertically (10 to 150), and conditions people must have are shown in the top row. (Alphabet). This data is given and can change alot. If I compare this data, a new table will be created that compares the conditions per station with the people if they have it too. Then it will need to give an answer, 'Yes or 'No''. Yes means he has met all the conditions needed, so has all the competences needed for the station.
I tried to do this with normal formulas, but no success. I can do it by doing like 100 IF's in 1 formula, but that isn't efficient.
Also I have done a formula:
IF(SUM(IF(Competentions!$D$21:$BT$21=Competention!$D10:$BT10,0,1))=0,"x",""),
But that only compares if the person has exact the same competences, if he has more then the needed on 1 location, a NO will be returned. Same with count, that doesn't work either, if he has the same amount of competences needed for example station 10, he is allowed to, but he doesnt have the competences that are needed for that station..
View 4 Replies
View Related
Mar 8, 2009
I have a formula in cell A1 and text in either cell B2 or C2. This text continues down the rows until there is no text in either columns B or C. I wish to copy the formula in A1 into A2 and then continue copying down column A until both cells in column B and C are empty. What is the excel VBA code?
View 2 Replies
View Related
Jun 17, 2009
I'm having toruble developing a formula to allow me to add all the values in cells A1 through A100 if their respective quantities are more than 0.
What i mean is if i have the following columns of data:
A B
6 2
2 1
4 0
A being the values added and B being the quantity then i only want A1 and A2 Added together. Therefore the value would be 8.
View 3 Replies
View Related
Sep 28, 2005
I am looking for a function or group of functions that will:
Match Column C and for every unique item in column A, it will sum column D.
Example:
Column A: Column B: Column C: Column D:
12346 Level1 ACME 1
12346 Level1 ACME 1
12346 Level2 ACME 1
12376 Level2 ACME 2
13255 Level2 ACME 1
13548 Level1 ACME 4
12356 Level1 WIDGET 5
12356 Level2 WIDGET 5
12376 Level2 WIDGET 6
12378 Level3 WIDGET 2
12378 Level1 WIDGET 2
View 11 Replies
View Related
Nov 23, 2008
I have attached my sheet. I want to apply IF-THEN formula in P1 cell in this condition:-
IF C1=Y then =IF(OR($D$4="",M8=""),0,IF($D$3="USD",J8,IF(J8="na","na",J8/ D$4)))
OR (IF NOT)
C1=N then=IF(OR($D$4="",M8=""),0,IF($D$3="USD",M8,IF(M8="na","na",M8/$D$4)))
View 3 Replies
View Related
Dec 2, 2008
I have two sheets of data.
Sheet 1:
...........Column A......Column F
Row 1........"F"............."acpt"
Row 2........"F"............."acpt"
Row 3........"F".............."---"
Row 4........"F"............."acpt"
Row 5........"G"............."acpt"
Row 6........"G"............."acpt"
Row 7........"G".............."---"
Sheet 2:
............Column A......Column B
Row 2........"F"............[count all "acpt" in Sheet 1!F:F if Sheet 2!A2=Sheet1!A:A]
Row 3........"G"............[count all "acpt" in Sheet 1!F:F if Sheet 2!A3=Sheet1!A:A]
In sheet 2, B2 should = 3 and B3 should equal 2. I can do it manually by simply adjusting the range in a countif(Sheet1!F2:F5,"acpt"), but I want it to be automated because the number of F's and G's in Sheet 1 is dynamic.
View 2 Replies
View Related
May 15, 2012
How to get the MAX of a range if 3 conditions are met.
1 after this date
2 before this date
3 Column G is equal to A2
Ranges are on sheet1
Date reference is on sheet2
range to sum is on sheet1
V14.
something like
MAX sheet1!H:H
IF sheet1!$G:$G equals $A2
AND sheet1!$P:$P,">="&EOMONTH($B2,-1)+1
AND sheet1!$P:$P,"
View 7 Replies
View Related