I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and
end of a code is indicated in the CONT. column. (Start of first code = S1,
end of first Code = F1, Start of second code = S2, end of second Code = F2,
etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start
& Finish Codes. Two conditions need to be met before the length (in DIST
column) is to be calculated, the Report numbers and the Codes must be the
same for both rows.
In the table above:
- Code B in report 100 would go for a distance of 7 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).
This is a complicated thing I'm trying to accomplish and I only have a rudimentary grasp of Excel formulas and logical statements.
I need to check BE2 if it's blank. If it's not blank, leave it alone. If it is blank, I need to check S2. If S2 contains the name of 1 of 11 counties (ex.: "Marion","Pinellas","Orange","Osceola", etc.), I need BE2 to say "TBD". If it does not say one of those 11 counties, it needs to say "N/A".
I'm trying to create a macro that allows a date to be entered into a Dialog box that is activated by a short cut key.
The macro would use this date to look through all the information on tab Output, and if(as) it finds dates that match the information will then be filled out on tab Schedule.
For clarification Work Center would be the abbreviation (letter code) about the dates.
I'd like the following functionality in a spreadsheet I'm working on. Can a function/code/whatever be written to accomplish this? I know that the format below won't work; I simply wrote it that way to illustrate what I want.
If G6 = S, THEN B3+H6 and display total in S3; If G6 =V, THEN C3+H6 and display total in T3; If G6 = P, THEN D3+H6 and display total in U3. I have no idea how to do this myself (even after researching various sites) and I do not have the ability to write create VBA myself.
I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.
I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet
This is what happens if working right =
look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)
Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)
Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age ------------------- Dog - Rover - 3 Goldfish - Tom - 1 Gerbil - Chewit - 4
I am trying to do a lookup from the price list based on the below data file, my problem is I need the price that falls within the qty range from the price list, the price list is not consistent and changes with every item. I would like one formula that is sophisticated enough to work no matter where the qty range lies.
I need to add a Bloomberg BDP function to my lookup statement. The formula that i'm trying to replicate in VBA is =IF(ISBLANK(h23,"",BDP(H23&" cusip","long_comp_name")). update my macro.
How the Lookup will break down within an IF statement?
Original
HTML Code: 'ActiveCell.FormulaR1C1 = _ "=IF(R[-1]C[-2]"""",LOOKUP(1000,SEARCH({""Principal"",""Foreclosure"",""Interest"",""Premium"",""Discount""},RC[-1]),{1,1,2,2,2}),"""")"
Non-Working If statement. I realize that the Lookup isn't currently here. Including it kind of defeats the purpost of breaking this out into IF's, doesn't it?
HTML Code: If MyFirstCell.Offset(0, -2) "" Then MyFirstCell.Offset(0, -1) = Search("Principal") MyFirstCell = 1 ElseIf MyFirstCell.Offset(0, -1) = Search("Foreclosure") Then MyFirstCell = 1
I want to lookup a value from cell C4 in 'workbook B' if the value in cell G3 of 'workbook A' equals "Yes". If the value of cell G3 in 'Workbook A' equals "No" I need the value to be 0.
Here the situation I have a list of around 2000 hyperlinks from my workbook and its various sheets. I have a pre made list of all the hyper links that there should be unfortunelty you know how things go and Im off by some here and there. What I need to figure out is how to match up my hyper link list to my pre made list. Im thinking an if statement looking for keys (truck #, Trailer #, Date) to get me the closest matches I can without having to go through every single hyper link....
Im thinking i need to match the criteria (truck #, Trailer #, Date) to that of the data i got from the hyper links. Ill explain more on my attached work sheet so it is more clear to under stand.....
I have a very long function I created. But I need to edit it in a matter I have never done before.
Below is my function and i will like to add a new condition to it.
IF the text value in cell C7 is equal to any text value in Workbook2 B10:K10 and the value underneath that cell (I.e if B10 then B11 etc..) is Deleted then I want to have one of the conditions of my function below display "Deleted".
I have three worksheets the first one called Econ the second one called CheckLogs and the third is a customer sheet. I am needing to match up a cell from the Econ sheet with a cell from the Checklogs sheet and then it matches a cell from the customer sheet with a cell from the checklogs to determine what customer sheet. My problem is how do I loop through the customer code cell until it finds the correct cell.
so in the formula "B1" is located on the customer sheet and is the customer code field, "CheckLogs!B2" is on the CheckLogs sheet and is the customer code field. I'm just not sure how you loop if the customer code in B1 doesn't equal the customer code in CheckLogs!B2 and run through the formula again.
I'm trying to use index match with IF statment. I understand how this works just not quite sure how to get a 'True' return based on 'logical_test' portion of the IF function. I want a TRUE return when column H starts with 'hbap.adroot.hsbc/gsc' or alternativly just contains the letters 'GSC'. If column H contain 'GSC' Column A will output 'GR' not HBAP'
I have multiple sheets within one Excel file. Each sheet is structured the same way. Column B lists Client's last names, which differ from sheet to sheet, though some clients may be repeated in several tabs. I have a column K which displays either the number 1 or 0. This is consistent between sheets.
I have created a formula that will look at the last name in a specific cell in column B of sheet 2 (FY 07) and search sheet 1 (FY 06) column B for the same last name.
=IF(MATCH($B5,'FY 06'!$B$5:$B$24,0),1,0)
If the same last name exists and if column K of sheet 2 and sheet 1 display a "1", I want it to place a 0 in the formula cell.
So, if there is a client that is in both sheets and has a "1" in both columns K of each sheet, I want the formula to let me know.
I have a list of 31 values in separate cells. The values are three digit letter codes. Here is an example of 5 of them:
ATL GMI WOB COI PTS
What I am wanting to do is have a Conditional Format to change the color of the cell to Pale Blue if the value equals another cell that is within a range.
1. Pupils have a target grade 2. Pupils can either do a higher or foundation Tier exam depending on their ability 3. Pupils do 9 modules and the grade bounderies are different for each module. 3. I use bar charts to report the results back to pupils and parents. Instead of just telling them you got 50% D your Target is C. I also want the target grade to be converted to a mark. I do not want to create another woorksheet inorder to use Vlookup. 4. I now know that Index and Match can do the trick of reading the left columns. But I cannot get them to first consider the Tier for each student.
I was thinking of usind IF
IF Tier is Higher then Index the Higher Table and Match else Index Foundation Table and Match to get the mark. But I do not know how to express this in a way that's readable by Excel
I am trying to match up two sheets and I the match criteria is based on the data from columns B, C, D, E, and G. Sheet 1 contains ~20,000 line items of data. Sheet 2 contains ~250 line items. I am comparing the info in sheet2 vs. the data in sheet1. Both sheets are formatted the same.
For each row in sheet2, I need to compare against all rows in sheet1. For example; for the first row in sheet2, determine if there is a row in sheet1 that contains the exact same data for columns B, D, E, and G (all must be true) and the data in column C is False. Examples:
Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%
When I set up a CLASSIC Conditional Formatting using a formula (="If($BJ$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?
I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.
I have a two sets of data, both which I have calculated variances plan Vs Actual, I would like to compare the previous months variances of different sheets. I have put a sort on the variances in descending order.
I want to match two fields E.g. B1 & D1 in sheet1 AND if they match then bring back L1 data in sheet2? The both sheets have same column names and in same order.
I am looking for a fourmla to return a value in a column next to the closest value. Yes, this would normally be a vlookup example however in my application the numbers will not match exact as such in an client lookup application. I need to find the closest value to the value compared in the range and return the value to the right of it.
I have made up a small table attached with comparing what vlookup gives me and what I am looking for.
I am setting up a spreadsheet, that will have multiple sheets for each branch, this will than be referencing back to another sheet for data. As i will be replicating the sheets I want to make the formulas as easy as possible so I dont have to change them in every sheet. I have attached an example, the data sheet will have a table for each month, the remaining sheets will be each branch, ideally I would like to have a formula that looks up the data sheet and returns a value for each month and then be able to replicate the formula across each sheet. As the formula is looking at 3 different areas (date, branch and mortgage balance) i have tried using idex and match, but are not having any success.
I am trying to create a lookup table that will lookup parts of a colum and return the second column in the table. For example, column A has a lot of different titles in it, they each contain different segments and codes in them but I need to see if NM is in the list and if it is I want to return a 1.
Column A Lookup COLUMN Lookup Result NM01A NM 01 NM02B IN 02 IN01A IN02B
I need to see if what is in the lookup column is in column A, and if it is then I need to return the lookup result.
2 worksheets. The first has the raw data and I want to drag some stats onto the second.
I'm trying to make it look in one column on first sheet and identify matches for a range of numbers (say 100 to 199), then where there's a match, look across into another column and count instances of specific inputs (A). ie, 114 has A in column G as does 119 and 199, 115 has B = count will be 3 -
My attempts at stringing combinations of match, IF, Countif and lookup/vlookups have, frankly, been a tad pathetic!
I have exported an income statement report from the ERP system to Excel. Only items that are not equal to zero are populated. So if there has been no expense in the Central division for Consulting fees, it does not show up on the report. Here is the formula:use control shift and enter
A 2 East Division 3 Sales 4 Cost of Sales 5 Payroll 6 Lights 7 Consulting Fees 8 Net Profit 9 10 Central Division 11 Sales 12 Cost of Sales 13 Payroll 14 Lights 15 Net Profit 16 17 West Division 18 Sales 19 Cost of Sales 20 Payroll 21 Lights 22 Consulting Fees 23 Net Profit
Excel tables to the web >> Excel Jeanie HTML 4 So this formula picks up the first Consulting Fees after the Central Division. The problem is it is going to pick it up under the West Division. So, how would I modify the formula so that if the row number is also less than Central Division Net Profit (A15), it is zero. (no offset or indirect)
i'm trying to figure out how i could get the sumproducts or anyway to get this forumla work basically i'm trying to have the cost column to vary depending on the variable set on column d & e on the top basically ...
* if d7 is between d2 and e2 use cost 1 for the multiplication to generate H1. * if d7 is between d3 and e3 use cost 2 for the multiplication to generate H2.
