I have a list of products with parts for each. I made a drop down of each product and what I want to happen is after I select the product it will autopopulate all the part info for that specific product. Each part has a description, sku, quantity and price.
There are up to 10 parts per product so I dont know how to do this. Normally I would use vlookup or something to that degree but I dont know how to display a bunch of information.
I am trying to make a search form in Excel 2003 where I can lookup data for a specific value which may have multiple rows. I have several columns, column A Tracking Number, B Order number, C Item Number, etc.
For example; Order number S6001 has three item numbers on rows 1,2,and 3.
I can get the data on row 1 using the basic "LOOKUP" function but not rows 2 and 3. How do I have all of the line items (rows) returned for that order number.
Is there a lookup formula that will give me multiple items? so in my example attachment i want to look up risk from section A to give me the item in section B, but for risk 7 for example it has multiple items (a,b,c), how would i get all them? even better would be if its possible to have in seperate cells
View 4 Replies View RelatedI was wondering if there was a way to create a pop-up list to appear when a user selects a certain cell that would provide the user with a list of (in this particular case) inventory items. The user would select which items they require for the task at hand and the total $$cost of the items would appear in the cell once is is deselected.
View 4 Replies View RelatedI need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
I have a long list written twice in 2 worksheets worksheet A has a list with some of the numbers repeated. worksheet B has the same list (none repeated) and another list with new numbers beside it. What I need is to take the new numbers from worksheet B and put them next to their correlating number in worksheet A. With many of the numbers being repeated I need something to identify and repeat the new #. I'd copy and paste and drag etc. but there's about 21,000 numbers to go through.
View 3 Replies View RelatedI am making a spreadsheet for food and calories, On sheet one i have a meal tracker, I want to have a drop down list in colum 1 that references the food list on sheet 2, column A=food, column B=Protien, column C=Carbs, column D=fat. The values from Sheet 2 columns B-D would then be inserted into Sheet A in the respective slots. I have fiddled with vlookup and tried numerous things but I can't seem to figure this out.
View 3 Replies View Related=LOOKUP($C1,$A1:$A1,$B1:$B1)
Though i wanted to rid the Na error from it when C is blank
I seen a few ways, And wanted to try the method of using Countif becouse it can be used withen the same cell without using extra cells like another method i seen "=IF(ISNA(A1),0,A1)"
what i know of countif
This function counts the number of items which match criteria set by the user.
=IF(COUNTIF($A1,"")),LOOKUP($C1,$A1:$A1,$B1:$B1),"")
so how the heck can the countif function be used to rid the NA of a lookup. i just cant see a relationship between counting a lookup.
(File is attached here)
I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.
I have a spreadsheet, in which I need to lookup invoice numbers from a raw data file with ~5,000 line items on a daily basis. The lookup is based on two criteria searches (1) search product type (2) search product make. In this example, I have 4 product types:
1 – car
2 – truck
3 – boat
4 – motorcycle
For this example I want to search invoices; (1) first search for cars only (2) search for product make. In my attached example, the first item (cell E2) would return invoice number 7147875-FRD from the raw data file. The second item (cell E3) would return invoice number 7147877-NSN.
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 have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.
I find writing out what I want helps some times so let me try it here.
So my variables are:
Product (there are 22 products)
Supply Less than (inset number)
These are the two criteria I want to use to produce a result.
The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.
What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.
The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
I am trying to find the min value in one column for each values in another.
Length Name Min for Name
1 Dave Formula??
2 Joe Formula??
3 Fred Formula??
3 Dave Formula??
3 Fred Formula??
3 Dave Formula??
5 Joe Formula??
I have added this example:
Test lookup main supplier.xlsb
I have a list of 5000+ items that I need to check (only 6 of them in this example in the red table).
I have a list of 25000+ items in a lookup table (only 10 of them in this example in the blue table).
Each item can have more than one supplier, but has only ONE Main supplier.
I need VBA code that determines if the supplier in B1 already is a main supplier for that specific item in the lookup list (yes or no).
I tried filtering it but that appears to be too slow for 5000 x 25000 items.
I have tried with VLOOKUP and Match, but I do not know how to combine the two conditions.
I am using a sumif formula where if column b is a certain number then it will add up the value in column e.
This works fine, however now I wish to add another filter, which is only for it to add the values in column E if the value is b is correct (as above) and also if column c has a value of 100.
I have this work sheet with several formulas in columns Z to AD. All of them highlighted red work fine as for as I can tell. I am stumped with the one needed for the cell highlighted yellow AD2. It should count all the dates in AD1 that are Requested Changes Made and/or Rejected in Column "M". AD2 is a total of today minus 8. Equipment Change out - TEST.xlsx
View 5 Replies View RelatedInstead of doing a long long formula that is =VLOOKUP(x1,....)+VLOOKUP(x2,.....) is there a faster way of doing it in the one formula?
View 7 Replies View RelatedI have two columns of data each with repeats. I want to be able to look up the third column of data and then list the multiple results attaching to the first two columns.
I have attached a dummy spreadsheet of what I am trying to achieve.
I want to do a function similar to a vlookup. I want to input a key
into a cell and have the relevant names listed. The problem I have is
the vlookup only shows one of the multiple names. Is there a way to
list all of the names?
Input Key: ____
KeyName
1414BRODRENE DAHL A/S
1880MAN FERROSTAAL AG
2356HEITON BUCKLEY LIMITED
2356HEITON BUCKLEY LIMITED1
2356HEITON BUCKLEY LIMITED2
2356HEITON BUCKLEY LIMITED3
3867STAVANGER RORHANDEL A/S
4367CLEANAWAY LTD
4618ALUKONIGSTAHL GMBH
4618ALUKONIGSTAHL GMBH1
4979MARMON/KEYSTONE ANBUMA N.V.
Ideal Output
Input Key 2356
HEITON BUCKLEY LIMITED //all brought up by a formula
HEITON BUCKLEY LIMITED1
HEITON BUCKLEY LIMITED2
HEITON BUCKLEY LIMITED3
In column A i have list with the names like this.
ROSEHILL 1
ROSEHILL 1
ROSEHILL 2
ROSEHILL 2
ROSEHILL 2
ROSEHILL 2
ROSEHILL 3
ROSEHILL 3
ROSEHILL 3
When i use the Vlookup to return the value in column M it does not work as you would expect, i have done some searches and found a few different formulas but can't get any to work, the data i am searching is listed on one sheet and the criteria and the formula is on another.
I tried this formula as an array but no joy.
=IFERROR(INDEX(Data!$A$2:$M$900, SMALL(IF($GO$4=Data!$A$2:$A$900,
ROW(Data!$A$2:$A$900)-ROW(Data!$A$2)+1), ROW(1:1))),"" )
I am importing some text files into an excel worksheet. I want to change the format just a little bit. For each date I have 5 rows (pixel: 25, 37, 50, 100, and 200). When I import my data the date is in the same row as my pixel numbers. I want to place the date in another column to the left of these pixel numbers.
Below is an example of my "spreadsheet" The first 5 lines (with date "2008_308.txt" next to each pixel number is what I want it to look like and the next (with date "2008_309.txt" is how it is imported with only one date above the pixel numbers. I could do this manually, but I have many years of data to import. And would like a way to kind of copy/paste these multiple items at the same time. Any ideas? .......
I am new to writing macros and am wondering what is the easiest way to create a drop down and select multiple items separated by comas. The people who are going to input data into this spreadsheet are field people and not technically savvy so need to design something that is just a matter of cliking on the drop down and selecting multiple items and they should show on the same cell separated by commas.
View 9 Replies View RelatedIn column A, it contains the monthly salary data of each employee, then in column B, it contains the classification level data, Like Grade A, Grade B and Grade C. In this exercise, it want to find out the statistic as follows:
1. How many employee's monthly salary is below 10K and their classification level is Grade A, B or C
2. How many employee's monthly salary is above 10K and their classification level is Grade A, B or C
So any formulars can do that in instead of using the sorting method?
I am trying to copy multiple items from a listbox.
I have a listbox which populates with all the email addresses in my global address list.
From this I select email addresses which move to another listbox.
I then want to copy these selected email addresses to a spreadsheet when I hit a button.
I can get it to copy the 1st one but that is it.
I know this will be very simple to do but I just can't think what it is.
I'm working on a travel form, which allows people to enter a travel itinerary. From that, I want to be able to extract the cities where they are spending one or more nights - so that I can then do the calculations for accommodation allowances.
I am able to calculate the number of nights stay in each city where there is an overnight stop. But I'm stuck on how to extract every combination of City and Nights where Nights is greater than zero - there is no need to calculate for cities with no overnight stay.
I have attached an example spreadsheet.
Some notes:
- the itinerary and accommodation tables have to remain separate, as they hold more data than in the example,
- the itinerary table can't be sorted for number of nights. It has to be in chronological order for each segment!
Every lookup function I check out seems to demand a sorted table.
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
Is it possible to restrict the values of cells in a particular column to entries defined in a list BUT to allow each cell in that column to display multiple items from the list (seperated by a comma for example). I've attached an example of what I would like to do -
In Sheet "2010 Data" I want to be able to select multiple values in column F....(the values are defined within the list named "Platforms" on the worksheet called "Lookups"
[URL]...I was trying to use this thread's solution macro on my sheet that is only consolidating columns A, B, and C with a couple thousand lines and I've had it running for over an hour now. There hasn't been any errors but is there any particular reason that might be causing it to take longer than it should aside from having to go through a few thousand rows?
Basically, I only want 1 Version of data in Column B and any other rows that has a match in column B, i want to consolidate unique data from Columns A and C.
A B C
AB 123 AAC
BA 131 AAC
AB 222 CAA
Res:
A B C
AB 123, 222 AAC, CAA
BA 131 AAC