I have a formula to pick up certain bits of information depending on the contents of a cell.

=IF($F3="138-1",MID($E3,11,4),IF($F3="151-26",MID($E3,11,4),IF(MID($F3,1,2)="00",$E3,IF(MID($F3,1,2)="40",$E3,IF(MID($F3,1,1)="-",$E3,IF(MID($F3,1,1)="8",$E3,$F3))))))
The data in column F has a mixture of 8 digit numbers including numbers starting with multiple zero's, the numbers 138-1 and 151-26, then some text.

Orginally the 8 digit numbers only started 00 so it was easy to pull out the relevent information, then they started 40 and then 8. I'm just worried that I'm going to end up with too many nested 'if' statements (and reach the max limit), so I was wondering if I could do 'any 8 numeric digits' instead of this current 'dead end' solution.

I'm not sure if I could use a wildcard of some sort with the MID formula

I want to be able to look up and match several values in my worksheet to confirm, that an entry is valid. I would like the test to return an OK / NOT_OK value at the end of each entry. In the attached file you will find four different variables. In order to include an entry, it must be matched to find related entries, before it can be included.

For an entry to be OK, it must contain a company that has been listed in the same industry, in four different quarters (not necessarily in the same year). A company can be listed in several industries, but as long as there are not four entries in the same industry in four different quarters, the entries are not yet OK (hence NOT_OK). Therefore the only entries that are valid in the attached file are:

Company A Q1 2009 Administration OK Company A Q2 2009 Administration OK Company A Q3 2009 Administration OK Company A Q4 2009 Administration OK

I work in XL 2007, but have converted the attachment to 2003 for maximum compatibility.

Using Microsoft Access to pull from a database work orders. Some of those are what we call "dummy" work orders and have a letter in them, real work orders are signified by a 10 digit number. I would like to sort and eliminate the letters to find appropriate data. I am assuming I will need to copy into excel and do some sort of formatting but not sure where to start.

I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.

I want it to show me that if B3 is bigger than any of the other cells that you should "eat less". It works if a number is entered into every cell, but if a 0 is entered into one of them it comes up with "eat more" when it should be "eat less"

I'm calculating a variance for a set of actual numbers vs. variance numbers. When there are no actuals, I don't want the formula to calculate a variance. How can I do this?

=SUMPRODUCT(--(GLCodes=$B7),MasterNetAmt_01) I want to dis-include the CC="06" in the summation. I tried using: =SUMPRODUCT(--(GLCodes=$B7),(cc="06"),MasterNetAmt_01)

I'm struggling to include a repeating 8 (or 4) weekly cycle in my sheet.

For instance, week 21 starts on 20-05-2013, than I would like to have the below cells filled untill 26-05-2013, following cells must be blank, untill 8 weeks are over, and the first date you see again is 15-07-2013, and so on. Using the MOD formula, I can get the first day of each cycle correctly, but I'm having difficult to get the next 6 cells entered and the next ones blank again untill 15-07-2013

I'm using now following formula, where B is the date:

Code: =IF(MOD($B$264-B264;56)=0;B264;"") but I need to nest another IF I guess that takes care of the rest.

I would like a cell to show either OK or No depending on the requirements of three other cells as below

G4 is the cell to show a value. For OK a date in G6 must be more than todays date, a tick must be in F6 and a date in D6 must also be more than todays date.

I can get G4 to do each section individually i.e. (A4 has todays date in it, updating automaticaly)

I am having trouble trying to develop a code to include into a spreadsheet for work. It will be a live spreadsheet accessed by a few people who will have control over there own columns in the spreadsheet (2 columns are designated for one project) Each Project director is to edit the info about their project.

So my goal is to put a code in cell C3 that shows the date that cells C4-32 and D4-32 were last updated.

Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"

I need the " solutions cells " to match to the " source cells " and return a " yes " or " no ". in cells d4:d10. Ex: #1 cell C4=01234598, it will conduct a search & match of cell A5 which contains the number 123, since cell C4 contains all 3 numbers from cell A5, then the formula would result in a " Yes ". The match has to contain all 3 numbers from the solution cells to the source cells.

