Return Formula Value/Text Based On Many Cell Conditions
Oct 16, 2006
This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses)
Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses
Is there a formula/function that i can use (like the IF Formula) to complete the following information;
EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO"
Column H lists all departments
Column A lists deaprtments
A3 representing the 1st Department needing training
View 8 Replies
ADVERTISEMENT
May 20, 2007
I need to write a formula that will Display “Big Fish” if a customer is in the top 100 sales or ‘Not that great’ if the customer is not in the top 100 sales for the customer whose CUSTOMERID is equal to my ID.
An updated Spreadsheet can be found here
[url]
OR
[url]
View 9 Replies
View Related
Apr 1, 2006
I need a function that will use a column of text values and test these values
to see if one or more of the values exist in a single cell. If it does I need
the function to return true or false.
Ie. cell A1 contains the text "Jim Smith" the B column contains the test
names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
Because Jim is in the cell A1 I would need the function in C1 to return the
value "true". If A1 contained the text "bob smith" then function in C1 would
return the value "false".
View 10 Replies
View Related
Oct 10, 2009
Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?
View 14 Replies
View Related
Nov 7, 2006
I have very limited vb experience and I believe it’s the only way I can do what I need to get done. I am working on creating an invoice template. I want the price to populate based on two conditions. The two conditions are invoice line quantity and month. Let me provide data ranges. The user will enter a quantity for each invoice line moving down B17, B18, B19, B20 etc up to 24 lines. (through B40). The user will enter a current date in F13. Away from the main body of the invoice:
Range T5 thru T16 will be pre-populated with each month of year 2007. So T5 = 1/1/2007, T6 = 2/1/2007, T7 = 3/1/2007 etc.
Range U5 – U16 will be pre-populated with the line price for each month if the line quantity is less than 10
Range V5 – V16 will be pre-populated with the line price for each month if the line quantity is 10 or more
The price should autopopulate in G17 - G40 where a quantity is placed in the corresponding column range B17 - B40. The number of lines varies per invoice so I'm setting the template up with sufficient lines. As an example, the price should populate for each invoice line beginning in G17 to correspond to the month entered in F13 and quantity entered in B17 based on the corresponding month in T5-T16 and the price in either U5-U16 or V5-V16 given the quantity. So if the Month in F13 was February and the quantity in B17 was 25, the price that populates G17 would come from V6. If the Month in F13 is March and quantity in B17 is 5, the price that populates G17 would be from U7. I just don’t have enough experience to write this myself.
View 7 Replies
View Related
Aug 22, 2013
how to return values based on some conditions.
So for the sake of this example i have two cells lets call them J1 & J2 , one with the client name and another with State (matches the table name)
How can i get it so when i type in say Client 1 & Table 3 it will return the information in client 1s column into table 1s rate column. Obviously i want to be able to type in any clients name / table and have the same thing happen.
the red is what i want returned into table 1s rate column
Table 1
Table 1
Table 2
Table 2
[Code].....
View 1 Replies
View Related
Jul 26, 2014
Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?
View 4 Replies
View Related
Apr 23, 2008
I am trying to return a cell address range based on values in different columns. Attached is an example.
In Column A1 I have "Range" and below that a set of numbers from 1 to 31.
In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.
What I want to do is as follows:-
I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.
I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.
View 9 Replies
View Related
Apr 30, 2009
I have a cell with a %. I want to return text in another cell based on that data i.e. between 90 - 100 = excellent, 75 - 90 = good, or something like that.
View 3 Replies
View Related
Oct 26, 2007
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities.
In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario:
Warranty Replacement = 20
Insurance Claim = 20
Billing Issues = 15
Retention Opportunities = 20
View 5 Replies
View Related
May 2, 2008
I am attempting to choose a cell containing a text string based on information placed in a raw data area.
My output from this formula will be a line of text picked from yet another reference cell, based on each of the four choices.
Removed non pertinent information and edited for clarity.
View 3 Replies
View Related
Jun 1, 2008
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.
1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.
2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
View 4 Replies
View Related
Sep 6, 2013
Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?
View 9 Replies
View Related
Jun 3, 2006
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case).
I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
View 7 Replies
View Related
Sep 27, 2006
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
View 9 Replies
View Related
Dec 21, 2008
I am trying to write a formula that will return a statement if a certain month is contained in the text within another cell. Formula is =IF(ISERROR(SEARCH("Dec",Assumptions!B2)),"Ensure Journal is Non Reversing","")
Cell B2 contains a date in the format of Dec 08, so if this date contains Dec, then return "Ensure Journal is Non Reversing", if it doesn't then leave the cell blank.
At the moment it is putting in the first test for every month I select and not changing to blank.
View 2 Replies
View Related
Jun 17, 2009
I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes.
A B C E F G
M S Get value from G M S 5G
M T M T 6M
M U M U 7H
M V M V 8K
View 9 Replies
View Related
Jan 6, 2014
Current simplified situation:
Department A
Employee_1
Employee_2
Employee_3
(empty cell)
Department B
Employee_4
Employee_5
Department C
Employee_6
Employee_7
Employee_8
Employee_9
Employee_10
With the VLOOKUP function, I have looked up the row numbers of the departments. Subsequently, I'd like to find the last employee of that department, based on the department's name and/or the department's row number.
A department-typed cell is non-empty and never contains an underscore. An employee-typed cell is non-empty and it always contains an underscore.
The simple and non-sufficing formula is the VLOOKUP of the row number of next department minus one. But with adding, moving and subtracting departments rapidly, this is not an option.
View 4 Replies
View Related
May 17, 2009
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View 5 Replies
View Related
Mar 7, 2012
I am trying to write a Sumif formula based on two conditions. I want to sum the data in column AK if the data in column D = BUD12 and the data in column E = US Custom. I have written the following formula but am getting a #VALUE error.
=SUM(IF($D$25:$D$700="BUD12",IF($E$25:$E$700="US Custom",$AK$25:$AK$700,0),0))
View 6 Replies
View Related
Jan 9, 2014
I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.
The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.
1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.
The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.
Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.
View 3 Replies
View Related
Aug 25, 2009
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking.
For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05)
The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
View 5 Replies
View Related
Apr 4, 2014
I am trying to use sumif formula to add sales forecast based on three conditions but i also want to add the revenue for current month which i have but for the next one months as well as two months plus.. this will change based on the current month.. below is what I am using for the current month..
=IF($B$3=Reference_Data!E2,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
IF($B$3=Reference_Data!E3,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
IF($B$3=Reference_Data!E4,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
[Code] ...........
View 2 Replies
View Related
Oct 15, 2007
I am trying to Lookup a country to see if it is classified as a "Developed Country".
My formula would be in cell AA4. I want to see if the country in cell B4 is on a list of developed country's on another worksheet. IF the country is found on that list cell AA4 displays Developed. If the country is not on the list, cell AA4 displays Emerging.
I have tried an IF statement using the Match function and it does not work.
=IF(MATCH(B4,'Developed Country List'!$A$2:$A$37,0), "Developed", "Emerging")
View 4 Replies
View Related
Aug 22, 2014
I do data entry for a driveway company. I get addresses and phone numbers, and then call the people to offer services. I keep track of my calls on the spreadsheet, color coded for my results, green for leads, yellow for voicemail/no answer, and red for not interested.
I have formulas in cells M2 through M8 to give me my overall stats for the calls I have made, it filters them by color for leads, missed calls, and rejections, and a couple other stats (mostly for my own curiosity)
What I'm trying to add is the cells L10 to M13, I want to be able to input a date, for instance 8/15, and have it run the same stats, just for that day essentially. So, I need it to search the last 2 columns in the table for the text that I input into cell M10, and then run the color based formula on those cells, and total them up in the appropriate cells, M11, M12, and M13.
The 2 Stats tables are the same at the moment in the sample, as I only included one particular street that I have mapped, my actual table is nearly 3000 rows.
View 3 Replies
View Related
Jan 29, 2014
I am trying to put together a report tracking sheet, ie I have different test that take a different length of time to return to the office and I want to be able to see when a test should be back from the lab,ie:
A blood sample takes 1 day, a urine sample takes 3 days.
I need a formula that basically says that if the data in cell A1 = Blood, (and I have the test date in cell A2) then the data in cell A3 should says A1=Blood, A3=A2+1 to give me the test due date
I found the following formula in one of the other posts;
=IF(E5="BLOOD",G5+1,IF(E5="URINE",G5+3,0))
The problem that this will only work with two tests and I have more than two tests, I have fourteen and each takes a different length of time to process.
View 7 Replies
View Related
Jan 4, 2013
I am creating a document log that tracks all excel files sent and received.
I use RDBmerge to get the filenames and data from the file batches.
My current macro edits the data down to the last stage of data needed to create the log.
My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"
Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.
I have highlight the cell range on the "Final_Data" Sheet that I need to complete.
filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.
The criteria is this:
A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")
I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.
i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method
[Code] .....
I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.
View 9 Replies
View Related
Aug 5, 2009
I want to highlight A1 red if C1 is greater than 55. Is this possible?
View 3 Replies
View Related
Feb 23, 2010
I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10.
I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.
View 7 Replies
View Related
Mar 10, 2009
I am trying to build a spreadsheet that tallies hours worked and hours available for the next day. The key to this spreadsheet is the number 70. A worker can accumulate no more then 70 hours in an 8 day period, however after any day with no hours worked the rule resets to 70 hours available again. I have attached a sample spreadsheet so you can see how this should work.
View 9 Replies
View Related
