I have a set of 4 multiple choice answers B,C,D,E column "F" lists a # 1-4 which lets you know which column letter (b,c,d,e) the correct answer is in. I want to set up a formula that will copy the contents of the correct answer (based on that # identifier) and copy it into column "A" which is currently blank.
View 7 RepliesHere's an example of what i want to do
A_________ B Frank Sinatra C Sammy Davis D Dean Martin E Joey Bishop F 3
I want to put "D"'s value 'Dean Martin' in column A - i know it belongs in A because the "3" in column F indicates its the 3rd answer listed i.e Dean Martin. This format would be the same where there are 4 possible answers on the column to the right dictates which answer is correct.
when i choose material from my combobox Options (cboTM), i wanted, only the textboxes regarding to the sheet material unlocked, and the others locked with the color of the form, and the same for the other options like worklabor and equipments. i could blocked for material with this code :
[Code] .....
The prob is, worklabor and equipments will be blocked too, and i dont know how to put correct info on the textboxes.
Attached File : teste1.zip
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
I am using this formula for long now
=SUMPRODUCT(('[Diesel Statement.xlsx]Nov 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Nov 12'!$G$2:$G$251)
every month i just add a new sheet and change the month name in the formula to that eg
I changed it to
=SUMPRODUCT(('[Diesel Statement.xlsx]Dec 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Dec 12'!$G$2:$G$251)
but now it is not calculating the answer is coming as #value!.
Then I tried to add up by
=VLOOKUP($H$1,'[Diesel Statement.xlsx]Dec 12'!$C$2:$G$215,5,TRUE)
but it does not add all the entries only one entry is shown.
I want the total of the column G. looking up for values in C = H1 value
I have a workbook with calculations for a sale less the assorted fees and at the end giving the final amount from a sale.
I have noticed that some of the rows are not giving the correct amount in them.
In other words the addition of some columns in that row are not adding up correctly. It is only off by 1 cent (either over or under), but I can't figure out why.
I have the feeling that I am going to want to kick myself when someone explains this to me (I just know that I know the answer but for the life of me I can't right now).
How can I get this to read each, (if (and statement) and give the correct answer. And not give me the 1st underlined statement answer in cell D10. If that also equals true.
But if any other cells G11 thru G14 equals OUT. It still will give the 1st statement.
I need the answer to be the only one of them correctly that’s equal true.
I know that the 1st underlined statement = true. Because G15= OUT .....
I have 2 worksheets (worksheet 2 is Slicing and Worksheet 1 is Production).
Production worksheet D6 has a dropdown box for, 1, 2, 3, 4, 5, 6, 7, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6 and 7.7.
In the Production worksheet, I have in cell E6 =IF(D6=1,"ONE",IF(D6=2,"TWO",IF(D6=3,"THREE",IF(D6=4,"FOUR",IF(D6=5,"FIVE",IF(D6=6,"SIX",IF(D6=7,"SEVEN")))))))
In the Production worksheet, I have in cell F6 =IF(D6=1.1,"ONE",IF(D6=2.2,"TWO",IF(D6=3.3,"THREE",IF(D6=4.4,"FOUR",IF(D6=5.5,"FIVE",IF(D6=6.6,"SIX",IF(D6=7.7,"SEVEN")))))))
How do I show in Slicing worksheet cell B6, either 'ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX', 'SEVEN' as i have the formula =PRODUCTION!E6&PRODUCTION!F6 and the cell shows '
upon opening my spreadsheet which will only open if the correct answer/ password is typed.
I’d need to store the questions, passwords/ answers somewhere for VB to look up I guess??
Capital of England – London
Capital of France – Paris
Capital of Italy – Rome
Etc etc
And then I need coding which will open one of the random questions, which will only grant access to the spreadsheet if the correct answer is typed.
I created an UserForm that looks for Student Name his/her Conferenece Date through a VLookup.
The following is my code:
Private Sub cmdFind_Click()
With frmConference
.txtTranslator.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 6, 0)
.txtDate.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 5, 0)
.txtTime.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 4, 0)
End With
End Sub
The txtTranslator and txtTime works correctly, however txtDate give me a number '40850' not a Date?
How can I change this number for the actual Date?
I have 2 work sheet
First sheet is "Department "
11 Departments
Second sheet is "Designation "
20 Designation
i have generated VBA form
but..now in this form i want to create 2 input options..
1)select department (capture all depts. from Department sheet. if i select HR Department then in 2nd option all HR Designation should be copied
I have been struggling with this formula for ages and have finally given up. What I would like to do is to do a lookup on the concatenated values of Province, Department and Initiative No, which has mutiple values in the status of milestone column, and then give the answer based on a condition.
The condition for this example should be, that if the returned status / or statuses of the initiative is all 1, then the value should be 1, if it's all 2, then the value should be 2, and so on.....but if the values returned from the status of the initiative is a combination of 1,2 or 3, then it should give me the answer 2. If 4 is part of the comination of values returned, then the value should be 4.
Zero - 0 should be excluded from the formula as it is part of planned values and not actual.
I have the following table:
http://img85.imageshack.us/i/tableb.jpg/
Each question is multiple choice (either A/B/C/D/E) and the values listed are the probability of each letter occurring. What formula would I need to put in the 'output' column for it in each row to output a letter based on the probabilities. E.g. in question 1, most of the time it would output E, but sometimes (rarely) A and very rarely B/C/D.
I need the names of students from Summative tab in the correct box based on how many question they got correct. (Q 27-32, (0-1, below, 2-3, Average, 4-5, Above Average, 6, Excellent Performance. )
I have attached a file.
I have a frame (Frame1) on a userform added using Microsoft Forms 2.0 Frame. I have added option buttons to the frame named OptionButton1 thru OptionButton4. I am trying to add code where certain cells are copied and pasted depending on which optbutton is selected. I tried the following code but because the option button is a frame object it doesn't seem to trigger the event.
Private Sub OptionButton1_Click()
'copy level 1
If Me.OptionButton1 = True Then
Worksheets("Sheet1").Range("G10:G32").Copy
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("C10:C32").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End If
End Sub
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
View 4 Replies View RelatedI am currently using the Match and index functions to search for specific criteria and return specific data based on that criteria. The problem is the formula is returning the first value at the beginning of the month rather then the end of the month value that I am asking for. I have my criteria set to zero so it should be a perfect match. with the live worksheet I have several tabs for different years. I want to take the end of month balance on each of those year tabs for each month in each fiscal year and add them together. My formula only has the one month because I am unable to get it to work much less adding in all of the years information. I have the employees choosing EOM for the last transaction posted during that month and that calculates the month number in the field next to it. I was thinking I could use the match to look for a specific month in that column and return the balance adjacent to it at that point.
View 7 Replies View RelatedI am trying to achieve the following. In D2: AQ2 there a values indicating the subject identifier for that column, eg in D2 it is SEN and E2 is Art. Each row represents a pupil name into which grades are inputted. I want to compile a list of column identifiers that corresponds to a D grade (it could be D, D- or D+) eg
A2 Name ----D2 SEN --- E2 Art --- F2 Business Studies
A3 Bob ------D3 D+ ----- E3 A -----F3 C
A4 Tim ------D4 C -----E4 D------F4 D
A5 Pete------D5 D------E5 D+ -----F5 D-
Expected output for Bob is SEN. Expected output for Tim is Art, Business Studies. Expected output for Pete is SEN, Art, Business Studies.
I've enclosed a small sample of the actual worksheet. Expected results for row 3 start in cell CP3.
I have a Sheet in a workbook that has about 250 Unique supervisors in it (column A) It has about 2300 rows of employees. Is there a macro that can take the employees supervisor column (A) and break the rows associated to that supervisor into a new sheet & and name the sheet with the supervisor’s name? Can excel have 250 sheets (2003 version)?
View 3 Replies View RelatedI have been attempting to solve a problem for quite some time and would love any help anyone can provide. I'm on Win XP, Excel 2003.
I have a series of assets each with a unique number to identify it in it's own row. In a separate worksheet I have a column with just these numbers next to a column with a word assigned to each number. Some are only listed once but many are listed multiple times. I was looking for a function that will retrieve the information in the second worksheet and place it in the row that it corresponds to the number in the first sheet. I've listed an idea of what it looks like below. I'm looking for it to search for every instance of that number and provide what's next to it. The first set of information is what I have, the last is what I'd like it to look like.
1 hamster..............1 hamster
2 cat......................2 cat, feline, kitten
2 feline..................3 dog, puppy
2 kitten..................4. etc.....
3 dog
3 puppy
copy the excel sheet data from one sheet to another sheet?
-> I have one excel sheet (name: Test.xls, sheet name: SHEET1)
Sn Code Type next calib
5BPR CORR7-Apr-12
4BPR CORR7-Apr-12
73BPR CORR7-Apr-12
9BRG CORR8-Apr-12
10BRG CORR8-Apr-12
11BRG CORR8-Apr-12
17BRG CORR9-Apr-12
311DP CORR9-Apr-12
227DP CORR9-Apr-12
227DP CORI R9-Apr-12
Want to create a new work sheet and copy the today's date(next calib - filed name) records to new sheet.
Example: Today's Date is 8-Apr-12
So, I want to copy following record to new work sheet (when I click the button / run the macro).
Sn Code Type next calib
9BRG CORR8-Apr-12
10BRG CORR8-Apr-12
11BRG CORR8-Apr-12
I have multiple worksheets with multiple varying columns with varying rows. My one constant is the product_id. I want to merge all worksheets into 1 worksheet based on the product_id's. Here is my example:...............
It has to consolidate all of the column names from all of the worksheets into the final worksheet, then take all of the rows and put the product_id in the product_id column and put the other data under the appropriate columns. Some columns will end up blank where they may be a column in worksheet 2 but not worksheet 1.
I would like to copy values corresponding to a unique identifier from one sheet to another, using the vlookup function. The problem is that after a certain cell the values are given as N/A even though they are present in the sheet I want to copy them from.
View 3 Replies View RelatedI would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
I have two or more fields of data, where parts of them are auto populated based on inputs from elsewhere. That part i have working fine, however the problem arises in taking that information and displaying it as a Bill of Materials list on another tab in my excel file. I want to take the information, and only display it in the BOM tab if that piece of information is being used (ie item count field not being 0). I know how to do that part in a rather simple format, however i'd prefer to make it so that when i have the list, and a certain item isn't used then its not included in the BOM, and an empty space is not left behind. I would prefer this to be all be done automatically by a formula if possible instead of just having to resort to sorting the table every time the tool is being used.
Is this even possible in excel, A quick example doc is attached, shows 2 example fields. i'd like to take the information from those 2 fields. and somehow create a single list, with a single heading along the top, with all row containing a 0 "Unit Count" to be omitted entirely from the list, and not have a blank space where the item should be. Keep in mind these fields may be greater than 2, and may not be position one after the other in a strait down line.
Example.xlsx
I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
How do you put an answer in a cell after subtracting 2 numbers and if greater than a number put that answer in. This isnt right just an example
=IF(a1-a2 is greater than 10 then put that answer in the cell if not just leave it blank?
I want to tell Excel that, if a cell value is between 1 and 100, to enter one result in another cell. If the value is between 101 and 250, enter another result in that same cell. If the value is between 251 and 500 enter another result in that same cell and so on about another 12 times.
View 5 Replies View RelatedSo I am creating a board game in excel. I currently have a draw card feature to draw random cards. I also have section above where you can fill in the answer to the question and another 2 sections that flip to correct or incorrect. Those formulas work as =IF(AND(Sheet1!J6="When resubmitting the claim to another insurance should you be voiding an adjustment to the S code if one is posted?",Sheet1!I2="Yes"),"That is Correct!"," "). I have another for if Sheet1!i2="no" then it flips to that is incorrect. I want an OR formula that will be like if multiple cells have any text in them it will display them but only one cell at a time. So essentially a way for multiple questions to be able to be drawn and you can answer those questions and be told if they are correct or not.
View 14 Replies View RelatedI currently have a drop down menu in one of my worksheets, in which I have several different text values entered. What I would like to do is link each of those text values to a numerical value, which would be entered in to another cell. So if I select "Option A" from my drop down list, and Option A is equal to 200, I want "200" to show up in another cell. If I select "Option B" from my drop down list, and Option B is equal to 400, I want "400 to show up in that same other cell.
View 4 Replies View Related