Search Using Info From Two Cells To Give Corresponding Answer From Different Sheet
Feb 14, 2014
see the attached 'Example' excel sheet.
I'm not the best at explaining, but I think that the excel sheet is pretty straight forward.
In Sheet2, cells C3 and D3 are manually entered by the user. I am looking for a formula for B3 that will take the information entered into C3 and D3, find the row in Sheet1 in which column C=Sheet2!C3 and column D=Sheet2!D3, and give the unique corresponding result from Sheet 1 for that row in column B .
So, for example,
if C3=A and D3=102, then the result would be 444
if C3=A and D3=104, then the result would be 111
if C3=B and D3=102, then the result would be 111
The combination of C3 and D3 is always unique and will never have more than one possible result in column B of Sheet1.
View 5 Replies
ADVERTISEMENT
Sep 17, 2009
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.
View 8 Replies
View Related
Oct 27, 2005
The following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.
The answer I get is 308669
=(((F22*71449)*0.4)+16147/C22)
Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8
View 10 Replies
View Related
Jun 18, 2013
Here is an example of my spreadsheet: Excel example.xlsx
I would like to get all the bid/ask quotes for the different currency pairs in sheet 2 into sheet 1 sorted by the right date and time.
I need a formula which recognize the time + the currency from sheet 1 and search for it in sheet 2 then brings the right bid/ask quote back into sheet 1.
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
Feb 6, 2014
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.
View 5 Replies
View Related
Dec 27, 2012
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].....
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
Jan 12, 2007
I have a blank log template on one sheet, On this I need to be able to enter two variables that tell other cells to pick info from the next sheet. The two variables being 1) Date and 2) DAY SHIFT or NIGHT SHIFT.
On the next sheet is an archive of data running in columns with the dates in row1 and the denoted shift (day or night) in row 2. The rest of the relevant info is in the columns below each date and shift
What formula would I use so that the log template cells fill with data from the archive when the date cell and shift cell data is entered.
View 9 Replies
View Related
May 12, 2014
I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies
View Related
Jun 7, 2009
Search upwards in a column find the first cell with data and give the date in column A on same row as data?
View 9 Replies
View Related
Feb 24, 2014
My difficulty: I have an Excel 2007 DB xlsx Workbook file (mydb.xlsx) made up of several (8) Worksheets. I can't modify the content of this DB. For this reason I 've created another Workbook (MyGUI.xlsm) implementing a user interface for db querying.
The userform let the user making searches using the find method (criteria: "Text contains...") In the DB file there is one particular Sheet with thousands of entries. This makes the searchs too long (30 seconds). During this period the entire form is blocked.
My question: when the user realizes the search is too long I'd like to stop the current search
introducing for example a new button "stop current Search" to click it in these situations.
View 4 Replies
View Related
Mar 12, 2013
I am using Excel 2010 and am looking for a formula that can solve this matix in column below. The yellow is where I would like the answers.
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] ........
This is what it would look like when the formula were applied
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] .........
View 3 Replies
View Related
Jun 28, 2006
I have included the table. What I want to do is in a user form pick a county ie Devon, Then by clicking "go" all entries that contain "Devon" are then copied to a " sheet 2" I don't want to use the Ctrl-F
View 2 Replies
View Related
Feb 6, 2014
Attached is a sample of what I need completed.
Monthly, I have to do a chart just like this except slightly more complicated.
In the Sample download, there are three charts, "Sample Chart", "Sample Input", and "Desired Result".
"Sample Chart" has a list of accounts from different companies, The first column being their number, the second being their name, and the third being the money they spent.
The "Money Spent" Column is always blank when I start for ALL companies.
I have another chart, "Sample Input", which contains the prices that I'm supposed to put in "Sample Chart, Money Spent" column.
The thing is, "Sample Input" only has the companies with prices listed.. Not all companies have prices, so this means the "Sample Input" is always a shorter list than "Sample Chart".
What I need is the prices from "Sample Input" to be put in the correct position in "Sample Chart". The "Desired Result" chart is what I want it to look like.. exactly like that!
When I do this monthly, I have to scroll through several thousand accounts doing this.
Suggestions:
- Possibly have a macro or formula take the Account # in "Sample Input".. Sample it in "Sample Chart", then copy the price and paste it in the right location.
- Possibly make "Sample Input" have blank rows inserted in the places where it should have the account with no prices.
View 5 Replies
View Related
Jul 29, 2014
I have a formula in column J that is the following:
=IF(I541="",IF(ISNUMBER(SEARCH(LEFT(G541,4)&"*"&RIGHT(G541,4),H541)),G541,""),"")
See screenshot:
I need to replace "G542" in the formula above. For example, in cell J541 shown above, I need it to output "1996-1999|2000-2001" instead of "1996-2001" I need the output to include LEFT(G541,4)&"*"&RIGHT(G541,4)
View 2 Replies
View Related
Jun 6, 2008
I need to search a sheet for a specific phrase "phrase". When the phrase occurs I need to copy adjacent cells into another sheet. Then, I need to continue searching through the end of the sheet for all other occurrences, copying them into the other sheet. For example: "Phrase" occurs in cell B2, B20, B42, and B82 of Sheet1.
When it occurs in B2 I need to copy C2 and D3 into Sheet 2, cells B2 and C2.
Then when it occurs again in B20 I need to copy C20 and D21 into Sheet 2, cells B3 and C3.
View 4 Replies
View Related
Aug 18, 2014
I need to create a formula that searches the first table and autofills the second based on the teacher name. So when Mr. M is listed in column e, all the data to the right automatically appears on the second sheet.
View 3 Replies
View Related
Sep 24, 2007
I have a workbook with a Cost Analysis sheet, a master menu sheet, and then all the sub sheets. I have coded command buttons on the master sheet, so once a category is selected, it goes to the appropriate sub sheet. On the subsheet is a list of items of varying sizes, and command buttons beside that which will allow the user to "Select" that particular size. Once the size is selected, the verbage in cell B5 plus the verbage (size) beside the selected amount, and the prices (labor and materials) in the next two columns, need to be transferred to the first available blank line, starting with line C5, to the Cost Analysis sheet. I have never done coding before for a Loop (i.e. For i = ???) and am not sure how to get it to do what I want. After they select that, they can pick a command button that takes them back to the master menu so they can continue picking various items until they are through. Then the Cost Analysis sheet will show all the items they picked, (name of item, material cost and labor cost: Columns B, C, & D). Not everything is coded yet (such as selecting a command button to go back to the main menu, because I wanted to make sure I could solve this problem before doing the easy stuff).
I read over quite a few of the previous threads and am not sure how to change them to suit my circumstance. I know some code (VB), but not much. I do not understand the concept of DIM, and how the loop works to make it happen myself, and all the websites I could find were not suited to my situation of copying three cells of data to the first blank cell on another sheet.
View 9 Replies
View Related
Feb 16, 2008
I have a list box that I'm using to pull customer information from my "Customer" sheet to my "Invoice" sheet. I can actually open, select, and close the userform. However, I can't get the macro to put the information into the specified location on my "Invoice" sheet.
Here's my code so far:
Private Sub Cancel_Click()
Application.ScreenUpdating = False
Unload Me
End Sub
Private Sub customer_Change()
End Sub
Here is the "Customer" sheet I'm pulling the info from:
And here is the location on the "Invoice" sheet that I'm trying to direct the userform to:
And here's my userform just for reference:
The userform works perfectly until I press the OK button. That's when I get the error.
View 9 Replies
View Related
Jul 29, 2014
I have a list of about 600 addresses that need to be reformatted with a space following X character and need to be in all caps. This would be the more tedious way because I would still need to search the other file to make sure the text itself is correct. I saw on a different forum that this line would work in adding a space
(=REPLACE(A1,5,1,MID(A1,5,1)&" ")) but I need to add two spaces and adjust the location of the space so that it is following the first string of text.
The other approach could be to set up a formula or macro that searches the other file with a section of the original text and replaces that string.
View 3 Replies
View Related
Dec 8, 2013
I 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.
View 6 Replies
View Related
Apr 18, 2014
What I'm trying to do is link a cell to give me the sum of 2 different cells located in 2 different tables I created.
So I want to have Cell B5 look for the number in cell B3 in the Table that starts in column Q1 and give me the corresponding number in the same row in the column R1
then lookup the number in cell B1 in the Table that starts in column T1 and match it with the number in Column U1 that corresponds to it in the same row
and Finally give me the sum of both corresponding numbers.
IE.. the number in B3 is found in Q13 which uses the number it corresponds to in R13. The takes B1 finds it in T16 and uses the number it corresponds to in U16.
Then returns the sum of R13 and X16 into B5. (B5 should show 176)
View 5 Replies
View Related
Jul 13, 2006
If this has been requested already, I apologize, please direct me to the right thread and flog me with USB cable. I've been searching the forum for an answer for a couple of hours now for the answer to this:
I have a list of words in column A on a sheet and I'm trying to write a formula on another sheet that will check all cells on a pre-populated row and return a value if any of the words from the list on are contained in any of the cells in the row. I've attached an example spreadsheet
View 3 Replies
View Related
Mar 11, 2011
I had a question considering a workbook that I am working on for overtime, a different one than I posted before. On my workbook, I have vacation leave, sick leave, and overtime to figure in. I want to be able to have a choice to choose which you would like to keep at the end of the week. Such as if you were to work 8 on Mon, 8 hours on Tues, 10 hours on Wed, and then take vacation leave on Thur, for 8 hours, and then work 6 hours on friday, I need a way to choose whether to be paid for the overtime and vacation time, or to subtract two hours from my overtime and add it to my regular time, i.e. 40 hours, or subtract two hours from my leave time and add it to my regular time. I know this sounds complicated, it is at least to me.
View 3 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
Nov 12, 2009
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
View 9 Replies
View Related
Jan 16, 2009
I am running the formula =VALUE(RIGHT(CELL("filename"),15)) to read the tab name in a workbook with 90 sheets. When a new account is opened, the operator inserts a new sheet from a template (which contains the above formula), and labels the tab to the account number. The formula then 'reads' in the account number and performs look-ups based on it.
The problem is that the sheets are only working when the sheet is active. They all seem to reflect the data in the active sheet or give me a #VALUE error. Where am I going wrong? How do I fix the problem?
View 3 Replies
View Related
May 28, 2010
Is there some code I can put in the Workbook Open section that will minimize the ribbon so give the users more sheet space?
View 3 Replies
View Related
Mar 28, 2014
I want to count a range of cells and if the value of those cells is 5 and over, i want it to give a 1 and if its under 5, i want it to give a 0. I've attached a file of what i'm after, row 13 is where the formula would go, i have just put in the values.
View 1 Replies
View Related
