Worsheet Cell Does Not Show Correct Answer
Apr 6, 2008
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 '
View 9 Replies
ADVERTISEMENT
Nov 15, 2013
Why than going line by line on a document that has 60k rows!
I have a document with NAMES in column A and ROLES in column I. If the user has more than one role..their name will be listed in "A" for each role in "I". I'm trying to purge down a file that has 60k + rows. Lets say i want to see all the people and all their roles if they are in "PROGRAMMER_ANALYST" role...can this be done by macros? or formula?
View 2 Replies
View Related
Oct 27, 2009
i need t oknow if i can have text added into 1 cell with having a formula with a answer in it as well,,,i have attached a sheet with better examples of what i mean.
View 3 Replies
View Related
Mar 11, 2013
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
View 7 Replies
View Related
Feb 20, 2009
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).
View 2 Replies
View Related
Nov 26, 2006
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 .....
View 9 Replies
View Related
Apr 22, 2009
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.
View 9 Replies
View Related
Nov 6, 2011
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?
View 9 Replies
View Related
Mar 23, 2014
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 Replies
View Related
Mar 23, 2014
Here'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.
View 2 Replies
View Related
Apr 21, 2009
I need to enter a countif formula if a cell equals the name of the active worksheet and also cell A1. My worksheets are not always the same and I also want to be able to copy this formula to multiple worksheets so if the formula states active worksheet then is should work properly when I past it to multiple tabs (at least in my head)
View 9 Replies
View Related
Aug 22, 2006
I am trying to rank number that do not have a specific adress. My problem is that I do not know how to define all parameters of this function
View 3 Replies
View Related
Nov 8, 2009
i want to insert a range of cells from a worksheet onto a userform. how can this be accomplished? i use excel 2007, so theres no spreadsheet control. active x controls i dont know anything about and i heart its unsafe.
View 9 Replies
View Related
Dec 30, 2006
I have a very large spreadsheet with the following columns: WO# (number field); Start date (date format MM/DD/YYYY); Frequency (text); and craft (number). I am trying to have code that checks the frequency and if is "Monthly" or "Weekly" it just goes on to the next row; if it is "Annual", it adds 163 to the start date (start date needs to changed to a numeric field); if it is "Semi-annual", it adds 82 to the start date; if it is "Quarterly", it adds 45 to the start date; and so on, there about 20m different frequencies. After it adds the above value to the start date, I need to check if that number is less than today's date (the day I run the code). If it is, it needs to flagged as "LATE" and the whole row of info copied to another worksheet with LATE as the title and all the column headings and info copied to the worksheet. I hope this makes sense to someone because I am a beginner in Excel and even less informed when it comes to VBA. Any help would be greatly appreciated.
The way the process must work is that I need to check the frequency and if it is "Weekly" or "Monthly" , it is ignored and goes on to the next row. All other frequencies are cut in half, i.e., "Annual" is 183 days, "Semi-annual is 92 days", "Quarterly" is 45 days, "2-Year" is 365 days, and so on. This number needs to added to the scheduled start date (now formatted as a number, not a date, and checked to see if it is smaller than today's date (also a number). If it is, it is reported on the second worksheet (titled Late).
View 11 Replies
View Related
Jan 31, 2010
creating the user form using the submit button. Ive used a tutorial to create the form and the code, but it doesnt work i get a subscript out of range error 9)
Here is what i have
Private Sub addpatient_Click()
Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.txtfirstname.Value = "" Then
MsgBox "Please enter a First Name.", vbExclamation, "Patients "
Me.txtfirstname.SetFocus.........
View 11 Replies
View Related
Nov 18, 2007
I wrote the following...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("Obj1")
If Not Intersect(Target, VRange) Is Nothing Then
Range("ObjDescr1").Select
ActiveCell.FormulaR1C1 = "=IF(Obj1="""","""",(IF(EmplType=""Salary"",VLOOKUP(Obj1,SalaryDevGoalsTbl,2,FALSE),VLOOKUP(Obj1,HourlyDevGoalsTbl,2,FALSE))))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End If
End Sub
What it does, everytime range named Obj1 is selected, range named ObjDescr1 is populated to a Vlookup, depending on which type of employee.
I need the Worksheet Change procedure to handle Obj2 - 5. I want to Change Procedure to find out which named range Obj(n) is effected, and to do the rest on that one. ObjDescr(n) is also to match the value of (n).
How can I do this the most efficient way? I want to cut down on code in ever area possible, so I dont want to just copy and paste for obj1,2,3,4 and 5.
View 9 Replies
View Related
Feb 15, 2010
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies
View Related
Jan 26, 2014
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
View 4 Replies
View Related
Jul 3, 2008
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
View 14 Replies
View Related
Dec 27, 2013
So 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 Related
Aug 29, 2009
im looking to return an answer in cell c28. in b28 it will say "best goalkeeper"
in c28 i need a formula to look in range d2:d6 then return the highest answer and correspond that to whats in b2: b6. so we could say
(b2)shilton(d2) 33
(b3)corrigan (d3) 55
(b4)parkes (d4) 66
(b5)schmeichel (d5) 100
(b6)cudicini (d6) 14
in c28 it will return the answer schmeichel because he is the highest
and in d28 it would return the points he has.
View 2 Replies
View Related
Jan 15, 2010
Can this Formula work? Where if c2<e5 then its value is a number. The formula below is what i tried but it didnt work
=IF((C2<E5);"c5=value=f5")
View 2 Replies
View Related
Mar 20, 2014
How I can get a picture to pop up when a certain answer is selected in a certain cell, or when a certain answer is keyed in to a cell. Another portion to this question is that we know how to get a message to pop up in a dialog box when a certain piece of information is typed in to a box, but we don't know how to get graphics into those dialog boxes.
View 3 Replies
View Related
Feb 25, 2014
Here is the qtn if two cells A1 and A2 has "ab 1" and "ab 2" the answers for comparing cells should be true as both cells has first 2 letters as alphabets then space followed by numeric..format of cells is same.
View 2 Replies
View Related
Nov 5, 2009
Is there a way to have the answer to a formula display nothing if one of the cells have nothing. Example cell A3= A1*A2 ... it will naturally show 0.00 but even if both A1 and A2 are completely blank. Is it possible for A3 to show nothing if A1 or A2 or both contain nothing. I still want it to show 0.00 but only if that’s what is in the other cells.
View 5 Replies
View Related
Jun 18, 2013
I need an economical way to fill values in 12 cells.
I have a list of 50 cities in column A. For each city I need find and fill certain demographical values in columns, L2 through W50, consistent by looking up data in another sheet. Both sheets share the same column names.
I have tried =VLOOKUP(A2,'CitiesDemo'!$A$1:$W$51,11,FALSE) and it works well enough but I would like to drag across and down to avoid manually editing references.
View 3 Replies
View Related
Jun 12, 2009
I have one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:
- "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";
or
- "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".
Basically the 8 is always correct with an R and the 7 with an L: ...
View 6 Replies
View Related
Oct 1, 2009
I need a formula that will look up a cell to get a figure from, but there is three of the same name (sometimes more, depending on different products sold) i.e. "Dept Total" (shown below & attached for easier reading) ....
View 7 Replies
View Related
Jun 3, 2014
I have a list of Grades (Job Description). Each of them is allocated a SCH No = SCH1 / SCH2 etc
I need a macro that will go through this list and copy the Grade into the correct SCH Column on Sheet List2.
The columns in List 2 then need to be sorted A-Z.
AddNamestolist.xlsx
View 5 Replies
View Related
Mar 4, 2009
1 is a Master sheets with all prices of products etc etc.
2nd sheet is an update of all products that have changed in price.
There is a unique code for each product.
What i was hoping for is a way where on the second sheet the product (using the unique codes assigned to the product) is looked up on the master sheet and the NEW price on the 2nd sheet replaces the OLD price on the Master sheet?
View 9 Replies
View Related
