I have data in A column.
I want to Get anser in B Column.
Sheet1 ABC1S.NoCountryCode2E8481234567CroatiaE8483E8351234568SloveniaE835472123456900Ireland725JM123457000LuxembourgJM Excel tables to the web >> Excel Jeanie HTML 4
Answer Based on C Column....
I am trying to created a spreadsheet for work where I have created to validation drop down boxes, one each box has been selected i want it to return back with the correct answer in the 3rd column.
below are the 3 colums. i have created a validation for column 1 and 2 but when selected i want the final box to = column 3 ie. >=9, =2
120%
12
>=2
130%
13
>=2
140%
[code].....
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 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 two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.
I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…
A = 2000 text
B = 1234 text
F = 2000 trailing spaces, text
G = 1234 trailing spaces, text
I = 10,000
Answer in column J
I want 20001234 = 20001234 = 10,000 for the whole column.
I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.
I want to get value from reference one column and return value by corresponding row from another column. This can be done by vlookup column. But it find value from 1st column and return value from given relative column. That mean find from Left column and return value from right side given column number.
But I want find from Given column number and return value from given left column. For Further detail see the Attached file.
=IF(ISNUMBER(VLOOKUP($E8,Su!$D:$K,7,FALSE)),VLOOKUP($E8,Su!$D:$K,7,FALSE),)*$I8
In the first vlookup it picks up a value. I need to do the same vlookup function but it should also pick up another value in column 9 and should add value in column 7 and 9 and return it. Employee id numbers are in column E in the first page.My formula checks for the employee id in Su sheet and retrieves the value from the seventh column. I need to retrieve also from 9 th column and add them together.
I want to look up a value in the third column of a table and return the value in the first column of that row on the same table. What do I do?
View 2 Replies View RelatedI have a table as below (you can also refer to the attachment) and need to do the vlookup with EE Code if the left 2 characters of "EE Code" = 91 to 95.
EE Codedeptif left 2 characters of EE code= 91 to 95, vlookup dept code
921600012345678
vlookup arraryA12345623456700
39914123 test785637039914123
38214125 test915580057889056
40964197 test932660038214125
77014155 test913580039914123
345699940964197
945800077014155
953790300123432
915800012345678
045699957889056
585637039914123
use the VLOOKUP function when the column I need to search is not column A?
I have a Workbook with two worksheets in it and in Sheet 1 I have 10 columns of various text and numbers. In cell A1 of sheet 2 I will fill with a certain code that appears in column E in Sheet 1. The result I would be looking for should come from Column A in Sheet 1. I thought that the following would work copied into B1 of Sheet 2 but it doesn't appear to - =VLOOKUP($A1,'Sheet 1'!E$1:$E$481,1,FALSE).
Currently, I have a standard vlookup forumla that looks like =Vlookup(B2,Sheet2!A:G,5,false)
However, the data on sheet two is ever expanding--each month, specifically. And I need to return the most recent (so, rightmost filled) column. Right now, the column is 5, but next month it will be 6 and so on and so forth.
in the "Order for EMS" and "Order for FEDEX" there have 1 product exist, now the customer need to type the country in the "F35", and the Price of weight will look back to the sheet "EMS Weight" and "FEDEX Weight" but for example, if i type Japan in both sheet, the EMS that one is correct 1kg to Japan is $168 but in FEDEX that one is not correct 1kg to Japan is $169 but now show $342, the formula in "G36" should be correct, what's wrong of my formula????
View 3 Replies View RelatedI have a sheet that has 2 fields that i need a yes/no answer from. Basically if b = "P1" then a should be less than or equal to 4 to reply good otherwise it should reply no. but if b isnt "P1" but instead is P2 P3 or P4 then it should => 10, 30, 40 respectively.
View 8 Replies View RelatedI 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 Relatedi just wanted to know if we can open a pop up which will give us answers. i have attached a example work sheet of what i require
View 3 Replies View RelatedI have a formula that is in ColF Row4, and it is copied down to ColF Row 13. But trouble is that when I autosum this column I get 6, but the answer should be 8.
The data in Rows A-E are extracted from an external data source. I've attached the spreadsheet. =IF((AND(A4=7,D4="")),1,(IF((OR(A4=7,D4>=2)),D4,0)))
I have to following code. I have an issue now where I have to Pop a textbox from based on the answer to one of my questions.
If cboProjOffset = "Other" Then
userSalOther.Show
Problem is I actually want the text box to populate on the same line of the range answer in column m. At present it doesnt seem to be doing this. Can anyone help?
Public gintEditMode As Boolean
Public gintTotalEntries As Integer
Public gintLineNumber As Integer
Private Sub cboProjOffset_Change()
If cboProjOffset = "Other" Then
userSalOther.Show
End If
End Sub
in cell d1 i have
hk/q46559
in cell i1 i need to return whatever is before the "/"
in this case hk
and in cell i2 i need to return the number after the q
in this case 46559
I have a few 2007 workbooks that are set up to track and compare individual budgets over time. The majority of the cells are referencing workbooks that have yet to be created.
On opening these workbooks the users are confronted with an informational window informing them that "This workbook contains one or more links that cannot be updated", to which they are instructed to choose "Continue". Is there code I can add to these workbooks that would automatically choose "Continue" once a user enables the security warning for Automatic updates of links? I believe this would be the same as an On Open event.
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 would like to add a drop down that pops up only when a certain answer is given in a previous cell. For instance, if the user picks "Single Door" in cell E2, then in cell E6, I would like a box to pop up that says "HL/HR?" so that in cell E7, they have a drop down & can select either HL or HR. But, if they pick anything other than "Single Door" in cell E2, I don't want a box to pop up in cell E6 to prompt them to do anything else.
View 2 Replies View RelatedThe macro I am working on will ask the user if they wish to transfer data to one of two Forms, held in two separate worksheets. The worksheet names are Record Form Games 3583 & Record Form PE 3581. The msgbox will ask "Is the data being transferred to Record Form 3583?" If Yes the sheet "Record Form Games 3583" will be set as the sheet as the one to transfer data to. If no "Record Form PE 3581" will be the destination sheet. This is what I have so far:
Sub Do_Admin()
Dim Record_Form As Worksheet
Msg = "Is the data being transferred to Record Form 3583?"
ans = MsgBox(Msg, vbYesNo)
If ans = vbYes Then Set Record_Form =
Sheets(Sheet"Record Form Games 3583")
End Sub
My macro cuts and pastes a section of one sheet over the top of each remaining sheet in the workbook. It pauses before each paste and presents a message box (relating to copying over a named range) requiring the user to manually click on "Yes" in response to the question before continuing on. How can I modify my macro so that it finishes without requiring the user to click on "yes" for each sheet. In other words, how do I get the macro to automatically answer "Yes" to the message box ??
View 2 Replies View RelatedI would like to do a VLOOKUP (or similar operation) to grab information from a column based on a column name instead of column number.
For example, instead of:
=vlookup(a2,t5:z10,4,FALSE)
I would like the formula to read:
=vlookup(a2,t5:t10,"COLUMN_NAME",FALSE)
I am trying to determine if I can write a formula that will read text in one cell, such as insurance and if true enter the amount from another cell and if false then 0. I have tried a IF statement by add the text to name manager. However, the answer is 0 rather than the amount in the selected cell. I might need to use a different formula or function.
View 10 Replies View RelatedI wish to be able to add adjacent cells as follows, assuming I have a number in a1. (in B1) =a1-4.
If the answer is zero I would like to change it to -4 and then continue the process for more cells to read 16 ; 12 ; 8 ; 4 ; -4 ; -8 ; -16 etc.
I have three different sets of numbers on a r12m that I have to add and divide by different numbers and not quite sure I have the formula right as when I do it manually on a calculator it comes out different.
The formula I have put together is
=sum(O4:z4)/10)+(sum(o5:z5)/200)+(sum(06:z6)/1000)
This is the three rows of data
221121
2333443201142
402448314034292331351663
Using the formula above the result is 0.81 but if I do it manually on a calculator it is 1.6 (by doing each line individually and adding results)
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 Relatedim 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.
I have the attached worksheet with clients' names in four columns (A-D). The first names and last names must remain separate. I need one additional column that shows whether a full name (first and last) in C and D also appears in A and B.
Then one more column that shows how many times the name in C and D shows up, but only if it is also in A and B.