Jun 26, 2008

I have a table whereby I Vlookup a different spreadsheet on an order number (in this instance Z011352/001). There are multiple sketch numbers associated to this Z number.

I would like to be able to input the Z number and then obviously the Vlookup pulls up the first sketch number. This works fine.

If I then input the same Z number again it pulls up the same sketch number, but I want it to look in the current table (the one i'm entering the Z number onto), see if the sketch number is already present and if it is pull up the next sketch number in the list on the separeate spreadhseet. (I hope this is clear enough)

My Vlookup code is:

=IF(C1070"",VLOOKUP(C1070,'[CrossingsSchedule.xls]Update Worksheet'!\$E\$1:\$AC\$10000,20,FALSE)," ")

Return A Count From Multiple Columns - Each Column Has Multiple Answers

Sep 28, 2012

Want a single count of multiple columns based on the columns selected value. Data is in text format.

Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?

In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.

Sample data:

Pegnancy Smoke
Pregnancy Alcohol
Pregnancy Marijuana
Pregnancy Powder
Stress Cigarettes
Stress Marijuana
Stress Alcohol
Stress Medication

[Code] .....

May 8, 2006

here's the formula that I'm using: =INDEX(\$C\$35:\$AF\$35,MATCH(MAX(C41:AF41),C41:AF41,0 )) where \$C\$35:\$AF\$35 contains names of people & C41:AF41 contains #'s. This works great if there isn't a tie. Is there a formula to search the range, find the max # and if there are two answers display both names. ex. the max# is 3 and both joe and sam have 3. it would then show joe / sam or something like that. Is this possible? If not, then if there is a tie for the max #, to have the cell just display tie.

Jan 12, 2009

I have a message box in my spreadsheet. I want it so if the user puts in multiple answers that certain message box appears, if they put in a different set of multiple answers it shows up. For example, the code below is what I am using now. It says that if the user types 5225 in C16 and 3000 in C17 then a specific msg box appears. I want it to say if the user types in 5225 or 5226 or 5227 the same msg box appears but if they type in 5665 a different msg box appears. How would I change this code? ...

Count Certain Phrase When There Is Multiple Answers

Jun 4, 2014

In the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.

HRA Results.xlsx‎

May 6, 2014

I am using the formula =IF(OR(E2="COLD"),"33%")

Which changes my cell to show the text 33% if the text cold is entered into cell E2. Now what I would like to know, is if I can add multiple catch words to give alternate pre defined percentages. Such as warm and hot to give the respective answers as 66% and 99%

Apr 9, 2012

On my spreadsheet i want to find the results from 2 criteria that i entered.

My search criteria are "Oostbos" and "AA8", and excel has to find this from another spreadsheet that i made for rostering.

OostbosN3 evelineAA8N3 evelineAA8N2 MargaAA7

The problem is that i have multiple shifts with the "AA8" criteria, but my function only finds the first one.

I used the following function:

Code:
=IF(C8="";"";INDEX('Afdeling PG PH'!\$A\$6:\$A\$28;MATCH(C8&\$A\$7;'Afdeling PG PH'!\$B\$6:\$B\$20&'Afdeling PG PH'!\$AI\$6:\$AI\$20;0)))

Also when the AA8 cel is empty that i doesn't show anything.

How the second N3 eveline, shows the 2nd result and so on.

Jan 13, 2009

I am currently maintaining a database that keeps up to date records of employees in my company and their vacations including their nationality dept. etc. for the vacation reports i have a "last day of work," "return to work date," "Actual Return to work date," and at the end a "remarks" column,

Moreover I need to report how many employees per department/Discipline are on leave ex. ( mechanical, electrical, and so on.) That I did using countifs having whoever is remarked as "na" vs. actual return date, Discipline vs. each discipline. All works fine but what i want to ask is there anyway that i can list the names of employees that are on vacation under each discipline?? Ie. if 3 are in the electrical engineering department, can i list their names? or if Today()>Actual Return to Work day (ie they are late and have not arrived yet) is there a way that it can list the names of multiple employees? rather than having to work against each name etc.

Lookup Formula To Pull Multiple Answers To A Row

Jul 2, 2014

Instead of trying to explain my challenge, the attached workbook should be self explanatory. My answer is surrounded by the box. I need a formula that would automatically provide this output.

Lookup Scenario.xlsx‎

Index / Match And Concatenate On Multiple Answers

Mar 4, 2014

I have 2 worksheets, Worksheet 1 has Customer Magic Number on it as a reference and a few customer details and Worksheet 2 has Customer magic number and contact fields.

I am currently using the formula

=INDEX(\$C\$3:\$C\$575, SMALL(IF(N2=\$D\$3:\$D\$575, ROW(\$D\$3:\$D\$575)-MIN(ROW(\$D\$3:\$D\$575))+1, ""), COLUMN(A1)))

to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?

Mar 28, 2008

I have a table in excel that i need to use to mark a 20 question quiz. I have the correct answers in one column and the students answers in the next column. I want to mark a correct answer with 1 and an incorrect answer with 0 marks. I know how to use IF, however the students answers can vary eg, correct answer could be B and D but the student writes B + D. Is there any way of marking this correct even tho it is not exactly written out correct?

Aug 28, 2008

I have a sheet with this:

cell a1 = numbers from 1 to 10 or nothing
cell b1 = TO or OFF depending on cell a1
cell c1 = numbers from 1 to 10 or nothing

Now I have a formula in cell b1 that detects if theres text in cell a1.
IF A1 = nothing, then b1 = off, else b1 = to.

What I want to achieve is to do 2 things on the IF.

If a1 = nothing, then c1 = nothing and b1 = off. else, b1 = to.

how can I do this?

I looked everywhere and no one speaks of double answers on IF.

Dec 3, 2008

Cell C7 is a drop down list with 3 possibles (Consumer,Commercial, Public). Depending on which is chosen, I want Cell C10 to provide a different list of possibles (Consumer Products, Commercial Products, Public Products).

I have tried to create a custom IF statement with the list choice & sometimes it refuses & sometimes it gives me the list, but with the formula string as the drop down options.

Is this forumula possible or is it beyond excel's capability?

Consolidate Y Answers To One Row

May 16, 2013

I have a spread sheet that looks like the following. I need to take all of the Y answers and make one line.

Client
ProductA
ProductB
ProductC
ProductD
ProductE

ABC Inc
N
Y
N
Y
N

[Code] ......

If any of the Product columns had a Y in the row, the line would get the Y. If not, an N.

IF OR Formulas With 7 Answers

Apr 26, 2006

I am trying to achieve the following in one formula.

IF an amount is greater than 0 and less than 500,000 put in 490
IF an amount is greater than 500,000 and less than 1,000,000 put in 1000
IF an amount is greater than 1,000,000 and less than 4,000,000 put in 2205
IF an amount is greater than 4,000,000 and less than 9,000,000 put in 5000
IF an amount is greater than 9,000,000 and less than 10,000,000 put in 6500
IF an amount is greater than 10,000,000 and less than 15,000,000 put in 7500
IF an amount is greater than 15,000,000 put in 9500

Different Answers In Excel Than Calculator?

Jan 18, 2010

I have created Excel spreasheets for our payroll. The answers on my calculator are sometimes different than my spreadsheet. How can I make them tie?

Oct 25, 2007

I have a workbook filled with many tabs / sheets. Each sheet contains the same structure of information and isn't an important aspect of this question.

I've looked at this topic http://www.mrexcel.com/board2/viewtopic.php?t=16683 and I'm unable to follow it well.

Is there a way to search all the tabs for a word and if it matches to copy the entire row into the search tab / sheet? Specifically for each and every searched match.

It would save my users trying to search each tab individually and possibly miss the data they are actually looking for. Hundreds of people use this reference workbook which stores where all our documentation is stored across numerous servers. Each row is one document and its corresponding storage information.

Dec 12, 2008

How would I write out a formula if I currently have this formula/ =COUNTIF(D6:Q6,"a")*8 on cell S6 but I also want to include/ =COUNTIF(D6:Q6,"a-1")*1 and =COUNTIF(D6:Q6,"a-2")*2 and so on till a-7 and i want all this to read on the same cell.

If Statement With 4 Diffent Answers

Sep 3, 2009

This is my current formula that im using in cell U6

=IF(L6>=500,"Sept 8 2009","Sept 1 2009")

it works fine with just 2 answer cuz if its not one answer is another

=IF(AND(\$B\$2>=0,\$B\$2>=500),"Sept 8 2009","Sept 1 2009")
found this one online but it got frustrating...i change the values to mine

so what im looking for is a if formula or similar to the following

if L6 is between 0-500 i want U6 to say Sept 1 2009
if L6 is between 501-750 i want U6 to say Sept 8 2009
if L6 is between 751-999 i want U6 to say Sept 15 2009
if L6 is between 1000-1200 i want U6 to say Sept 22 2009

i try both if formulas (stated above) both but both return one or more results...i just want one

so....Example

L6 U6
***(decimal included)
0.00 than i would get the result Sept 1 2009
253.52 than i would get the result Sept 1 2009
647.36 than i would get the result Sept 8 2009
750.01 than i would get the result Sept 15 2009
1100.00 than i would get the result Sept 22 2009

if above 1200 i would get the result sorry your amount owed is to high

formulas are preferred *but will work with macros and other options

*if using macros or vba or what have you please explain how it will work

Calculating A Number From Answers To An Audit

Dec 18, 2008

I have created a workbook that I store data from my audits, this data is in the form of Y if compliant, N if noncompliant and N/A if not applicable. Where the fun part begins is that each question has a different risk involved. I have used a simple 1 to 5 risk scores and given scores for compliance and non compliance to each score, for example a risk 1 if compliant is 100 points, if non compliant is -100 points, all the N/As are worth 0.

I currently calculate the totals in a different sheet in the work book, but I do this kind of manually, I have calculations to work out the totals and percentages and all that, but I cannot figure out how to get the Ys and Ns to appear in this sheet as 100 or -100. All I do at the moment is bring the Y, N or N/A over with a simple =corresponding cell in sheet 1 then manually change this to the number I require.

Sum A Table Without Putting Individual Answers On Each Row

Apr 22, 2009

Would like to sum a table without putting individual answers on each row and summing column. The table can consists of 100's of rows but only 3 columns.

lbsratehrs
Item 1100.10
Item 250.525
Item 3200.2550

Find lbs/hr for each item and sum all items lbs/hr
(lbs*rate)/hrs

Tabulating Correct Answers To A Test?

Apr 24, 2012

I would like to tabulate correct answers on a test given. The data is in excel as student answer (column header), with possible response of 0 or 1. Another column of Answer Key with response of 0 or 1. There are 80 total questions (rows).

I would like to be able to create another column of "Grade" with 0 (incorrect) or 1 (correct) so that I can tabulate % correct responses. I will eventually be analyzing correct responses by question, combining results with other students at same level, etc.

I will likely do further analysis in SAS, but want to get the data set together in Excel.

Building A Lookup Table With 3 Variables/3 Answers

Mar 25, 2009

I'm trying to figure out how best to build a table to do:

IF A1="yes", "no", "na" and
IF B1="yes", "no","na" and
IF C1="yes","no","na" then
"AT" or "IC" or "VE"

So, I need a table that can look up responses to three questions, then lookup the corresponding answer (AT, IC or VE). Some of the combinations of anwers will yield the same anwer (i.e: yes, yes, yes=AT and yes,yes,no=also AT). I'm trying to come up with the most probable answer by evaluating these three questions; Thus; 10 combinations will yield an answer of "AT", 10 combo will yield "IC" and 7 combo will yield "VE". I've done this with 2 variables, but can't seem to get it to work with three...

Generate List Of Combinations - 20 Questions With 3 Answers

Feb 26, 2011

How to generate a list of numbers of this function in Excel: COMBIN(20,3), but i want the combinations of numbers. I want to create a list of 20 questions and every question has 3 answer. I think that are 1140 combinations.

VLookup - Single Value Lookup Returning Multiple Records Into Multiple Columns

Feb 7, 2014

Certification and Training tracking.xlsx

I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.

What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.

Pivot Table - Displaying Count Of Data In A Column With Y/N Answers

Dec 16, 2013

I am new to Pivot Tables and I am having difficulty displaying a count of data in a column with Y/N answers.

Previously I would have undertaken this using the SumProduct function in a standard table.

I attach an example workbook with my data, what I want it to look like and the pivot I have created.

Book1.xlsx‎

Create Multiple IF Statement And Multiple VLOOKUP Within Same Formula

Aug 11, 2014

I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.

This is as far as I have got but even this won't work:

=IF(I15="BUTT CUT",=VLOOKUP(J15,V13:Z22,2,FALSE))

VLOOKUP For Multiple Ranges In Multiple Columns

Feb 21, 2013

Im trying to make a vlookup so I can say find a record where column A is >4, B is =2, C is 1.

Example - The lookup would find these 2 ranges.

7
2
3

2
6
4

[code]....

Multiple Ccolums/rows To Get Data From Multiple Columns/rows (vlookup)

Jan 15, 2010

I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............

VLOOKUP With Multiple Wks

Mar 12, 2007

I have a spreadsheet with 5 worksheets. I want VLookup to read the City and State in the two columns next to the County Column in the custmast worksheet. Compare the city and state to the worksheets AL, FL and GA worksheets and return the correct County and Territory Code found in the Territory worksheet in the County Column and Territory Column in custmast worksheet. I've attached a file for better understanding.