I have two cells which refer to employee number: A1 and A2
(employee numbers are 001, 002, 003, etc)
I want Cell A3 to check if either A1 or A2 = 001
If both cells is 001, I want an "error message" because that is not allowed, and if one of them = 001 then I want the value from Cell A4 to be but in Cell A3.
So in my head the forumla should go like:
=If(A1 Or A2 = 001;A4;"-";)
I have a spreadsheet that uses IF(C5>J5,(EDATE(C5,12)),(EDATE(J5,12)))__ IF(J9<D9,(D9-$N$1)) where N1 is current date. If C5 and J5 are empty how do I get it to ignore the formula and just leave the other cells blank?
View 3 Replies View RelatedVBA to loop through all the cells, or an active selection, and if it finds a cell with a formula that starts with "=VLOOKUP" or "GETPIVOTDATA", then copy and paste values for those cells. If it comes across a cell that starts with "=SUM" then it leaves it as is. I've been looking for way to insert a wildcard to make this work, but can't seem to find anything.
View 2 Replies View RelatedI need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:
=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
I need two formulas that can do the folowing:
• check whether a cell contains text, if so, then trim all spaces and get result. If it doesn't contain text then set the result as blank.
• check whether THE CELL T13 contains text, if it does then check the adjected cell (U13) if it contains text, if so, then concatenate the two cells with a space between them and remove any other spaces. If the T13 doesn't contain text, the set the result as blank. Also, I have another question: how could I use the counta formula without counting spaces?
=IF(OR(Q136<O132,Q136>O132),Q136-O132,O132-Q136)
This does not seem to be working for me. I need to check one number against another and if the one cell is larger then the other subtract it or just the reverse.
I need a solution for this situtaion! Iam using a french version of excel2003!
I have #DIV/0! in P24 (i.e an error value) So I need to check in Q24 such that if (P24=#DIV/0!,0,P24). I need to have a logical formula for this one!
I have a direct mail company and lock in certain areas for a certain amount of time for my clients.
I want to be able to check a hand full of zip codes to see if they are aviable for new clients.
The lock in period is 60 days.
For a zipcode to be available it can not be in my list or has passed the 60 day expiration.
Attached file.
I need to come up with a formula to verify that the last 3 characters of a text string match the 3 characters in the adjacent cell. So I am comparing in the example below Column B to Column A.
Ex:
A1 010 B1 3.010
A2 010 B2 1A.010
My spreadsheet is very large and I need to get this done without manually having to look at each.
I have a Formula in Cell A1 and I am entering a Vlookup in D1, I get a #N/A in D1. If I enter for example Monday in Cell A1 instead of the formula, Vlookup works. My question is, will it not work in a Cell with a formula?
View 9 Replies View RelatedI have a worksheet I need help writing a formula for. I need to be able to check which recruiter (A711:A718) has the highest amount of contracts (B711:B718). I can write a formula for that, but this year each recruiter must have at least 30 contracts (B711:B718) and 90 plans (B711:B718). So in summary, I need to place in one cell the recruiter name that has the highest amount of contracts as long as they are above 30 contracts, and 90 plans. In the next cell I will need to do the same for the recruiter in 2nd Place.
View 9 Replies View Relatedis there a way to check for a value in a range, and return the value if it is not in the range, else return nothing?
H1 =85
B | C | D
25 | 50 | 75
40 | 30 | 10
12 | 9 | 66
None of the following IF statements will work:
{=If($B2:$C4="","",If(B$2:C$4<>H$1,H$1,"")}
{=If($B2:$C4="","",If(B$2:C$4=H$1,"",H$1)}
I got a pivot table that when selected will give me a value (actually its an ID number) I want in H3. I then got a list of values outside of the pivot table in column J. If H3 matched any of the values in column J, I need a “Warning”, if not “OK”. I have been using the formula =IF(H3=J3,"OK","WARNING") to match selected cells (H3 with J3) but cant get it to work to look down the column list. The OK or Warning is in cell F34 out of arms way!
View 2 Replies View RelatedI have a spreadsheet that has a formula placed from cells B21 through to AT21. This formula is present in the sheet 95% of the time, but occassionally the user has to enter his/her own value in one of the cells.
How can I check or the user be reminded that he/she has deleted a formula and replaced it with a user defined value. ie a message box(only as a warning) to be displayed just before the workbook is printed. The user should be able to acknowledge the message displayed and carry on.
******** ******************** ************************************************************************>Microsoft Excel - test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH2I2J2K2L2H3I3J3K3L3=
HIJKLMNOPQRSTUVWXYZ1Cat*ID*#ELECTMTC*HoursInv.*Material*$Mutiplier096450965509656097510975209757098891023610673106751067610842108772108429655#N/A1.5813310317206365573396459655#N/A1402310317206365573Buchanan Survey*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
This is a partial of the sheet that I am working on.
The current Formula in Column L is manually entered for each line. What I would like to know is if there is a way to have a macro that would create this formula?
What we would need it to do is check out the Cat ID #'s in cells H - J and based upon the numbers in there, look up the $ amount (right now they are in columns N-Z). The amount in column M would be manually input and then the formula in column L would be
the value in M multiplied by the sum of the values that correspond with the ID #'s in H-J plus 10.
I have a tab with about 10,000 records with IDs and timestamps (date and time). On another tab I have a list of IDs and start and end timestamps. There are duplicate IDs on both tabs. I want a formula for the 10,000 records tab that checks the ID and timestamp for each row against all the IDs and start/end time stamps of the other column and flags the rows where the ID and timestamp fall within the start/end timestamp on the other tab. It seems pretty simple, and it feels like the code should be something like this (but this code doesn't work):
=IF(AND(F2=List!C:C,B2>=List!F:F,B2<=List!G:G),"Y","N")
where F2 is the ID and B2 is the timestamp on the records tab. The "List" tab has the ID in column C and the start/end timestamps in columns F and G respectively.
What would be the correct formula to check the format of an address that must start with a number followed by a space then text - in cell B4
At the moment I have:
=IF(B4="","r",IF(ISERROR(SEARCH(" ",B4,1)),"r",IF(ISNUMBER(VALUE(LEFT(C5,SEARCH(" ",C5,1)-1))),"a","r")))
If it is incorrect or missing an 'r' should be displayed, an 'a' if correct
i wanna to put check boxes in colums d4:d41 with a formula inside which beginning with this formula (=Sheet1!$D$4) in cell d4.how to copy and drag down this formula into d5:d41?
View 8 Replies View RelatedIs there a formula I can use to see if a cell is highlighted? I need to filter for highlighted cells and want to use a formula to produce a binary result (1 for highlighted or 0 for no highlighting) so I can filter on that to only the highlighted cells.
View 5 Replies View RelatedI've two lists of text - one long with multiple words and one short with one or two words. (sample data: [URL]
I would like to check if List A has the word(s) present in List B, and return a Y/N accordingly.
I have a worksheet which J3:NJ3 lists the dates for the calendar year. (Example - J3 = 1/1 , K3 = 1/2 , etc. )
A3:C3 are my headers for project information (project name, manager,etc)
In D3 I will be entering employee names. in E3 I will be entering Project start dates, and in H3 will be end dates. These dates are selected via validation lists of the dates listed in J3:NJ3, and it is set to auto fill the adjacent date range with an "X" after the start and end dates have been selected.
Now I also have another worksheet which is a pivot table that will auto update upon any workbook change, and will show each employee and all of the dates in which they are scheduled to work. I am using conditional formatting on this sheet to highlight any dates that the selected employee is double booked on two or more projects.
I want to keep this second worksheet as is, but to avoid having to flip flop back and forth between tabs to find out of someone is double booked, I would like to add a column (I) which will either say "Available" or "Double Booked" . Is there a formula that I can use for this? I thought about Vlookup, but I don't believe I know of a way to write a Vlookup formula that can check a dynamic range of entered dates. I thought perhaps a nested Vlookup/Hlookup, but not quite sure if that will work either.
I have the following data (as below).
Basically I want to check if a value is in AC but not AD, I would like to mark it as such in column AE i.e. mark all datevalues in column AD that aren't in column AC.
Could anyone please help with the required formula?.....
I have software that generates an excel worksheet with data with dates. In this column of dates, there are either valid dates or dates with all zero's (00-00-00). I need to setup a formula that checks this column, if they have a valid date (anything greater than 00-00-00) then mark that row with "Contract", if hit has all zero's (00-00-00) then mark it as "CP". One thing, this column is not formatted as "Date", it is formatted as general. I didn't know if that made a difference.
Also, is there a way to setup this WHOLE column with this formula so that as soon as the data is extracted, it will automatically change the cell value to CP or Contract?
I have a range of cells which contains one of two text values (Ok and Not Ok). I would like to check my range of cells for these values. If this range have one cell that contains the text "Not Ok" I would like the formula to say "Not OK". If all cells contain "Ok" I would like the formula to say "Ok".
View 8 Replies View RelatedI have multiple colums that I want to compare (A=F), (B=G)........
I want to see the difference of the two as shown in Blue column (example file is attached.)
Skærmbillede 2012-12-13 kl. 18.37.15.jpg
I have a number of worksheets, each sheet is set up like a data entry form, at the bottom of which is a command button that takes them to the next worksheet based on a value in a cell. what i want to do is before it takes them to the next worksheet it checks a selection of cells, if any of which are blank it shows an error message and wont allow them to continue, better still the cell that is blank it high lights with a red border.
View 2 Replies View RelatedI am using the followng code to check data is entered in a cell ...
View 6 Replies View RelatedI would like to check a combination of two cells, if these two cells are both empty (not zero, just blank) then it will return a blank in another cell. I tried using AND but am unsure how it works. I would like to use a "Case" Function.
Function FirstCheck(Count1, Count2)
Select Case FirstCheck
Case Count1 = "", Count2 = ""
FirstCheck = ""
Case Else
FirstCheck = Abs((Count1 - Count2) / (Count1 + Count2))
End Select
End Function
I am trying to get a statement to check 7 different cells to see if they contact the same value, i go into a little depth
if a person is late to work then, a text value of 'LT' for late is placed in the cell, if they are late the next day then another LT will be used and so on.
from this then how many mins they are late is loaded into the next cell so
i have so far (in english) =IF Cell Mon,Cell Tue,Cell Weds, Cell Thurs, Cell Fri, Cell Sat, Cell Sun = 'LT' then SUM all mins late
but excel is saying there are to many arguements, any one any ideas can provide sample spreadsheet if needed