Return A Value Based On 2 Items From List
In Excel 2007, I need to be able to return a value (definition of something) based on what is selected in 2 other cells.
(See Attached)
If in cell C2 I select "competency 1" and in Cell C3 I select "Expert", I need to be able to return the definition for someone that is an Expert in Competency 1 in cell D2.
Then, if in cell C5 I select "competency 2" and in Cell C6 I select "Leading", I need to be able to return the definition for someone that is Leading in Competency 2 in cell D5. And I'll be doing this about 10 times on each sheet.
All the tables with info are in another worksheet. I can do a vlookup if I'm just using one thing to match, but how do I match 2 things to return the definition?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Remove Matching Items From A Workbook Based On A Master List
I have Workbook A populated with skus from a report and I want to remove all rows in this workbook that do not have a matching sku in Workbook B. Each workbook contains only one sheet and the skus are in column 1 of both workbooks. Also, I am looking for a good resource/tutorial for working with workbooks, worksheets and ranges within. I don't anticipate this being the last time I will be confronted with this and would like to learn more.
View Replies!
View Related
Return Value Based On Validation List Choice
I have created a cascading data validation list, and I would like to have an adjacent cell auto populate according to the dropdown selection. Data Eg: Oranges 1100 Apples 1101 Pears 1102 Grapes 1103 If the user selects "Pears" from the dropdown list, I would like the adjacent cell to populate with the numerical code for "pears", in this case 1102. I am designing a new expense report form at the office, and I am at a standstill with this problem.
View Replies!
View Related
Compare Duplicate Items And Return A Value
if there is a way to find duplicate items in a list and return a value, if a duplicate exists. Basically I have 19 buildings each with a separate bill of quantities. I'm looking to compare and compile a comprehensive list. So that duplicate items in different buildings can be compared side by side. I have attached a sample of what I am trying to do.
View Replies!
View Related
Index Function - Data Identified Based On A List Of Account Numbers And Return This Data To A New Sheet
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet. In "Money" I have: IE; columns B, C respectively have cust #A100 & 20.00 columns E, F respectively have cust #B100 & 40.00 columns G,H respectively have cust #C100 & 60.00 Etc. (above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars) So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes. I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
View Replies!
View Related
Lookup & Return Multiple Items
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.
View Replies!
View Related
Listing Items In List Once
Is there a way than I can take a list of data and create a new list but only listing each value once if there is a duplicate value within that list? Original List ========= Apple Pear Orange Orange Pear Apple Peach Plum Pear New List ====== Apple Pear Orange Plum Peach
View Replies!
View Related
List Of Items In A Column
I have a list of items in a column. I would like to have a userform pop up with a list box (?) of all the items and I want to be able to select multiple items to perform an action on the row corresponding to the selected item. I am thinking of other examples I have seen where there are two list boxes (?) and then arrow buttons between them. When you click the arrow the item moves from one list box to the other to see which have been selected. I have never used list boxes before so if you have a link to some sample code that would be useful.
View Replies!
View Related
List Unique Items ...
I have a list of >50 contract numbers listed in cells A1:A350, with several of the numbers listed more than once. What I would like to do is, on a separate worksheet, list each contract number only once, in column A.
View Replies!
View Related
List Items Filtered
I have a list 10 of names in Column A. ( On sheet 1 ) I then filter column A to show only three specific names, or four names etc. . . On Sheet 2, in cell A1, I would like to list the three filtered names from column A on sheet1 e.g. if the three names showing in column A ( Sheet 1) are: john, mary, jane . . . then on sheet 2 cell A1, I would like to see: "john, mary, jane"
View Replies!
View Related
Count Of Items In List
I'm new to the forum - I've done a search but cannot find the answer to my problem. I work in a hospital and have a list of consultants who refer for a particular type of scan over a period of time. Each time the consultant refers their name is added to a list so I get a list like this: Dr Smith Dr Smith Dr Jones Dr Smith Dr Paeker Dr Paeker Dr Jones etc...
View Replies!
View Related
Searching A Column For Specific Text To Return A Number Of Items Found
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ... the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
View Replies!
View Related
Return Top N List & Return Corresponding Data For N
I am trying to find the largest value, second largest, and third largest value for a column of numbers then return a content value for the adjacent column that contains the name for which each. The problem I am running into is when the second highest has the same value as the highest and also when the third value is zero (n=meaning there are only non-zero numbers for the column). I am using these formulas currently whic work when the numbers are not the same and I have atleast 3 non-zero numbers but I need to modify in order to account for the problem stated above...
View Replies!
View Related
Adding Items To A List Box
I have the following code used to add items of expired IDs in a listBox,, it actually works well for adding the ID type, but it doesn't add the details of the expired ID completely .. It adds only the details of the last expired ID (I think the code overwrites the details) I hope that my problem is clear for all, Can you please find me a solution for that?
View Replies!
View Related
Deleting All Duplicate Items In A List
I am a tax auditor, and I am working with excel and I have two lists. One is a complete list of items, both taxable and non-taxable, the other is a list of just non-taxable. I highlighted the non-taxable sales and copied and pasted them under the main list, and I want to delete all the non-taxable items. Basically, the only duplicates in my list are non-taxable, and I need to delete ALL of them.
View Replies!
View Related
Getting Values For Dropdown List Items
I created a drop down list for products my company sells. Now I am wondering how can I get Excel to display the cost of the item in another column so I do not have to type in the cost each time I bring that product up. I have searched google and this site but of course I can't describe my search well enough to find what im looking for. I'm sure this has been explained I just can't find the answer to my problem.
View Replies!
View Related
Colour List Items By Type
I have a standard pull down list in a worksheet containing names of rivers, canals, lakes etc Is it possible to colour code by type so that all rivers in the list are coloured - say blue, canals - brown etc... Each name in the list has the designated type included i.e River Thames_Ri (_Ri = river, _Ca = Canal etc...)
View Replies!
View Related
Identify Items In List Existing In Another
In an excel file I have 2 lists of files. List #1 has about 6,000 filenames and List #2 is a subset of List #1. I am looking for a solution which will help me highlight/identify those files which are also in list#2. Ofcourse one way is to sort both lists and then place them in consecutive columns (say column A and column B) and then in another column (column C) use a IF function to identify if A1=B1 (or A1 <> B1) and then take it from there. However this approach helps a litle becuase there may be a gap in List#2 and thus for a great # of cells in column C i will end up getting false answer. Hope you're following me?? The underlying requirement for this is rather lenghty so I'm skipping those details. However I would like to share that so far and whenever I come across doing this exercise (about 3-4 times a month), I ended up achieving the results but it takes me hours.
View Replies!
View Related
Vlookup On A List Of Items Where Appears More Than Once
I'm trying to do a vlookup on a list of items where the item I'm looking up may appear more than once. when this happens it will only post the result of the first one it comes to in the list when I use the standard vlookup. does anyone know of a way I can get it to report all the results for a value that appears more than once? CodePO Line DescriptionQty DueUOM 745000738031015mm Sandstone 3,018M 745000741791015mm Sandstone 3,018M 745000745091015mm Sandstone 3,018M 1245000738032015mm Jet 3,018M 1245000745092015mm Jet 6,036M 4645000741792015mm Poppy 3,018M 4845000741793015mm White 6,036M 4845000745093015mm White 3,018M
View Replies!
View Related
Non-Selectable Dropdown List Items
I would just like to know if there is a way to add items to a drop down list that cant actually be selected. Asking because I have a table from which the dropdown list gets populated and the user then selects a particular row but would like the header fields to also be added to the list just for reference. I have attached the spreadsheet in question for reference.
View Replies!
View Related
Counting Items In Filtered List
{=SUM((C2:C8="black")*(B2:B8="sneakers"))} I’ve got following formula counting different kind shoes that are black and sneakers and above formula work very well. What I wonder is if you can convert above formula to count these items in a filtered list. Could you use the subtotal-function in any way?
View Replies!
View Related
Formula To List Duplicate Items
I have a long list of work items that is referenced by a unique 7 digit number. I need to create a formula to identify the specific work items that is being duplicated in the list and generate that number. Example is as follows Work Item# 1234567 1234568 1234567 1234569 1234568 Based on the above list, I need to create a formula that generates all work items that is being duplicated.In the example above, the formula should generate the following work items: 1234567 1234568
View Replies!
View Related
Count Items Repeated In A List
I have a table of data, from which I have created lists for “items created within a period” (the items are paintings on canvasses). Each list displays which canvasses were completed within 2 dates. My main data table is large (over 1000 lines and growing) and I need to extract the total quantity of each canvas size that appears in each period list. My sample attached shows sample data, with corresponding lists of canvasses created within each time period, together with the result I am looking for (shown in red).
View Replies!
View Related
Summary Count Of List Items
I have a sheet with a top to bottom date/chronological list of Horse races arranged so; Column 1 Column 2 Column3 Column4 Date/Time Race type Empty Horse Name e.g 01/03/2008 12:30:00 1m stks Mad Robber 01/03/2008 12:30:00 1m stks Don't Run 01/03/2008 12:30:00 1m stks Laughing boy etc Then next race i'm looking for a Macro that will add up the number of horses in each race and enter the number for a given race in Column 3 for each horse in that race. So the example above would become; 01/03/2008 12:30:00 1m stks 3 Mad Robber 01/03/2008 12:30:00 1m stks 3 Don't Run 01/03/2008 12:30:00 1m stks 3 Laughing boy Then next race down the list; 01/03/2008 12:40:00 2m gtd 1 Walk fast etc
View Replies!
View Related
Count Instances Of List Items
is there a formula that gives a true or false statement upon searching a list (ie A2:A6) and finding multiple entries? like, when i search A2:A6 and there are two fields that contain the word "hello", is there a function to say "true, there are multiple entries of the word 'hello'"
View Replies!
View Related
Populate Combobox List With ONLY Unique Items ()
Populate Adv Filter Criteria from Inputbox with valid data I've set up a macro to extract 3 different sets of data using Advanced Filter (same data range, different criteria & extract ranges) There are multiple options (different individual dates) to satisfy the criteria (a date) for each of the 3 extracts and I want the user to select the criteria (a specific date) from the range of unique values in the data range upon which the Advanced Filter criteria is going to be applied. (A bit like the effect provided by Autofilter) My initial attempt was to capture the criteria date from the user and populate a cell, and then have a vlookup function in another cell check if that crieteria value exists in the specified range. I then ran a loop in the VBA code until the value is valid (ie. exists in the range). However, to streamline this I was hoping to allow the user to see a list of the valid (available) dates when they respond to my Inputbox request for the criteria.
View Replies!
View Related
Formula To Create List Of Unique Items
I have a column that looks something like (it is VERY long and has over 20 different names): abe abe abe abe ben ben ben cat cat john john john john tex tex I want to create another column (automatically) that lists each of of those once: abe ben cat john tex Any idea of how I can do this automatically?
View Replies!
View Related
Limiting Number Of Items In A Range From A List
Here's my problem. In column B, i wish to allow users to choose 15 items for which i have created a list using validation. Each cell in the range B1 to B15 will contain one item. I have managed to allow them to select only 15 items by locking the other cells and allowing only the range B1 to B15. However, i need prevent users from entering more than 5 identical items from the list. They can only choose a same item up to a maximum of 5 times. Would you guys have an idea of how i can get around this problem? I really need to prevent them from entering the same item from the list more than 5 times and not only warn them.
View Replies!
View Related
Vlookup To List Multiple Items With The Same Key
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
View Replies!
View Related
Divide A Sum By Number If Items In List
I basically have lots of lists of 12 cells. how ever some of those lists contain errors (e.g instead of having 12 numbers it will only have 10) so instead of doing =sum(a1:a12)/12 i want to divide by the amount of numbers in that area in this case 10 so i can get an average.
View Replies!
View Related
Sort ListBox Or Combobox List Items
The code for sorting a multi-column listbox in Retain Selection After Sorting ListBox was really good, and I've used it a lot in an application I'm building. What I'm hoping to do is reload a listbox in such a way that the items appear in the same order they were previously in. I'll describe two scenarios: 1. User edits a record - user sorts listbox - user selects a record - user edits record - listbox reloads, unsorted 2. User adds a record - user sorts a listbox and sees a record is missing - user adds the missing record - listbox reloads, unsorted ideally the last step for each would be "listbox reloads, sorted" and the user would go on down the list. the tricky part i think is when rows are deleted or added. i'm starting on a solution, but if there's some existing code that will do this i'd appreciate if someone could point me to it (because, for example, the listbox sorting code i referred to above anticipated things i did not).
View Replies!
View Related
Creating A Clean List From Select Items.
I have 2 sheets, 1 sheet has a list of materials that require Certifications. The 2nd sheet is actually a report of which materials is needed for the specific job at hand. Column C will be linked to check boxes on a different sheet in the future (thats why i used True/false instead of Yes/no). What i would like to happen is when a cell in Column C is TRUE the information in column B be sent to the Certifications sheet in a list from C8 to C34 with no spaces.
View Replies!
View Related
Selecting Multiple Items From A List In One Cell.
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"
View Replies!
View Related
Individually List Grouped Items / Quantities
I have a list of items and quantities I need of each, such as cat 3 dog 2 mouse 1 horse 4 snake 2 leopard 4 I'm thinking that it would be a macro that would be way to go for this project. I want to run a macro that will make the list above the following cat 1 cat 1 cat 1 dog 1 dog 1 mouse 1 horse 1 horse 1 horse 1 horse 1 snake 1 snake 1 leopard 1 leopard 1 leopard 1 leopard 1 So what happened was that 6 lines of information was transformed into 16 lines of information. My purpose is that I will then will exploring various combination of these items, and thus I think that splitting them up like this will make them more manageable to work with. Since the original list values will change I will not always know how many lines to set aside for the individual breakdown.
View Replies!
View Related
Create A Drop Down List And Use Lookup To Get Corresponding Items
I 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 Replies!
View Related
Add New Items To Data Validation List
I have a user form named "final report" worksheet which contains 12 cells (B14:18,B19,C19,B38:39,E34:35 and C46) with dropdown list validation. These cells are noncontiguous. The corresponding lists are on the other worksheet--"MyLists". The list are in adjacent 12 columns on that sheet.They begin from cell A1 without any header. I have named them as dynamic ranges. This is-- not exactly connected to my query but still -- I coded the "MyLists" worksheet so that when I delete or add the names in the list, the empty cells shift up or down accordingly in a sorted manner in the list.Here is that Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Columns(Target.Column).Sort Key1:=Cells(1, Target.Column), _ Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub to code the "final report" worksheet so that instead of just selecting from the dropdown list, I can type a new entry in any of those 12 cells and the "MyLists" worksheet will get updated in the respective columns simultaneously.
View Replies!
View Related
ListBox Retaining Old List Values/Items
I have a listbox which is populated with filenames from a specific folder when the userform is acivated. The user can choose any filename in the list to open it or adversly to delete it. When delete is used (ListBox is set to MultiSelectExtended) the selected files are 'Killed'. The filename list is then refreshed and listbox repopulated and resized ready for the next action. The problem is the recalculated ListBox.Height works fine and the ListBox shrinks in height as expected, but a vertical scroll bar appears as the number of lines is still that was prior to the files being deleted. This happens despite code having run an instruction to make the ListBox.Height = New Number of entries * line height. This doesn't stop the application to perform as expected but is an annoying cosmetic thing.
View Replies!
View Related
Adding Items To Multi Column List Boxes
I am using a ComboBox to get a value from the user. The code below will then check my worksheet and if it finds the value will place it into my ListBox. Dim x As String myvalue = ComboBox1.Value A = 5 Do x = Sheet1.Cells(A, 4).Value If x = "" Then Exit Sub If x = myvalue Then Me.ListBox1.AddItem x A = A + 1 Loop Until Sheet1.Cells(A, 4).Value = "" The problem is how to alter this code to place the value from column 5 into the ListBox as well as the Value from column 4
View Replies!
View Related
Vba To Isolate Items In Spreadsheet And Compose A List
I have the following spreadsheet: A----------- B----------------- C Invoice------ Product Code------Classification 82001733----LX+150SVL0044----9010.90.9000 -------------SE+68763001-------8501.31.4000 -------------GJ+10005----------- -------------SE+59163-----------8504.40.9580 -------------EB+98575----------- -------------KF+MX09300--------9010.90.9000 I'd like to extract the rows with blank cells in Column C and product codes in Column B and create a list of product codes which don't have classifications on a separate list. This new list would need the ability to be revised as other codes will be added once a macro is run.
View Replies!
View Related
Lookup Details For Multiple Chosen List Items
I 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 Replies!
View Related
Creating A Macro To Count Unique Items In A List
I need to count the unique for a customer. The areas to be counted are separated by blank lines. At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements. Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?
View Replies!
View Related
Spreadsheet That Allows Users To Select Items From A Validated Data List
I am making a spreadsheet that allows users to select items from a validated data list. Each item in the list has a numeric value in an adjacent column. When the user is entering data they will select from a drop down of the data list in column "A" and I would like to have the numeric value that corresponds with their selection to then autofill in column "B". I have tried VLOOKUP and IF formulas but cannot seem to get anything to work.
View Replies!
View Related
Parsing A Row Of Multiple Items From A List Of Matching Unique
I have two worksheets: A and B. Worksheet A:Contains 2 columns: Issue# (Col. A) and Program (Col. B). Issue# contains a list of multiple issues. There are several instances in which the issue# is repeated. Any particular Issue# field may have several issues in it, delimited by a comma. Program is a program associated with the issue and this column also contains duplicates. Worksheet B:Contains 1 column: Issue# (Col. A) This is a unique list of issues#'s. All Im looking to do is parse all Issue#'s from Worksheet B and have some way of knowing if that issue# is anywhere in Worksheet A. Most importantly, I need the "indicator" to be displayed on Worksheet A. This way I can see what program(s) is/are associated with the matching issues. A couple other notes:All Issue#'s in Worksheet B are referenced at least once in the Worksheet A Issue# list There are several issues in Worksheet A which are not referenced in the issue list on worksheet B (of which I dont care about) I really hope that makes sense, but if not... Here's the best example I could come up with: ....
View Replies!
View Related
List Box Depending On Combobox And Add Or Delete Items To Range
i had a user form with a Combo box,list box,text box and a command button. I need the code that works upon selecting: 1)An Item from the combo box should display the list in the list box. eg: If Country is selected from the combobox then the list box should contain all the names of the countries from the country Column 2)An item should be added to the list in the worksheet when an item is entered in the textbox. Like wise, when an item is selected from the combobox from "Delete Items List" all the items relating to the item selected from the combobox should be displayed in the list box and a choice to delete the items relating to the combobox item should be provided.
View Replies!
View Related
|
TRACKER
