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 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 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 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 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.
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 am looking for a single formula I can run along a single row that looks at several columns of data, and then checks if the date range falls between the running row, take it's relevant number, and multiply it by a single rate. The attached probably explains it clearly - I think the solution would be some sort of Sumproduct, but my attempts have failed!Example - Date Range with Calculation.xlsx
View 10 Replies View RelatedDoes excel have a formula to check to see if every row in a specific column is blank and if it is then set the value?
View 10 Replies View RelatedIs it possible to have a formula check the color index of a cell?
Example if A1 color index = 6 then X
Ok. I am creating item lists in excel for every product that my company sells. I am in need of a formula that does the following:
I want the cell to check to see if there is a value in another cell, and if there is anything at all, i want to display the word Yes as the value, or leave the cell empty.
So I want the cells in Column A to Check if the adjacent cells in Column B have a value. If the column b cells have a value I want the cells in Column A to display the word "Inventory Part". If there is no value I want the cell left blank.
Can I use conditional formatting to check if a field has a value or a formula?
View 4 Replies View RelatedI'm entering some time data (in format hh:mm) into Columns. Pretty basic Workbook that I have been given and, to be honest, it's just donkey work putting the data in. The times going down the Columns need to be progressive (ie B8 is arrival time, B9 is admission time etc). This goes through to B27 and then repeats from C8-C27 and ultimately J8-J27.
I'm looking for something as a formula to pop into Column K (or VBA, I don't mind!) which will do a simple verification to ensure that the times I enter is not less than the any of the cells above in the aforementioned ranges. I can do simply the cell above, but due to rusting of brain, can't think how to do this for the range!
Users copy and paste source data from a report into worksheet 1 each month. Data from last month is deleted and data for the current month copied into worksheet 1.
I am trying to write a formula within worksheet 1 to check that data for the current reporting period only is in worksheet 1. For example all data from last month's reporting period has been removed and the only data in worksheet 1 is the current reporting period.
Reporting period is shown in two columns Year and Period number (1 to 12).
I've tried this in formulas (if, if/and, if/vlookup, if/vlookup/and) and I've been trying to write a macro for this (which I'm thinking needs to loop).
Problem: I have a report that is broken into three categories.
Column A includes locations, Column B includes vendors. Across the top, the monthly report is broken into four classifications (trans types), which are then further subdivided by quantity and cost. (Rough estimation is provided below)
Locations
Vendor
Z53
Z50
Z51
Z52
[code]....
My data comes in sorted by location, and subtotaled by vendor and trans type. So I'll have a row of data that includes:
Location 1, Vendor A, Z51, $200, 2
Location 1, Vendor A, Z53, $150, 2
Location 1, Vendor B, Z53, $150, 1
...
What I need to do is pull the data for Z53 based on Location & Vendor. So from the data is would read Location 1 & Vendor A and then find them on the report and paste into the appropriate row (in my report Z53 qty is col E and $ is col F).
I was trying to do a macro that within a determined range (finding the first and last instance of Z53) would match location and vendor to the monthly report and paste the values of Qty and $ into the appropriate fields. Below is what I managed to mangle:
Code:
Dim Holder1 As Range
Dim Holder2 As Range
Dim Holder3 As Range
Dim Holder4 As Range
Dim Found As Range
Dim y As Range
Dim x As Range
Dim Z0 As Range
[code]......
I was originally trying to match from the report to the data and then copy back to the report, but I had the idea of instead taking the location and vendor from the data and finding the match on the report and pasting. That way I wouldn't have to worry about errors (unless a new location was added that was not included on the report - which I think is an easier fix than trying to go the other way, but maybe not).
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 RelatedAttached worksheet which is an sales incentive plan. I need cell B69 to represent the amount in b68 unless neither b30 or b99 are > 0. In other words if even 1 of the cells b30 or b99 come back positive, you get the amount in b68. If it comes back 0 in both cells b30 and b99, then they get 0.
View 4 Replies View RelatedI need a formula to check two times and count the colum figures.
example only:
countif(=>4:30:00 PM <=5:30:00 PM)*(11/12/06)
So, I need to count all cells with time between 4:30 and 5:30 with the date of 11/12/06.
