I need to lookup a Order Number and return all associated Item Numbers. My spreadsheet has about 10,000 lines and these Order Numbers could have as little as 1 or as many as 15 Item Numbers associated. I would like to have a drop down on each Order Number that shows the Item Numbers.
View 3 RepliesI'm trying to do the next level of a v-lookup. I have a group of data with a text "flag" on certain rows. On my summary worksheet, I'm trying to do a lookup or a nested index function retrun all of the values with the text flag next to it.
I've been playing around with nested formulas and this is where I'm stuck. Here's what I came up with:
=INDEX($D$1:$E$9,(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),ROW(1:1)),ROW(1:1))),2)
This only works for one row and not for the other rows. Maybe I'm going down the wrong path?
I'm getting cross-eyed from trying to find the source of the error and not having any success. I've successfully used this approach many times, but for some reason I'm getting an error I can't pin down.
The problem: INDEX-MATCH lookup formula returns # NAME error. Formula: =INDEX(Data,MATCH(H8,Date,0),MATCH($I$7,Line,0),MATCH("FPY",Hdngs,0)). I've done my best to verify the named ranges and cell references are correct and have stepped through the help file on the "potential source of the error", including going to Tools >Options and making sure "Accept Labels in Formulas" is checked, as well as having read a number of other posts.
i am trying to lookup up when ppart matches spart and subtract total shipped from total produced. Here is where I am getting stuck .... because spart has multiple returns LOOKUP(2,1/((I2=C:C)),J2)
View 3 Replies View RelatedI have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?
View 9 Replies View RelatedIm trying to do a lookup but when I drag the formula down it returns the wrong values and also returns #N/A or #REF (which I think means it's refering to a wrong range and cant find the data???). How can I do a look up function by dragging the formula down a long column of data eg. 65,000 rows in excel, that returns the correct text. I have attached a simple example- see attached.
View 4 Replies View RelatedI am using this formula
View 9 Replies View RelatedThis formula works perfectly except when worksheet 2 has a code that is not on worksheet 1, then I get the error #N/A. I understand why I get the error, but not how to fix it. Obviously some sort of nesting - but what and how?
What I want on worksheet 2, column I - if there is a code on worksheet 2 but not on worksheet 1 - a return of "N/A"...
I'm trying to generate a message box when a lookup cell returns the #N/A error value.
The lookup formula (=VLOOKUP(O8,Prod_Lookup,6,FALSE)) is in a "helper column" and returns a value of 0 if there is no entry in the lookup source sheet, but returns a value of #N/A if a non-matching date is entered in the active worksheet.
I have the following, but need some help in getting it work correctly. At present, it doesn't trigger the message box to pop up.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Prod_Val As Range
Dim note1 As String
Set Prod_Val = Worksheets("C_Data").Range("P8:P7000")
note1 = "Brazer PPM cannot be calculated because the Daily Production Count" & vbLf & _
"has not been entered for this date." & vbLf & vbLf & _
"The Daily Production Count must be entered prior to entering the daily leak data." & vbLf & vbLf & _
"Upon clicking the OK button you will taken to the Production Count sheet to enter the data."
I get a Type mismatch from this line:
x = Application.WorksheetFunction.Lookup(2, 1 / (Range("G7:P7") = "A"), Range("G7:P7"))
I have a table (A1:C5)
A1:C1 contain the table headers
A2:C5 contain the data
A6 contains the value I want to search for I use this formula to return the column header of A6 A6=INDEX(A1:C1,SUMPRODUCT((A2:C5=A6)*COLUMN(A2:C5))) Now, if there are duplicate values in A2:C5, this will return more than one column header What I need is a vba code to view a msgbox with all column headers returned in this case
I'm trying to create a lookup formula where it matches the beginning of words and returns the value of a cell I choose. This is my layout. In Workbook1 in column E I have a list of medicine names which i'm putting in from personal medical lists. These names are not "complete", i.e. they aren't the exact names of the medicines. In workbook 2, I have a sheet with the exact names and corresponding codes of the medicines. I want to return the code of the medicine. The beginning of the names of the medicines are the same, so I want to lookup the the X first letters of the medicines I write in manually (Workbook1 column E) and find it in Workbook 2 column B and return value of Workbook2 column A for that medicine (on the same row).
View 5 Replies View RelatedI setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.
View 9 Replies View RelatedI have a file created in Excel 2003 that uses a vlookup to reference another file, also created in Excel 2003. The function returns VALUE when the referenced file is closed. I get the VALUE error whether or not I update links upon opening the file. If I open the source file, the function calculates properly.
Here is the formula when the referenced file is open:
=VLOOKUP(A38,'[June Scorecard confirmation.xls]VPSC Summary'!$C$10:$F$22,4,FALSE)
The full & correct path appears in the formula when the referenced file is closed.
Columns C, D & E in the referenced range contain text, column F contains a Sum. This file is linked to another file, also using lookups. The linked area in this file works without problem, but it is a precedent to the calculation in column F.
Both these files reside on a network in subdirectories of the same logical drive. I am using a laptop that briefly had Excel 2007 installed on it, but was wiped clean before it was issued to me.
Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.
I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).
I've investigated the techniques in this link: [URL]
Questions:
1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?
I am having trouble with my macro. I have attached excel sheet for reference.
Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.
I have created the macro but it's giving me Run time 1004 error.
Also, my formula is incorrect. Maybe that's why my macro isn't working?
2WayLookup_Macro.xlsm
Im working on a dashboard for my department and its going pretty well. Im stuck on the Project tracker.
On the front page (dashboard tab) I want to see all overdue projects from the Project Tab. I have the Overdue status working on the project tracker so I need some kind of multiple return vlookup for all of the OVERDUE tagged rows. This can be a macro or auto fill formula.. if someone has seen something similar, i would love a link so i can edit it accordingly.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I am trying to write a formula that will give more than 1 return for a vlookup statement.
The info i am looking up is sku code (col a) and expiry date (col b).
There are more than one entry for some skus , but I am only getting returned the first expiry date.
I have a raw data page which contians peoples name and where they charged thier time. My problem is that I only want thier name to appear once. What type of formula do i need to use to make this happen.
Ex
Employee name Project No. WBS Code Hours
John Smith 123456 1 4
Jane Doe 123456 1 2
John Smith 123456 2 2
Jane Doe 123456 2 1
John Smith 123456 3 2
Jane Doe 123456 3 5
At the end I want to do a count to let me know how many people I have charging the job but I don't know what formula I need to exclude duplicate entries.
In this sample below, I need to extract all matches for my look up value. Where I extract the data to is not important because it will be used as informational data in user forms. Basically, I have 670 rows that make up this table...this table is static (never changes). Each row represents a unique combination for achieving the value on the left.
To take it a step further, I would like the ability to deviate from the lookup return...in other words, force the return to be one row down, or one row up (this would satisfy my first requirement as well). I tried offset with vlookup, but I cannot make that do anything useful. I used match to get the row number of the return, but I don't know what to do with that row value now that I have it sitting in a cell in order to accomplish this. I was thinking I could use an offset formula with the cell's value to get this done, but I could not figure out how without doing it in VBA.
In the end, I will present this data on a userform...first will be the actual vlookup returns, then the user will have an option to select the next row down if they want (same look up value if one exists) or up (next higher value).
If possible, I would like the data lookups to be accomplished without VBA.
This is an extremely large working application, (about 17MB so far), lots of VBA coding and logic applied. This problem is a result of a request from the users of this application.
Here is a sample of what I am looking at.....
I've never quite mastered the idea of passing arguments between subs and functions. I have a project right now where I am writing this same bit of code over and over, for different parts of the macro:
View 4 Replies View RelatedLet's say I have a standard Vlookup formula entered in cell C2
Code:
=Vlookup(A2,Sheet2!$B$2:$C$10000,2,0)
For that formula there may be 100 matches found in Sheet2, each with a different return option and obviously the above formula only returns data from the first found match.
Is there some way to drastically change that simple formula in VBA script to ...
1. Auto fill formula down 100 rows & return all possible return options upon match and not just the first return given upon the first found match
2. Make the lookup value be the A cell in the active row
I've noticed recently that a sumif formula will only ever add up 1 column's worth of data. Is it possible to get it to do multiple columns?
For eg, I have a spreadsheet that looks like:
Using the formula shown, I can easily add up values for 'A' codes for one of the 3 columns, but short of adding together multiple sumif's, there must be a more intelligent way of doing it?
Entering a code in a cell, I'd like the cell next to it to return a list of possibles which the user can then select:
Line Code Material
123456 Apple
Orange
Strawberry
By entering the line code the user get the 3 Materials that has this Line code and they coose the one they want.
The list I was using to look up looks thus:
Line Code Material
123456 Apple
123456 Orange
123456 Strawberry
I've read a bit about combo boxes and lost myself complete, so please use small words for me.
I have a table with 3 columns. See below for data (ignore dashes):
COLUMN X-----COLUMN Y-----COLUMN Z
Project 1---------21 Dec 09---------$70
Project 2---------21 Dec 09---------$85
Project 3---------21 Dec 09---------$90
Project 1---------1 Jan 10-----------$85
Project 2---------1 Jan 10-----------$95
Project 3---------1 Jan 10-----------$110
As you can see, COLUMN X contains Project Names, COLUMN Y contains Dates and COLUMN Z contains Monetary Value.
Here is what I need to do: I need a formula/function that will lookup "Project 1" in COLUMN X and find the latest corresponding Date in COLUMN Y (for this example, 1 Jan 10) for Project 1 and then display the corresponding Monetary Value from COLUMN Z (for this example, $85).
I have tried using VLOOKUP, but since the data must be sorted it won't work. I tried using INDEX(MATCH), but can't get it to work. My goal is that the cell will be dynamically updated every time a user inputs updated data for a certain project based on the latest date.
I'm attempting something new, a dropdown menu using vlookup to find data . If i use the simple example below, using data from B-D on 1 worksheet, where i want to find people by county:
A B C D
Search by County Name County Town
John Sussex Brighton
Eric Yorkshire York
Jim Cheshire Manchester
Liz Sussex Hove
Sarah Avon Bristol
I've created a dropdown menu on a second worksheet,A2, by county search, to return a persons name,county and town. The problem is if i select Sussex i will get 2 names who live in different towns within Sussex. I've created a simple vlookup which works fine, but because of Sussex having 2 results, i created a second vlookup for the second result to appear in the cell underneath.
The problem is that for the results that only have 1 result, the second vlookup returns a the same result as the first result, which i dont want.
Is there a way where multiple results will be shown if they exist, but for one result to appear if only one result exists?
I am trying to return several values >10K, >5K, >3K, >1K,
View 7 Replies View RelatedI have created a query in an Access database which returns multiple results. I need to be able to run this query within Excel, but only showing the total of the results in a single cell.
I also need to run it multiple times on the same sheet using different dates. The Access query asks for a date each time it's run
I have only found ways to retrieve the entire results of the query into Excel based on the settings within the query.
I have a situation where I have source data formatted like this:
Document ID
Document Name
Author
[Code]....
What I would like to do is to get each author on a separate row. I am able to do that using Text to Columns using the carriage return and then doing a transpose. I've also seen some threads where VBA is used to accomplish similar. However where I am stuck at is getting everything else to drop down accordingly. I.e. with the data set above I'd like to get to:
Document ID
Document Name
Author
[Code]....