Here I would like to get your valued guidance for the usage of "IF" formula. I am having two sheets one is new XL-1 and other is old XL-2. I want to have a item number from the old file corresponding to the same item descprition in the new file. I am attaching the snap shots of two files which is more explanatory than describing in words here.
View 2 RepliesI am using a vlookup and have a problem. I am assigning a category to an item number based on the first two characters of the item number. For example item number 60123 would equal scrap because of the first two characters of 60. But the item number can begin with either a number or letter. Here is the formula I am using that works for item numbers that begin with numbers:
=VLOOKUP(VALUE(LEFT(E2,2)),Sheet3!A:B,2,FALSE)
It works fine until I reach a item number that begins with a letter, then I get the dreaded #Value error. If I take the value out of the formula then it works for the letter based number items but not for the number based item numbers.
I have 3 routines to (1) create an empty zip folder, (2) place a file into the zip folder and (3) create a mail item and attach the zip folder to it.
The zip folder is successfully created. The mail item is also created and displayed, yet the zip file does not get attached.
Option Explicit
Dim FileNameZip
Sub NewZipFile(strFilePath As String)
If Len(Dir(strFilePath)) > 0 Then Kill strFilePath
Open strFilePath For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
End Sub
Sub MakeZipFile(strPath As String)
Dim strFileName As String
Dim objApp As Object
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False.......................
I am using Vlookup to compare our item number with the suppliers item number but the result is removing the decimal point on some but not all items.I have included a small sample but it concerns almost 10.000 items.
View 9 Replies View Relatedi need a formula in Cell B2, that will look in raw data and then look for the team in criteria (named range) then return the number of item for each manager....
View 9 Replies View RelatedExcel Userform
VB:
'enables user to click [U]highlight and select[/U] an item in ListBox1 and ListBox2 item (same row in index) is also [U]highlighted[/U] (highlighted only not selected)
Private Sub ListBox1_Click()
ListBox2.ListIndex = ListBox1.ListIndex
End Sub
Question: Is it also possible to enable a user to click to select an item in ListBox1 and ListBox2 item is also selected simultaneously (same row in index). Is there excel vb code to do this?
I think the code may be along the lines of the ListBox SelectedIndex property. What would be the Excel VB code equivilant for the ListBox SelectedIndex property, if so?
numberdesc
1_______yellow
1_______yellow
1_______blue
2_______purple
2_______purple
3_______green
3_______orange
4_______black
I need some way that can identify when the item in the description column doesnt match the first item of the same number- for example, here the 1-blue and 3-orange would be flagged because they should match the 1-yellow and 3-green.
I need to do this on a much larger scale (approximately 20,000 data points), so I wanted to create a formula or macro that could do this for me.. I thought making a reference page with would work but I keep getting an error.. I haven't done VBA in a while, so I may have syntax errors.
If Range("A2:A9").Sheets("Sheet1") = Range("A2:A6").Sheets("Ref") And Range("B2:B9").Sheets("Sheet1") = Range("B2:B6").Sheets("Ref") Then
Range("C2:C9").Sheets("Sheet1") = "x"
End If
I am getting close to finishing the drop down menu capability when filling in column L in tab Transactions. However, there is a snag. When I enter part of account say "fin" (the important part here is that the part of the word should not be the beginning of the account name) - then I select an account from the menu - but it does not stay in the cell if the part of the name is the beginning of the account name - all is fine.
View 4 Replies View RelatedI've in a sheet something like this:
A B
Number Item
2 Apples
1 Orange
2 PineApple
5 Orange
1 Apple
I need to do a code to get how many Apples Orange, PinneApple or somethig is in the range
For Example:
3 Apples
6 Orange
1 PinneApple
The items are variable. It's starts in the second row, and finish in the next blank row.
I am trying to number rows based on criteria. when the value changes add a 1. This is what I need to have happen
Apple - 1
Apple - 1
Apple - 1
Orange - 2
Orange - 2
Plum - 3
Plum - 3
I can get an if statement to number everything 1.
I am trying to see if there is a way using count or another option, of using one formula to look in a column and then spit out how many of each item are there:
i.e: lets say it says a, b, c, d etc...
Can a formula go through and say you have 3 of a, 5 of b and so on
I understand the basic of count sum iserror etc, just not sure if excel can do what I am wanting or if i should use access.
I have two workbooks, (Invoice.xls) and (Inventory.xls) both using sheet1. When I fill out an invoice, with lets say 10 items on sheet1 column B, I need a macro to find those items in workbook "Inventory" sheet1 column A. When the item is found, I need the qty I entered on the invoice workbook "Invoice" sheet1 column C (same row as the item number) to be subtracted from the current total for that item in workbook "Inventory" sheet1 column C. I would like to run the code from the workbook "Invoice" before I do a "Save As".
I have found other Vb code on this site that seems very close to what I need, but I just don't know enough to make the changes necessary.
One has the master data in it ( Item #Lot#/Cases per pallet/date ) = 30K rows
Second is the list of item #'s .
I need to find the MODE of cases/pallet by item number.
I am using a vlookup with a table that has data which uses a number which correlates to an item. A person types the number into a cell which triggers the vlookup. See example
35apple37orange40lemon
So..if someone enters let say number 36 into the look up cell..since 36 does not exist in the table the vlookup returns the value next to cell 37. Is there a way to use vlookup so that if a number it is looking up does not exist it wont return the next value? I would like to return an error message if the number being looked up does not exist.
attached is my spreadsheet. What I would like it to do is when someone marks a "N" or some other symbol in the box showing that supply not there; that excel copies the item #'s for the products listed and calculates up the prices for those items.
Thank you in advance for your help. If at all possible if it could calculate the order on another sheet such as sheet 3 that would be fantastic
I use the code below to enter a value from a list box in a cell on a workssheet. Is it possible to code VBA to enter a number for the position of the selection in the listbox to a cell in a worksheet rather that the actual value from the list box. For example if my list is:
Option1
Option2
Option3
And I click on Option2 in the list, I can sennd the value 2 to a cell on the worksheet rather that the value "Option2' from the list.
Private Sub ListBox1_Click()
Sheets("SA").Range("SA_Poistion_To_Archive_A_New").Value = ListBox1
End Sub
When copying the formula =SUMIFS(G3:G103,I3:I103,"3") how do I modify it so that only the "3" rolls to the next integer and the rest of the formula remains constant? In other words the 3 would roll to a 4 when copied down a column and the rest of the formula does not change?
View 4 Replies View RelatedHere is my current formula:
=IF(ISERROR(INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0))),"0",INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0)))
I would like to add one more item to match. I need to match column H in my current sheet, to that of column E in sheet 3. Just not sure how to go about it.
If I have 1,000 entries in a column is there an easy way to determine the number of times a certain item is in the columns. For example if field A1 is "qwerty" I wan to see how many times "qwerty" or field A1 appears in the entire column?
View 2 Replies View RelatedI’m getting data from query. My data has fields: Item No, Customer ID & Sales rep.
I need to find out for each Item number which customer put order and who was the sales rep. (Excel 2002). see file attached.
I want to write a formula/script that searches file names in Column "A" for an item number located in Column "B", then combines all file names containing that item number into Column "C" with comma separations. Each item number will have a different number of file names associated with it (between 1 and 10 files), and there are about 2000 different item numbers being crossed with about 7000 file names.
Here is an example completed with a CONCATENATE formula:
File Name
59481A_1.jpg
59481A_2.jpg
59481A_3.jpg
59481A_4.jpg
59481A_5.jpg
59481A_6.jpg
Q110XL_1.jpg
Q110XL_2.jpg
C5710_1.jpg
C5710_2.jpg
C5710_3.jpg
C5710_4.jpg
Item Number
59481A
Q110XL
C5710
Final Image List
59481A_1.jpg,59481A_2.jpg,59481A_3.jpg,59481A_4.jpg
Q110XL_1.jpg,Q110XL_2.jpg
C5710_1.jpg,C5710_2.jpg,C5710_3.jpg,C5710_4.jpg
I am kind of new to Excel programming and would like to know if there is a solution to my problem. What I need is to have a dropdown list (I know how to make this) then select an item from the drop down, then based on the name i select, Excel automatically fill up the rest of the cells.
For example, from the drop down list I select the company name then Excel automatically type the address, phone number, Fax number and the contact name of the company for me in corresponding cells. Is there a way to do this?
I have three companies in column A, In Column B I have a date of when an item was sent to them, in Column C I have the amount of days this item has been out with that company. Is there a formula that will count the number of days an item has been out with one of the companies per month, and find an average if more than 1 item has been sent per month. Two items have been sent to company A in Sep 09, one item has been there 10 days the other 20 days, the answer I would be looking for is 15.
View 4 Replies View RelatedI have a drop down box on a worksheet and once I have selected the item I want from the list, I would like the price of that item to appear in the cell next to it..
View 2 Replies View RelatedIn the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.
sortbyDates.xlsx‎
Col A contains SS#'s and Col B contains names. Quite often there are duplicates and sometimes triplicates because they may be sending payments for multiple providers. We charge one fee per client (unique SS#), regardless of the number of times they may be on the spreadsheet.
I can get a grand total by summing the unique items by using this formula:
=SUM(IF(LEN(A2:A4970),1/COUNTIF(A2:A4970,A2:A4970)))
and then doing a CTRL-SHIFT-ENTER to put brackets around it so it will "take."
However I now need to show the fee by each unique name. Right now I am inserting Col C, typing in 3.00 into C2 and copying on down, but then have to manually remove the amounts in the rows which contain duplicate ss#. Long painful process. How can I put a formula in Col c so that it will only show fee one time by the first unique ss#?
I have created a drop down list for items we quote on. each item has a different formula depending on the variables on sheet one Sheet1. I have listed the formulas in the column next to the drop down list for each item, but our now need the main quote to put in the formula in another cell when the item is chosen from the drop down list.
View 2 Replies View RelatedI have a sheet that lets the user enter a category and then a sub heading, I need the data to be displayed under each category heading in alpha/numeric order as each new entry is made into the list.
Please see the attached sheet for details. Book3.xlsx‎
I am trying to use COUNTIF formula to count how many item in a column that meet certain criteria, say between 10 and 20...
=COUNTIF(G1:G100,"AND(>10,<20)")
I have 3 different costs for an item, 15 different items. The costs very depending on the delivery state. Is it possible to write a formula that will retrun the right cost for an item, depending on the delivery state?
if ap10 = "CA" then look up in range AH2 to AH18
if ap10 = "GA" then look up in range AI2 to AI18
if ap10 = "ME" then look up in range AI2 to AI18
if ap10 = "PR" then look up in range AI2 to AI18
if ap10 = any other state then look up in range AG2 to AG18
cell AP10 being the cell that the state is listed in
Cell U10 being the cell that item is in (a drop down menu)
This is the vlookup formula that will get one price.
=IF(ISNA(VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2,FALSE)),"",VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2, FALSE))