Our company initial is TP, but sometimes it might be combined with a reference number, e.g TP5659
I would like the IF formula to stick pick this up..
I know if it was just the phrase 'TP' I would use =If(a1="tp"...
How do I express that I would like my If formula to work when something 'contains', not an 'exact match'
I have been reading the different post here on the forum.
I have found a snippet of code that will open a pop-up message from a value of a cell. I would llike to do this on one of my reports. Here is the scenario.
Range (R5:S5) 0.15 them MsgBox "Note Must Be Entered For +/- 15% change"
But There are 14 different cells that I want this to work for. The value differs from day to day, and this should pop-up happen without a user having any actions other then the calculated value. But ones the note is entered in the "Notes: cell the message will not return.
Hope I explained it correctly.
The value of the cells in question are calculated in each cell with a formula example "=IF(ISERROR(Z20/H5),0,IF(ISERROR(Z20/H23),0,(Z20/H23)))"
I want to add all of the numbers in column D if Column C contains the word "REMO"
View 4 Replies View RelatedI'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
I need a nudge in the right direction here. I have found several examples on the board that have spawned some ideas but need a little more help.
I am looking to enter variables into 2 columns A and B and then run these variables through an expression.
I have already done basic cell math and want it to be cleaner by using VBA to run the calcs and then spit the answers back into the spreadsheet.
I think I have an idea as to how it need to function but do not know the exact commands to make it efficient.
I will have 2 columns. In column B, will be a list of invoice numbers. Now each one starts with the "job number" (Ex. 51APGC01) then a dash and unique number per invoice. (Ex. 51APGC01-01)
In column A, will be a list of Purchase Order numbers sent by the customer so we can bill. Now, sometimes we get one PO for multiple invoices, (Ex. 03, 05, 06, 07.....)
What I am looking to do, is when I get these PO's, I enter them in and then I print a report for our billing Dept. The way the output line (cell) will read, is Job Number, then each unique number: (51APGC01-03, 05, 06, 07)
There are multiple jobs and each one has it's own report, so the job number will be dynamic and defined else where in the workbook. I can define that. It's just getting the numbers after the "-" and putting them in only.
In excel 2007 i need to do a formula...
I have the number of hours and de couste...in an other cell i have the expression that says that is paid or not....
I need only sum the cells that have the expression paid ...how do i can do this?...
I have a large spreadsheet in which I have reached the limit for conditional formatting. (i.e. 2050 rows)
To resolve this I can add a single expression for conditional formatting across all cells. This means I need to create a more complex expression.
I effectively have two types of conditional formatting that are very similar. I have the standard Yes, No & A/L which I wand to shade accordingly. I then have alternating columns of Yes , No , A/L . I have introduced spaces to stop these values from being counted by another formula but still display yes and no. I want the shading to be the same in both cases.
I have built the following expression which does not seem to work.
=ISNUMBER(SEARCH("Yes",(ADDRESS(ROW(),COLUMN()))))
The two components of the expression work separately:
=ISNUMBER(SEARCH("Yes",P3))
=ADDRESS(ROW()-1,COLUMN())
There are 20 worksheets in my workbook. For the first ten, I would like to print out the only sheets in which cell E28 = 0. For the last ten, I would like to print out the sheets in which cell T1 = 0. Each sheet has a unique name.
View 9 Replies View RelatedExcel 2000
Really simple data sheet: list of names with homerooms
Lookup table: list of homerooms with teachers. (named "Range")
=VLOOKUP(E2,Range,2)
Some of the cells return the correct name, others #N/A.
As far as I can tell they are all formatted exactly the same; I've tried
several different formatting options. The correct ones stay the same; the no
data ones do not change either.
I want to count the number of cells in a row containing a value greater than a fixed number but less than a number entered in another cell.
For example, in the cells A1..K1 I want to know the number of cells which have an entry greater than 7 but less than the value I have entered in cell M3. I have used the COUNTIF in other circumstances but I cannot find out how to use it with this "greater than and less than" condition?
I'm wondering if there is a way of linking to cells by looking up part of the link in another cell. E.g.In Sheet1, cell A1, i have some data that I want link to from Sheet2. Normally I'd do that by using "=Sheet1!A1" in the cell in Sheet2.
But I'd like to lookup the "A1" part of the link from two different cells in Sheet 2. Something like: =Sheet1!(Text from B1 in Sheet2 AND Text from A2 in Sheet2)
or: =Sheet1!(Sheet2!B1&Sheet2!A2). So that if Sheet2!B1 would contain an "A", and Sheet2!A2 would contain a "1" the resulting link would be the correct "=Sheet1!A1"
I have a column chart in excel. I would like to fill in part of each bar based on a percentage.
View 1 Replies View RelatedI tried to modify a sumproduct formula given previously by Kris which is to sum base on strings, to solve another problem but with no luck. My attempt without success is in BLUE column whereas the correct answers should be that in the RED column. Basically my problem is that if a condition is true, I want to concatenate the values of 4 cells with a space in between them.
View 4 Replies View RelatedLoad Comboboxes With Variant Range using ListFillRange
I'm trying to implement this, but am getting a 400 error when this code runs....
I'm having trouble with the Rmdir statement.
I have tried ...
Am trying to use index match to return value in column B based on matching 2 first chars on the left in column A.
Col A Col B
POS Majalt
POS Minalt
POS Instinctive
NB Viral
NB Sierra
NB Pierce
CLM Team1
CLM Team2
OS Tr1
OS Tr2
HR Tetra
HR Pentagon
IT penelope
Cell C1 : NBA MyFormula : {=INDEX(B:B,MATCH(LEFT(C1,2),"*"&$A:$A&"*",0))} <<== I need to find the match of NB only in range A:A to return the related values in Col B.
Expected Result:
NB Viral
NB Sierra
NB Pierce
Other than formula stated above, I've also tried with Isnumber(search), etc but all return errors.
I want to track winning football teams by highlighting the victor. So lets say I have a cell that contains
Raiders @ Niners
and another cell containing "home" or "away" indicating the winner.
Based on the home/away cell, I want to either highlight Niners/Raiders.
I have code that takes full cell with filename, looking for it in certain directory and copying to another directory.
Sheets("Unique").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
For Each rng In Selection
If rng.Offset(0, 4).Value <> "" And InStr(1, rng.Offset(0, 4).Value, "http") = 0 Then
FileCopy SourcePath & rng.Offset(0, 4).Value, ThisWorkbook.Path & "" & DestPath & "" & rng.Offset(0, 4).Value
End If
Next rng
I want to change the code that it will copy based on number. Any suggestions?
Example:
I have range of filenames.
10989478.MEC0388A-L0_RoHS_globemotors_1.pdf
10989495.Keystone_M1.4cat. zip
10989559.MEC0388A-L0_RoHS_qualtek.pdf
I want to copy files starting with 10989478, 10989495, 10989559. I mean ignore the part after "dot".
what the formula is if I want to hide all rows that begin with: "FORCE-MATCH CANDIDATE" in a specif column. The text after that string varies based on user input.
View 9 Replies View RelatedI'm trying to make an expression to calculate some fields in Excel 2007.
Trying to check fields up to a given number and give them a high multiplier, and add the rest of the numbers with a lower multiplier.
I have multiple fields with numbers in hundreds. I want to multiply the sum of this fields (up to 500) with 4. But the expression must also start the count from the first field, and when im hitting 500 as a sum I want the rest to be added with 2 as multiplier (the reason for the prioritizing of the fields is that they also have their own values/multipliers, but I'll skip that to avoid to much confusion here).
It's probably a lousy description. I'll give an example:
Field 1__Field 2__Field 3____Sum
100____300_____500______2800
How in earths name did I get that Sum you might ask.
((100 + 300 + 100) * 4) + (400 * 2) = 2800
400 is the number left from the third field wich I only want to multiply with 2 instead of 4.
Another example:
Field 1__Field 2__Field 3____Sum
300____600_____500______2000
((300 + 200 + 0) * 4) + (900 * 2) = 3800
900 is taken from the rest value of field 2 and 3.
If there any way of doing this "sum check and multiply, and then add the rest with a different multiplier"?
With Excel you can do If(array, array2, false) to poplulate another array. Is there a way to replication this without looping. For instance can the IIf hold an array as its expression or is there any other way of replicating this If statement without looping?
View 4 Replies View RelatedI want to apply regular expression for password
a) The first letter cannot be number
b) the password should have a combo of numbers letters and one special character
c) The password should begin with a big case letter
Here's what I need to do. I have a list of row numbers in AA2:AA6, so for i =1 to 5
I want to insert a page break before cell (column AA and Row(#in cell AA2-5))
what the long expression for the range being summed below.
Reason: I need to exclude some cell values.
I'm trying to figure out the whole Regular Expression thing...so I figured Hotpepper's EXDATA UDF would be a good place to try and rewrite with Regular Expression...I got it to work...but not sure why as it seems opposite of what I would have expected...
eg pattern [^/d] i thought would give me everything except numerics...but it appears to be giving me numerics....
here's the code and sheet...can someone help explain what's going on? .......
I am currently using a SUMIF statement as follows: =SUMIF(W1:W1500, "Charnwood" & "*", S1:S1500). The problem i am having is the fact that SUMIF doesn't work with a reference to a closed worksheet. I tried using SUMPRODUCT but it did not work. The reason it didn't work is because I need to find in column W anything with Charnwood and anything else past it, auch as Charnwood Estates. This is what I tried: =SUMPRODUCT((W1:W1500="Charnwood")*(W1:W1500="*")*(E1:E1500="S"),S1:S1500)
which of course is a syntax error.
I am using excel 2000. I want to apply conditional formatting to a range of cells if they contain (V) as part of the cell contents. The cells will generally look like this : 09.00-6 (V) or 9-5.30 (V). I want to shade any cell if part of the contents of that cell is (V).
View 2 Replies View RelatedI am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).
View 3 Replies View RelatedI am trying to create a calculator in excel that has five parts. I want to manipulate each part based on the other parts. I cannot figure out how to remove the formula and replace it with the derived number without a circular reference. The circular references slow down the main macros of my program.
View 2 Replies View Related