Ex: #2 cell C7=67891234, it will conduct a search & match of cell A8 =901. Since cell C7 only contains the 9&1 from cell A8, it would return a " No " because cell C7 did not contain all 3 numbers from cell A8. I'm not sure if the IF, Match, Index or Count formula is the right one to use, they all seem to partially work, but still don't complete this problem.

See attached file, "Rate Sample Index-Match Formula".

I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".

In the 1st tab, "TEST FILE" there are a series of columns as follows;

A = Service B = From C = To D = Rate

In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)

The formula needs to do the following;

1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green 2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue 3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59

For Example;

The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.

Additionally, if we were to add service as an additional lookup match how would this work?

I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:

I have to pull all correct numbers from second sheet into first sheet at right column. I've tried to write a formula to solve the problem, the answer is not correct. The main purpose of the formula is compare the value from TASK sheet into first column in DB sheet and if find matches to continue matching with next cells on the row. The advanced level is to write all correct matches into 1 cell, delimited by comma.

I have 4 columns A,B,C, and D im trying to make an inventory list of items that I have on my website... A has a list of lets say 20 numbers well just say 1-20 B is the quantity my website shows available C is the list of items my supplier has which is what I want to have lets say 1-30 D is the quantity available of C

Basically I run a website and sell **'s so column A, and C are UPC # and I have 1000's of them...what I want is a formula to make the UPC numbers (C) to match what is in (A) so I know what I need to add...but also I want to keep D in the same row with C so I can add and decrease B

I am attaching an example of what I am trying to do.

sheet 1 is how the jobs are assigned - Each job is assigned based on the part type and the last 2 digits of the order number. For example, Jon works any order that needs wheels and the last 2 digits of the order number are between 00 and 49.

sheet 2 is current jobs - this sheet shows the current job by order number and part, the employee column (C) is empty and is where I would like the formula and result

sheet 3 is the result I am looking for - formula results are in yellow.

So what I am trying to do is make it so that as employees come and go and assignment of jobs change on sheet 1, sheet 2 will automatically update the employee name to match.

I have one list of waybill numbers which are 12 digits long and I have another file of waybill numbers which are 15-16 digits long. I have tried a VLookup but I do not get matches on these since the digits are not the same length. Is there any other formula I can use to match a 12 digit waybill against a 15-16 digit waybill number? I am assuming that if 12 digits match in both lists that it is an exact match.

how to match two exact amounts (one positive and one negative) and add them together. i've been doing this process manually but i bet there's a macro code out there that can help me automate this process.

fyi here are the things that i've tried: - created an absolute value column and sorted by absolute value then netted numbers against each other...this is only good if you're working with a few lines but if you try to match 2,000 lines/rows in excel it will take you forever [like what i'm doing now sigh!]

if you do have a solution, please do step by step instructions. i.e. pls tell me what to click etc. i'm an excel dodo so i need very specific and elementary instructions so i won't screw it up.

In the table above in cells A3:F4 is the draw result.

Option1: in the column H played numbers are separated by semicolon Option2: in the column I played numbers are separated by coma Option3: in the column J played numbers are separated by space

Is there any formula or conditional formatting method to highlight match numbers out of any one of three options?

With Range("H2:H5000") .FormulaR1C1 = "= SUMPRODUCT(('Data Sheet'!R2C11:R5000C11=RC1)*('Data Sheet'!R2C81:R5000C81=""G - Prior policy incident handling errors""))" .Copy .PasteSpecial Paste:=xlPasteValues End With

Right now its simply counting, but what I need it to do is to sum col L if col A matches col K. Col L contains numbers, and I need a sum of those numbers if the match occurs in A & K.

In column 1 are 220,000 words (that's a lot ), every words corresponds to a number between 1 and 13 in column 2. Now i want to recode texts (column 2) using the codes in columns 1 and 2. I could insert all text in one cell, or i could insert each word in one cell. Then I want to calculate the sum of the corresponding values (column 2) of all the words (column 1) for each time they occur in the text (column 3). In other words, i want to recode text to numbers and i want to calculate their sum.