How To Generate Dropdown List With Data Which Uses Column To Identify Items
Apr 18, 2014
I have in column A and B. A includes two (or may be more) kind of items, i.e. drink and snack. Column B has for example water next to drink and chips next to snack. The list is huge.
How can I generate a drop down list with data which uses the A column to identify the items which it should include. I want to make two separate drop down lists for drink and snack and each should include only those options which belong to those categories. I.e. drink list should be like water, cola, pepsi, etc. The problem is that the list is huge and it's not possible to sort it. Some kind of if statement structure with named range? The drop down should be dynamic in a sense that if I add line to data, it should be picked to the right drop down menu.
View 9 Replies
ADVERTISEMENT
Mar 11, 2009
I have a report with ~44,000 line items. From this report, I need assistance generating a report that lists all unique line items. I am having problems trying to identify which line items are duplicates as I need to compare 4 different cells in each line to other lines to determine if it is a duplicate item (compare data from columns A, B, E, F)
For example, raw data may be as follows:
Column A………Column B…….Column E…………….....Column F
10/13/2008…….11111111…….71879W561000……….888.88
10/13/2008…….22222222…….239103302000………..222.22
10/13/2008…….11111111…….71879W561000……….888.88
10/13/2008…….33333333…….353496508000………..333.33
10/13/2008…….44444444…….239103302000………..444.44
10/13/2008…….11111111…….71879W561000……….888.88
10/13/2008 ……11111111…….353496508000………..888.88............................
Note from above, there are two occurrences of a duplicate item. As a result, the output table would only list this item once.
I have attached a sample of the spreadsheet with dummy data on one tab and the desired output on another tab. Note that all the data is broken up into blocks of data that is separated by data that is only in the first column of the row.
View 5 Replies
View Related
Mar 17, 2007
I Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies
View Related
Feb 13, 2014
im looking to have a drop down list on sheet2 that displays unique items in column A on sheet 1.
then when an item is selected from the drop down list, i want to be able to display all unique values that correspond to that option. i have attached an example worksheet
data on sheet1 is ever changing
View 4 Replies
View Related
Nov 2, 2006
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 9 Replies
View Related
Feb 12, 2014
I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.
View 1 Replies
View Related
Jul 17, 2009
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
View 9 Replies
View Related
Oct 28, 2011
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies
View Related
Oct 5, 2009
Hi all. I need to create a dropdown list that can handle more than the standard eight rows that data validation allows. My list requires 75 different items in the dropdown. Is there any way that I can do this?
View 14 Replies
View Related
Sep 9, 2009
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 2 Replies
View Related
Mar 18, 2014
I have about 145+- employees and I made a simple spreadsheet that allows me make a timesheet for every employee. However, I do not want to sit here and select each employee's name and press print 145+ times. Is there another way to do this?
All I would like to do is press print or run a macro and 145 sheets pops out. I have to do this weekly and it would take me an hour to individually do this.
View 1 Replies
View Related
Mar 25, 2008
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 9 Replies
View Related
Aug 13, 2014
I have a spreadsheet that lists a set of actions and the days they need to be completed.
The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.
I have a dashboard on a separate worksheet, showing if the item is complete and the due date.
Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.
I have been going through my books and trying to solve this, but I run into a different issues.
Even if for now If I can get the main worksheet to display the color conditional formatting that would work
View 2 Replies
View Related
Mar 27, 2009
I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.
I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.
View 5 Replies
View Related
Jan 11, 2013
I'm trying to make a spreadsheet that can be used to easily build a collective list of steps, for a user to read and follow line-by-line.
I want a source sheet of "steps" that I can change over time, and the resulting tabs that reference the source sheet get updated/populated automatically.
I've pieced together some VBA code from other sources, which kind of does what I want it to:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 And Target.Column = 1 Then
Dim SourceSheet As Worksheet
Set SourceSheet = ActiveWorkbook.Sheets("Steps")
Dim TargetSheet As Worksheet
Set TargetSheet = Target.Worksheet
Dim c As Integer
Dim Source As Range
[Code]...
But there are some problems:
1) The data auto-populates into a row. It would read better if each step was in the same column, meaning rows would need to be automatically added upon selecting something from the drop-down list. The number of rows that need to be added vary based on the number of steps in the source sheet, for the selection made from the drop-down list.
2) If you make a change to the source sheet, my goal is to have the other sheets referencing the source sheet's lists of steps update automatically, so you only need to update the steps in one spot and everything you've built from them gets updated instantly. Currently, you must select a different choice from the drop-down list, and then change it back, before it populates the "new" steps from the source sheet.
This is my first time using VBA.
What I have so far is attached: testAutoPopulate.xlsm
View 2 Replies
View Related
Oct 12, 2011
I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.
Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.
View 2 Replies
View Related
Mar 27, 2014
I 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.
View 6 Replies
View Related
Feb 21, 2014
I am trying to create a document that runs a scenario based on the two drop down choices selected that determines which worksheet in the workbook autopopulates over to sheet one based on the selection. I tried doing an if statement but did not have any luck.
View 1 Replies
View Related
Jul 10, 2013
I'm trying to set up a diet/training spreadsheet so that when a client enters foods and exercises they don't want/can't do, those foods and exercises are made unavailable in dropdown menus (on my programming sheet) so that I can't inadvertently put them into their plans. I have a webform they fill out, which automatically puts the data into a spreadsheet and I know how to pull data from there into the clients' workbooks...
View 2 Replies
View Related
Aug 17, 2008
i had a basic spreadsheet for my incomings and i got wiped with my hd exploding!! i know how much the total was i just need a way of creating a list of random amounts within a specified range over a year to give to my accountant.
View 13 Replies
View Related
Jul 8, 2013
How can I create a Makro that looks for values (e.g. values > 90) and that then generates a list with: all the values greater than 90, the row names and column names of the values?
I am pretty sure I will need a Makro for this, but how to create it. I am using excel 2010.
I have attached a file to give you an example. The red table is the table with the values. The blue table is what I want to be generated automatically. In the example I did it manually.
Workbook1.xlsx‎
View 2 Replies
View Related
Oct 9, 2008
I have attached my sample XL sheet. Column A & B will be imported from computer system, only one p/n and quanity. Column C & D will be scanned in from multiple trucks and warehouse, p/n might show up multiple times with different quanities. I need to compare A&B to C&D and come up with a recount sheet that shows the difference as in E&F. Ideally this could be put on another sheet that could be printed out for recounting. Is it possible to do all these different functions automatically. There will be somewhere between 3000-5000 different p/n's.
View 12 Replies
View Related
Feb 25, 2014
I have Sheet1 "MASTER" and Sheet2 "Area1" and Sheet3 "Area2" etc...
My MASTER sheet has a list of employee names and the areas they work in. I have employees working in different areas, and I want to pull a list of employee names from the MASTER working in Area1 (sorted on the MASTER sheet) to column A on Sheet2, then pull a list of employee names working in Area2 to column A on Sheet3, etc...
I want it to do this in such a way that if I add an employee to Area1 on the MASTER data, it will populate that employee in the Area1 Sheet.
So basically, I'm looking at one column on the MASTER sheet to see if the area matches. Then looking at another column on the MASTER sheet to get the name. Then taking that name and transposing it to a new sheet corresponding to the area they work in.
I've attached a sample sheet. I want Column A in the Area1 sheet to reflect all names that show up on the Area1 LIST on the MASTER sheet, and nothing else. I'm using VLOOKUP to pull the rest of the data from the MASTER table.
There is a new sample workbook up now. Couldn't update it sooner due to site outage. I've removed irrelevant data to improve readability and focus on what I'm trying to achieve. Again, the main issue is scraping column E from the Master, and populating a list of all employees who match certain values in Column E on the Master in Column A of the other tabs.
View 2 Replies
View Related
Nov 1, 2007
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 10 Replies
View Related
Nov 20, 2009
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
View 4 Replies
View Related
Jun 11, 2013
I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.
My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad
[URL]....
[URL]....
I am aware there are people with more pressing problems than computer games and as such
View 7 Replies
View Related
Jan 25, 2010
In Column A I have a list of Products and in Column B is the list of corresponding prices. I'd like to create a drop down list with the items in Column A that would automatically fill-in the pricing from Column B next to it. I've tried using an IF statement, but my product list is too long.
View 5 Replies
View Related
Mar 27, 2009
I have two spread sheets from one of our suppliers, one was issued in 2008 and the other 2009. The newly issued sheet has about 400 extra items. How can I identify these new items of stock easily. I have attached both sheets for you to look at.
View 5 Replies
View Related
Jul 16, 2013
I have data in range A2:E5000. Column D contains cost centers entered by users.
I also have a list of valid cost centers in range K1:K10.
What would be the vba code to check and return the count of the items entered in column D which are not valid, based on the list in range K1:K10?
View 1 Replies
View Related
Oct 9, 2003
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 9 Replies
View Related
