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