Search And Compile Answers

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.

View 9 Replies


ADVERTISEMENT

Search Criteria With Multiple Answers

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.

View 1 Replies View Related

Search And Compile Multiple Spreadsheets

Jul 6, 2013

The company deals with Machine services, we maintain, service and brake-fix machine problems, the machines are manufactured my ourselves and there are approximately 15 different machines.

We have a Field Service spreadsheet that is filled in by all service engineers for every job they go on, this is filled in with information like:-

Serial Number
Machine Type
Hours Run Time
Reason for Visit
Reported Problem
etc, etc

All the forms are identical so all the fields are identical

Field Service Record Spares 2013 Master Rev4 (2).xlsx (this is the correct file). The forms are then saved on a central server folder and stored in month order.

Is there a way of creating a master excel sheet to gather all the information from all the Field Service spreadsheets which would then allow me to search all sheets by serial number or fault, without me having to copy and paste all the information into the master sheet??

We have 10 service engineers and each fills out 1 sheet per job per week, so a 4 week job will have 4 sheets for 1 engineer. an engineer could go on 5 jobs in 1 week and would therefore fill out 5 sheets. so we could be looking at in excess of 500 Field Service spreadsheet per year.

View 6 Replies View Related

If- Returning 2 Answers

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.

View 11 Replies View Related

Different Lists For Different Answers

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?

View 7 Replies View Related

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.

View 5 Replies View Related

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

View 3 Replies View Related

MAX Multiple Answers

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.

View 4 Replies View Related

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?

View 9 Replies View Related

Vlookup And Multiple Answers

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)," ")

View 9 Replies View Related

Mutilpe Answers To Cell

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.

View 9 Replies View Related

Multiple Answers And Message Box

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? ...

View 9 Replies View Related

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

View 9 Replies View Related

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‎

View 2 Replies View Related

Adding Multiple Answers To IF OR Formula?

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%

View 3 Replies View Related

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.

View 4 Replies View Related

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

Total Lbs/hr0.2 (answer)

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

View 3 Replies View Related

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.

View 3 Replies View Related

Method To Show Multiple Answers

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.

View 10 Replies View Related

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‎

View 9 Replies View Related

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?

View 2 Replies View Related

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...

View 4 Replies View Related

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.

View 9 Replies View Related

Check Multiple Choice Quiz Answers

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?

View 6 Replies View Related

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‎

View 4 Replies View Related

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] .....

View 2 Replies View Related

Compile Error ...

May 4, 2008

Can some see why this Code would fail intermittently?

This gets executed after a Commandbutton is selected.

The Error Message is attached.

View 14 Replies View Related

Compile Errors

Apr 4, 2008

I've been using some code I wrote at the start of March to open some files that I've created earlier in the day in order to add to them.

The code worked fine until the month changed. Here is the code I have to open the file I need.

strdate = Format(Now, "dd-mmmm-yyyy")

ChDir "C:DesktopTodays ReportsReports " & strdate & ""
Workbooks.Open Filename:= _
"C:\DesktopTodays ReportsReports " & strdate & "Date Report " & strdate & ".xls"

Since April 1st I have been getting a compile error saying I have the "wrong number of arguments or invalid property assignment".

I am at a loss as to why the code doesn't wok as nothing chaged between March 31st and April 1st.

View 9 Replies View Related

Compile Error: For Without Next ..

Sep 14, 2009

I am using the following code to put a combo box in my userform. When I try to run it I get the following Compile Error: For without next. What does it mean and how do I fix it?

View 9 Replies View Related

Compile Error: For Without Next

May 2, 2008

just make some files to make my work easier. Few too many watching the game last night, and I think im lost here. Am I attempting to do too much here ? I ran the first For statement Fine, but I cant get the second to work. I dont understand where my next statement should be entered.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved