I have text in Q2:Q30 this text is spread out in N2:N500
then i have Numbers in K2:K500
what i would like to do is to
find Q2 in N2:N500 (can be there many times) and add numbers from K2:K500 where found into R2
example Q2 is Denmark
I have Denmark in N40, N60, N490
Now in R2 i would like to add K40 + K60 + K490
i have started with this
=IF(ISNUMBER(MATCH(Q2,$N$2:$N$500,0)),SUM but now i`m stuck
I need help to this : When i type a number to a cell and press enter , i want to check if this number exist in a range of cells (in sheet 2) , and if exists , excel show me a message. Actually i use it for my *** club. Number is the client code. When i writte 50 in a cell , i need from excel to check if this client own me money , and show me some message..
View 8 Replies View RelatedWe have a simple holidays workbook, with seperate sheet tabs for each member of staff in the department to note down there holidays in a calendar.
The 1st row (from B1 and onwards) contains the day numbers of the month, ie. 1 - 31
The 1st column (A2 downwards) shows the 12 months of the year.
The holidays are then marked in the relevant cell matching the dates required. The codes we use are F (full day), A (morning), and P (afternoon). A countif adds up all the occurences of the various codes and totals the ammount of used/remaining days.
Hopefully that drew a picture.
The problem is, each sheet only shows the holidays marked down for that person. There is no way of checking for a "clash" (ie. 1 member of staff off on the same day as another) without flicking to the other sheets and checking yourself.
how can I get a cell shading to automatically show up on the sheets, if there is a holiday noted down (ie a value, A, P or F) in one of the other sheets?
I am trying to automatically merge cells based upon a condition in different cell. Below is the example of what I would like to achieve.
Apple
Red
Apple
Red
Apple
Red
[Code] ......
I have 2 sheets, sheet1 and sheet2. On sheet1 I have a cell (w,3) which displays either "OK" or "Needed". It will be like this for every row on the sheet. On sheet2 I have more information. I have been trying to create a macro or VB function that everytime the workbook is opened will scan the entire column W for any that say "needed" and if/when one is found copy the data in the cell in column A of the same row from sheet1 to column A of the first blank row in sheet2.
So say cell w,6 displays "needed", I then want to have cell a,6 copied from sheet1 to sheet2 into the first column A that is blank. I don't really care if when w,6 changes to "OK" it removes the entry on sheet2 or not, in fact if it does that would be great.
Here is the code that seems to be not so complicated and has copied information from one sheet to another, can't get it to work consistantly. I copied it off a similar post on this site and am not sure what the last value = "1" is doing but I doubt its helping my cause. The cell I need to trigger the action is in the 23 column, the cell I need copied is always in the first and the cell I need it to be copied into on the 2nd sheet is always in the first column as well. I'm pretty good with regular logical statements but I feel that a lack of knowledge about all the available commands is really whats hurting me on this....
I have 5 sheets, each sheet has 1 column of data (column A). I want to find the values that exist in all sheets. I will gather all values in 6th sheet, and I want to write "YES" in column B next to value that exists in all 5 sheets. How can I do this? For example:
[Code] ......
Afternoon everyone i am having abit of trouble working on an excel 2007 spreadsheet. In cell I1 i have a tab called Color. i want the cells below I1 to be filled with one of 3 colors green, yellow, or red depending on whats in cell F "Status" (closed or open - in progress) and cell G ECD for estimated completion date.
Green - i need it to fill green if status is closed. Yellow - need it to fill yellow if ECD is any date greater than today and if status is open. Red - need it to fill red if ECD is todays date or older and status is open.
I have a conditional cell that change color or not if condition...OK so far. Now, how can I change the color of a range of cells when condition is in just one cell. Ex:
If C15>=150, change (C3;C20) to yellow.
I really can't find the way to do it.
Two Sheets:
1) Top Sheet
2) IRA
- Cells A4:A10 of sheet "Top Sheet" contain dates. i want B4:B10 to show the IRA value as of the date in column A:
____A__________|_B______________________
4 | 09/22/2005 | $ Total as of date in A4
5 | 10/06/2005 | $Total as of date in A5
6 | 10/20/2005 | $Total as of date in A6
7 | 11/03/2005 | $Total as of date in A7
8 | 11/17/2005 | $Total as of date in A8
9 | 12/01/2005 | $Total as of date in A9
10| 12/15/2005 | $Total as of date in A10
- Cells A6:A12 of sheet 'IRA' also contain dates but not in the same sequence (some date ranges had several transactions), B6:B12 of sheet 'IRA' contains the transaction values during the date range:
____A__________|_B______________________
6 | 09/22/2005 | $100
7 | _ _ _ _ _ _| $200.................................
I have been struggling with a way to use an IF statement that determines if a cell in column B = 1 then copy that cell and the two cells next to it to a cell starting in range B50. Once the condition is true would want it to copy another below and so on.
B C D E D
1 E10 Rear door failed BL OK
2 B4 Clearance light inop. KL OK
2 C1 Fire extinguisher date expired KL Ok
1 E1 Bumper falling off BL NO
The first and fourth row have a "1" in column B. So as a result these two rows meet the condition and want to copy just the cell in B thru D. Results would look like below
First entry copied to cell B50
1 E10 Rear door failed
1 E1 Bumper falling off
I am trying to create a spreadsheet to monitor behavior modification progress for children. I need to figure out a way to be able to send an email notification when there is a blank cell in a column that has been there longer or equal to 24 hours.
View 1 Replies View RelatedI am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)
I would like to know if it is possible to use a wilcard in an IF/THEN statement. For example, in Cell A1 I have 1234, in Cell A2 I have 9934, in Cell A3 I have 8346659. I want to build an IF/THEN statement in Cells B1, B2 and B3 whereby it looks at cells A1, A2 and A3 appropriately and where ever it finds a "34" anywhere in the cell it gives me a "YES" or "NO" reply.
View 3 Replies View RelatedThis is Excel 2010 on Windows 7
Trying to figure out a VB Script but don't know where to start.
I have a sheet for each month. lets take September 2013
A Column with 200 Servers (A1:A200) on the Intranet listed as server-1/, Server-2/ Server-3 ... Server-200/
Each Server lists a location of a file for each day (31 columns per row)
I am trying to check for the existence of the files on each server (The Cells contain the URL and File Name in Range B2:AF201) starting with server-1 check the URL in B2 if it exists, go to C2, Continue with that row until the file check is False (The Page will show a 404 error) if not found delete cell url and continue. do this for all 200 rows
I do have a problem with my excel sheet. i have a workbook with 2 sheet which i need to compare a colume of data from sheet1 with sheet 2 colume M2. If it is matched, then sheet 2 colume BJ2 will return a "F" else "N"
So i was thinking of using an If formulae: =IF(M2=sheet1!A2:A16),"F","N") but somehow the above text return a msg of "A16" is invalid. and i tried to used VLOOKUP by defining the mapping sheet as "KEY"
=VLOOKUP(M2,KEY,A2)
and i copy and paste the above formulae to the remaining column M for comparison. Somehow, this did not work as well.
When I use the formula:
View 12 Replies View RelatedI sent an excel file to a friend. When they opened the file a half triangle mark appears in any cell where there is a formula. How do i get rid of this mark?
View 3 Replies View Related1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
Here find the excel file
My requirement
1) 4 values contains in each row based on the values from those cells the max value will display.
2) if more than 2 cells have empty,NR or NA text means the entire row has to delete.
3) if 2 or more that means 3 cells having values the empty cell,NR or NA cell will place value with the condition of macro that is 75% of other values which is maximum among them.
way of protecting an array of cells on the condition that information is entered in another array of cells or vice versa. So, put simply, if data goes into a1 then a2 becomes protected (or vice versa).
View 14 Replies View RelatedBasicalley I have a series of columns, each 5 letters together represent an order(a,b,c,d,e), the columns are repeated approximately 15 times, similar to the following;
Bag Size:B/G5lb2lb 16oz8ozB/G 5lb 2lb (repeat...)
Catagory 1ABCDEA1 B1 C1 (repeat...)
Catagory 2 AABBCCDDEEAA1 BB1 CC1 (repeat...)
(...Repeat)
The B/G stands for Whole Bean, or Ground, the A's are drop down lists giving those 2 options, the other cells (non-A's) are all numeric. At the end of each row, the orders need to be summed up into categories; 5lb whole, 5lb ground, 2lbs whole, 2lbs ground, 16 whole, 16 ground etc..
I am looking for a way to sum each category for all orders as necessary, in one cell adding all B's (in that row for that catagory) if its corresponding A is whole bean, in the next summing all b's if its corresponding A is Ground. The same will go for the C's D's and E's.
I want to add a certain value to all cells when a certain condition is met.
I'll try to explain:
I have the following table. What should happen is by pressing a button it would automatically increase the value specified (in this case 50) to all values in the column "Frete 20" that meet the condition of the "Pais" being, in this case, "Angola".
The same should happen for each column (Frete; BAF; CAF;...)
Pais Frete 20 BAF 20 CAF 20 PSS 20 Angola 20 30 40 50
So when clicking the button it would add the corresponding value to each column
POL Pais Frete 20 BAF 20 CAF 20 PSS 20 Cape Town Africa do Sul
[Code] .......
I have a progress column of cells with a data validation dropdown list (the list has 3 values).
If the 'complete' has been selected from the dropdown list and a date has been suppield in the column next to it, i want that cell in the progress column to 'lock' - be uneditable.
How do i do that. I don't know who a macros works and i don't understand VB scripting stuff.
How to make blinking effects vba on the cell, if cell A1:A300 have FAILED value?
View 3 Replies View RelatedI have a Requirement where we need to concatenate the cells based on conditions
i have the attachment for the reference . please download the file from the link.
I have some data in numaric form from range a2:f20000 which I have got as a sum from a sheet and I want if any cell (a to f) from row 2 to 20000 has sum up value blank or 0 then it deletes shift xlleft.
View 9 Replies View RelatedW3:W300 is a protected range containing formulas returning numbers from one upwards, with two decimal places.
I need some vba to check this range and, if a number greater than 1 is found, to make the corresponding cells in Columns X and Y blank and lock them.
I have a workbook (column K) set with the following format conditions:-
Cond.1 - =(I3="declined")
Cond. 2 - =(G3>0)
Cond. 3 - =(K3