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 RepliesI 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 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 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 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'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 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‎
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 have a nice case to prepare and need weekly riskfree interest returns on the UK treasury bond. Right now I have daily returns and want to convert them into weekly. The problem is, weeks do not always contain five trading days. How can I turn my data into weekly average riskfree interest returns?
******** ******************** src="*********>*********>Microsoft Excel - UK interest daily.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1DateInterest rateweekday 24-Jan-889.871 35-Jan-889.992 46-Jan-8810.053 57-Jan-8810.094 68-Jan-8810.135 711-Jan-8810.051 812-Jan-8810.072 913-Jan-8810.283 1014-Jan-8810.114 1115-Jan-889.985 1218-Jan-889.871 1319-Jan-889.962 1420-Jan-889.913 1521-Jan-889.864 1622-Jan-889.715 1725-Jan-889.71 1826-Jan-889.762 1927-Jan-889.693 2028-Jan-889.564 2129-Jan-889.555 221-Feb-889.741 232-Feb-889.622 243-Feb-889.643 254-Feb-889.684 265-Feb-889.755 278-Feb-889.931 289-Feb-889.852 UK interest daily [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
In Row 3, starting at cell C3, I have a list of Stores in the format Store A (Town 1), Store A (Town 2), Store B (Town 1) etc
In Rows C4 to CA7 I have 1 or -1
I want to count the number of instances of 1 for Store A
I have tried the following formula, which is returning 0
{=SUM((C3:CA3="Store A *")*(C4:CA7=1))}
Which is the best way import many (thousands of) files in text format into a single excel spreadsheet. Importing files with e.g. 50 lines to create 50 columns for rows for every file
View 2 Replies View RelatedI'm trying to create a workbook that will calculate times. Currently I have a worksheet that has, for example Incident #1, Date/Time In, and Date/Time Out. At the end of the row it will calculate total time in hours.
I need to create a second worksheet that will have Incident # to correspond to the incident # on Sheet1, with Date/Time In and Out. However, on this sheet there could be more than one occurance of Incident#1, and multiple times in an out. That total will be deducted from the total of incident#1 on Sheet1.
The easiest way to explain this would be...say for the sake of argument Sheet1 calculates the duration of a vacation in hours (lets say 168 hours). Sheet2 will calculate how much of that time was spent doing business (lets say 2 hours on day1, 3 hours on day 4). In the end I want to subtract 5 hours from 168 hours giving me 163 actual vacation hours.
My problem is, I don't know how to go about using the values in multiple rows in Sheet2 that correspond to the row in Sheet1.
I need to apply filters based on 2 criteria
Filter1Dropdown SelectionFilter2Dropdown SelectionHeader1Header2Header3Header4Header5Header6
Depending on what is selected in Filter 1, lets say it applies to column F and i get a data set
Now with the filtered dataset of Filter 1, I should be able to apply Filter 2 or vice versa.
Also at the end, I need to release all filters so that i can see the entire data set.
I am having a hard time with this one. I have 3 cells.
A1 Dallas, TX.
A2 Chicago, IL. (Sometimes Blank)
A3 New York, NY
I would like this info to transfer to another cell like this
Dallas, TX / Chicago, IL. / New York, NY (All in one cell with the "/" to divide.
When the "Chicago, IL." is missing it would look like
Dallas, TX / New York, NY
i have created that will sort a db into dozens of sheets, now i need to be able to reset the process. There are 3 sheets i need, "balances", "trans", and "template". im unsure as to how define the sheet names so that the dSheet variable will recognize them. the way i have it now just deletes everything.
Sub reset()
Dim dSheet As Variant
Sheets(1).Select
On Error Resume Next
Do Until Sheets(1)
dSheet = ActiveSheet.Name
If Not dSheet Is "balances" Or "trans" Or "template" Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Else
ActiveSheet.Next.Select
End If
Loop
End Sub
I have 134 parts and each part is available in 5 price groups - these are static items that need no calculation as they are completed on another worksheet. I have 400+ "items" each item is made up of various parts. I need to calculate the cost of each item. Hard to explain without example so I have attached an example.
View 2 Replies View RelatedI have made a spreadsheet but would love to be able to incorporate multiple 'Clear Data Buttons', (Macros)
that clear cell data.
I have attached several screen shots of my spreadsheet, with the URLs listed below.
(Images 1-6)
http://tinyurl.com/qba57l
http://tinyurl.com/od9upb
http://tinyurl.com/ovaeej
http://tinyurl.com/pctsvj
http://tinyurl.com/oc2qqm
http://tinyurl.com/qdhzge
Spreadsheet (xls & xlsm) in zip format
http://tinyurl.com/qczlap
It is a trading order sheet that I want to use, but also upload to a blog that I have just
started, relating to trading as a free download, hopefully it may benefit some users/visitors etc.
OK, regarding the macros.
I'm not quite sure how it is best to do it.
In total there are 13 'Clear Buttons.'
11 are 'CLEAR DATA' Buttons
12 is a 'Clear ONLY ORDER SHEET DATA' Button.
Finally 13 is a 'Clear All DATA!' Button
I think it's best if I give the cell location of where I would like the macro buttons located and what data they clear.
Clear DATA - Button 1 - (Is in Cell F3) - To Clear Data, Columns D,E,F & Rows 4 to 13)
Clear DATA - Button 2 - (Is in Cell N3) - To Clear Data, Columns G to O & Rows 4 to 13)
Clear DATA - Button 3 - (Is in Cell U3) - To Clear Data, Columns S,T & U & Rows 4 to 13)
Clear DATA - Button 4 - (Is in Cell F15) - To Clear Data, Columns D,E,F & Rows 16 to 25)
Clear DATA - Button 5 - (Is in Cell N15) - To Clear Data, Columns G To O & Rows 16 to 25)
Clear DATA - Button 6 - (Is in Cell U15) - To Clear Data, Columns S,T & U & Rows 16 to 25)................................
I have a workbook that query's our case tracking database and pulls data into a csv. I am trying to write something that would do the following:
1) Prompt the user to enter in a case number
2) Open up the following file:
-"C:Documents and SettingsDetails.csv"
3) Look through A:A for a match of the user entered number
4) If a match is found then pull the data from cell B in that Row
5) Output the data (from row b)into a text box on the user form along with the original entry made by the user.
i have this data.
WeekFRM CURRCY Account val val2 val3 val4
42100¢BBHBANK403522
43100¢BK200 001310
44100ABBHBANK3013
45100ABK200 9043
46100CBBHBANK10106
47100EBBHBANK903420
48100EBK200 503527
How do i get this into a chart, with week along the bottom, Amount on the left. This is starting to be a pain now as i need to seperate by currency, but a pivot chart looks really realy messy with all the drop down boxes and stuff. I dont mind using drop don boxes, in fact, its going to be essential. but the pivot one look horrible.
I have attached a report I do monthly, a duplicate ticket report.
I am able to work out the duplicate tickets and parent tickets but
I need a formula to do 1 more task.
I need to check all the duplicates in A:A and also the assignment group in K:K.
If the ticket is a duplicate and the assignment group is nz - dms* i need it to count these.
Probably simple but Im still pretty new to all this. I came up with the following but its obviously wrong or I wouldn't be here.
COUNTIF(AND('Duplicate Calls for'!A:A,"Duplicate"),('Duplicate Calls for'!K:K,"dms - nz*))
about doing the opposite of consolidating multiple sheets into one: I have a large database and want each row of columns in their own new sheet. Rather than copying and pasting each row that I have, is there a code or formula to quickly command this to happen?
View 9 Replies View Related