I've followed several tips on how to match these formulas, but somehow cannot make them work. I'm using Open office's version of excel. I need a formula that searches on 'tension downfall' for data in the first sheet, and then return a value corresponding to the row and column that match the input on the first sheet.
'tension downfall' shows a double entry table which shows amperage against length and returns a wire size. I would like to input amperage and length on two different cells and have the wire size needed for those values. Is it clear enough? I'm attaching my current work to make it a little bit clearer.
Is it possible to nest the two functions of match & offset. I am working with two worksheets. One is a constant the other is building. I want to match a text from the first spreadsheet in the second sheet (otherwords I want to find that exact within the other spreadsheet) then I want to retreive the offset value to the right of the matched cell and place it in the constant worksheet where the formula is.
For Example
Sheet 1 Sheet2
Company: First Name: Company: Name: Adamack's ? Adamack's Patrick
So if I put the formula where the ? mark is, I want to match the first company name on sheet 1, from the range in sheet 2, then I want to get the offset from that match 0,1 to give me the name patrick.
I am trying to create a formula with mutiple criterias to come back with results of whether that person is Eligible or Not Eligible for stock. The criteria to be Eligible is as follows:
1) Hire Date before 1st March 2007
2) Band 1 and the performance review = Exceeded
3) If Band 2-5 then performance review must be either Achieved or Exceeded
See attached file for the example. I have got as far as the first 2 points but i am struggling with incorporating the last point.
I have this search function that works well: =IF(ISNUMBER(SEARCH("which is < ",E9)),"test ok", "not ok") my E9 cell contains the phase: "I have a number which is < 9, and which is positive..." E9 has "which is < ", then i get back "test ok"
My question is.... how can I do this multiple times within the same function/cell. i.e. if I wanted to search "which is < " and/or "positive" in the same cell, then i get back "test ok", "positive number") I keep trying all combinations to no avail, like: =IF(ISNUMBER(SEARCH("which is < ",E9)),"test ok", "not ok"), =IF(ISNUMBER(SEARCH("positive",E9)),"positive number", "no good")
Keep in mind I am using a numeric example, although I am using this function to check if part of a cell matches specific text...
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in Match: Lookup value = $E3 Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1 match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4 Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452 match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
I am working on a scheduling system and am trawling through some old notes of some two or three years. I came accross two formulas, which I must have used a while ago. they are a little more baffling that what I have recently used, but I must have referenced them for a reason. Normally I write down explanations of the formulas in blonde, so that I can understand their reasoning at a later date, however, I can find no reference to them. explain what they actually do in a logical fashion. I have been looking at this for four hours, filling in date ranges and cells that they refer to, but cannot make sense of them.
I would like to use a custom function or excel formula, if already exists, that finds the text "12 months" and then returns the value of the column that holds that value.
I have three columns of data in columns A,B,C - there are formulas in these columns that either produce a numeric value or return blank.
I have created Dynamic Named Ranges for each of them and have entered the following formula: =OFFSET(Working!$A$1,1,0,MATCH(1E+305,Working!$A:$A,1))
My issue is that this formula is looking one row past the last numeric entry in the column (a blank) which is leaving a blank space when I graph this data.
I use the SMALL function to sort the data in date order e.g: A6= =SMALL($A$2:$E$2,1) = 1/1/13, B6= =SMALL($A$2:$E$2,5) = 2/1/13, etc:
1-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13
I then use the INDEX & MATCH functions to place the names under the sorted dates e.g. A7= =INDEX($A$1:$E$1,MATCH(A6,$A$2:$E$2,0)) = "Dan", B7= =INDEX($A$1:$E$1,MATCH(B6,$A$2:$E$2,0)) = "Ed", etc:
The problem I have with this formula is that if there is a duplicate date (i.e. 3/1/13), the INDEX/MATCH formula returns a second "Aaron" rather than "Con".
How to modify this formula so it will return "Con" and not a second "Aaron"? Please note that duplicate dates may recur across the row.
The formulas always assume the data is in multiple rows rather than multiple columns.
I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.
Worksheet (functions!)
This one has a list of numbers formatted as general. (Column G)
Is actually a formula/macro that outputs a number... (didn't know if this mattered?)
As far as I can tell, the formula works just fine. If you look in the Name Manager and check on that Name, the highlighted box shows up exactly what it should and if you do a simple MAX test, it displays the correct MAX value from that range.
The problem comes in when I try and add it to a chart. Instead of a displaying all the values in the range, it displays nothing. Doesn't flag up any errors, just nothing.
The reason I chose to do it this way is that the table contents could change on a daily basis and so could the position of the row and I don't want to have to keep changing the references in the graph so make sure the right data is being displayed. Is it just that charts don't play well with the INDIRECT function?
I have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.
I tried a few of the index match max formulas I found here and could only get them to work with one column of data.
I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.
I tried using this array formula but it has an error in it and I can't figure out the error. =SUM(IF(Tracking!$C$2:$C$5000=$B$4,IF((Tracking!$D$2:$D$5000="15-M Eval")+(Tracking!$D$2:$D$5000="15-M TIC")+(Tracking!$D$2:$D$5000="Misd CRT"),IF(Tracking!$E$2:$E$5000=$A6,IF(Tracking!$J$2:$J$5000>=VALUE($B$1),IF(Tracking!$J$2:$J$5000<=V ALUE($B$2),1,0)))))) (I attached the workbook. In the CSTE worksheet, I didn't use control-shift-enter so you could see the formula.)
What I'm trying to do is come up with a formula that will sum if either of these three Legal Status types (15-M Eval, 15-M TIC, and Misd CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. The formula works if I just want to find one Legal Status type. I also will want to come up with a formula that will sum if either of these five Legal Status types (15/30-F Eval, 15-F TIC, 1/90 CRT, 2/90 CRT, and 180 CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. I tried researching if I could define a name that would sum these 3 Legal Status types but that didn't work either.
I got a 2 columns (A:B) with values in workbook1 (survey2.xlsm)
I want to open a sheet named according to values from column A wb1 in workbook2 (du_database2)
And I want to fill in the offset values (column B) in the aproppriate sheet
I wrote this sub for it. I am having troubles with the for each statements (how do I set them up properly?). The script itself hasn't been checked might also been wrong there.
VB: Sub copytest() 'by J Dim ws As Worksheet Workbooks.Open "survey2.xlsm" Application.Workbooks("du_database2.xlsm").Activate
I had the first and third argument working properly but am running into difficulty when I tried to enter the middle formula.
If A1 + B1 = 0 then "0" OR
If A1 = 0 AND B1 is greater than 0 then give B1's value times .01 If neither of those are true Then A1 minus B1, divide the result by A1 and then multiply it by negative 1.
=IF((A1+B1=0),("0%")*OR,IF((A1=0 and B1>0),(B1*0.01),((A1-B1)/AI*-1)))
I am having issues writing the VBA code for IF statements. I am essentially trying to nest IF statements within one another. I have it done through the regular If functions as seen in the attachment, but I cannot seem to get it to work with VBA.
I need it to work in VBA without knowing how many rows of data there are. In the attachment you will see the rules that I need the data follow, but here is an example. Essentially, depending upon how large cell C2 is, I need to add a certain amount of months to whatever is in E2.
I have a new job a part of which is where I have to provide reports using data from excel 2003 sheets that are sent to me by someone else. I add my own formulas to get the data I need. One such bit of data is. How many rows have "yes" in col E and "w" in range M:V?
I'm building a model that based on the start date (cell C7) needs to display the month of the project (M1, M2, M3, M4, ... M12) under the appropriate calendar month (Dec-07, Jan-08, etc.). The formula below works great (E4 is the calendar month Dec-07 in this case, and when I drag the formula horizontally it updates (F4, G4, etc.). B33 through B44 are my month references M1, M2, M3, etc.)
Here's the problem: The below statement contains seven nested ifs and Excel won't let me add additional if statements. How else can this be accomplished?
