I want to be able to use list as filters that will show me results for prices I need. I have no idea how to figure this out. I attached a sample file so you can see what I'm trying to do.
View 4 RepliesI was just given a task at the office of creating a in depth excel spreadsheet. Here is what I need:
I have one drop down list that represents the state a person lives in.
I have another drop down list that represents the the type of property it is.
If you select CA in drop down 1 and Multi Family in drop down 2 you get the end result in (specific) cell.
Then if you select AZ in drop down 1 and SFR in drop down 2 you get different end result in named cell.
How can I get items within 2 different drop down list to call up the end result in a 3rd cell?
how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.
What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On
Is there a way to do this with the set up I have?
I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory
=IF(ISERROR(VLOOKUP(D7,IF('Main UI'!C7="Heating_Cooling",Appliances!$A$7:$B$14, IF('Main UI'!C7="Kitchen_Bathroom",Appliances!$A$16:$B$27,IF('Main UI'!C7="Monitors",Appliances!$A$52:$B$62,IF(C7="Computers",Appliances!$A$64:$B$68,........
And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm
I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list
I am looking for the best way ( or choice of ways) to display the correlation between 2 lists of results.
I have a column of failure percentage rates for engineers that I want to compare against a second column of their compliance rate with a process. There are two levels also. The first set shows the good performers and their compliance, the second showing the poor performers.
My goal is to see whether their compliance with the process has any bearing on their failure rates. The full lists contain data on approximately 400 engineers in each category
(if the the numbers below are not in a proper format, I tried to download the excel converter thingy but the link failed)
faliure rate
Compliance
3.95%
91.40%
[Code]....
I want to know how to link te two lists in my register so that when i select a particular route in one column the corresponding column automatically selects the transit time from the reference list.
View 2 Replies View RelatedWorksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
Suppose I'm a grocer and I use a spreadsheet to track my inventory. I sell fruits, vegetables, and dried spices. On one worksheet in my Excel file, I create the following lists:
FRUITS
VEGETABLES
Apple
Potato
Orange
Celery
[Code]...
Then on a second worksheet, I have my actual inventory, which is this:
ITEM
QNTY
PRICE
CLASSIFICATION
[Code]...
I need a formula for the "Classification" cells of the Inventory. For each "Classification" cell, I want the formula to do the following: Search for the corresponding "Item" in the "Fruits" list; if found, then put "FRUIT" in the Classification field.If not found, search for the corresponding "Item" in the "Vegetables" list; if found, then put "VEGETABLE" in the Classification field.If still not found, put "SPICES" in the Classification field.
So using my toy example:
The formula for the first row would search for "Banana" in the "Fruits" list. Because the desired text sting is found, the formula enters "FRUIT" into the Classification field.The formula for the next row would search for "Cucumber" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is found there, the formula enters "VEGETABLE" into the Classification field.The formula for the next row would search for "Dried Oregano" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is not found there, the formula enters "SPICES" into the Classification field.
If Excel can handle this level of automation, but I suspect there must be a way to do this.
On about 30 worksheets I have lists of names. These names are changed and added to now and then. I would like them to automatically populate a worksheet called "list". Preferably the names of every sheet with a blank cell at the bottom.
The whole work book is about 60 worksheets.
I have a workbook with two sheets
sheet1(Invoices) is the master and sheet2 (GR) contains information that i need to put onto sheet1.
I need a formula that will allow me to lookup sheet2 based on the criteria from 3 columns on sheet1... (PO no, Part No, Qty)
At the moment I am using 3 different vlookups to achieve what I need
If there are records on sheet2 which matches sheet1 then I would like to have the word MATCH if there is a match and CHECK if there is not?
We currently have a few hundred vendors we order from for a retail business. We receive price lists in excel format, but they all differ in how they are formatted, i.e. one may have a column header of "UPC" and the next may say "UPC ID" and they may be in different columns. We currently format a handfull of these price lists so they have the same headers so we can easily compare costs, margins, etc, but we don't have the resources to do many more than this.
View 6 Replies View RelatedI have a list of data that is currently separated into 5 different columns with a heading. Each item in the list is given a score 1, 2, 3, 4 or N, which is then placed into the appropriate column for that score.
What I want to do is to place the data in another sheet in the same workbook in a single column list separated by the labels from the columns in the first sheet, without any blank rows.
I am sure it would be easier if I could show you the spreadsheet and the result I want to achieve but I cannot see here how I can. I tried to copy and paste, but it only pasted as text.
I would like to know if there's a way to create a checkbox list in a cell that influences other cells:
For example, I would have column A as "Materials Accepted", and each cell would have a dropdown list with the options "Wood", "Drywall", and "Metal", in checkbox format. Then to the right of that, I would have each of the Materials as actual columns so that column B is "Wood", column C is "Drywall", and column D is "Metal".
The way I would want it to work from there is that whatever options I select from the dropdown, it would then put a TRUE flag in that column. So if I select "Wood" and "Metal" on the dropdown checkbox list, columns B and D would both read TRUE on that particular row.
Just wondering if something like this is possible, as I wanted to apply this concept to a much larger scale (About 29 columns for 29 Materials, scrolling left and right is really time consuming). So far I've only seen guides on how to create a SINGLE checkbox on a row, and none with multiple checkboxes that would function in this manner.
I have a single list of data that I am trying to convert into multiple lists. Here is a sample of what I have:
COL-A COL-B
1 Animals
2 Dogs
3 Bulldogs
4 Missy
4 Rex
4 Fred
3 Terriers...............
Hard to describe, but the 1 should have the 2's under it. Each 2 should have the 3's. Each 3 should have the 4's, etc... I am not sure how to put tables into this so I will try to draw a few.
COL-A
Animals
Dogs
Cats
COL-B
Dogs
Bulldogs
Terriers
Dalmations..................
The order of the columns do not matter. If anyone has any ideas how I could do this that would be great. The data is on seperate sheets. I would love to be able to do it with INDEX and MATCH
criteria, but a VBA solution would be acceptable.
Looking to create a drop down list on the top of a column that will find all entries with that numerical value. For example, lets say I have four entries with the value 1234 spread out through the columns. When I click the drop down menu, it should only have 1234 once and when I click it, it shows me all the rows with that entry.
View 2 Replies View RelatedI am trying to get the data that is produced on 15 sheets copied into column A as a continuous list (concatenated?) in a sheet called "Keywords". I have tabs labeled Output-1 through to Output-15 comprising of column A in each ( No headers ). Each tab/column will have similar data but of variable size, some may even have no data at all. (the data is updated/pulled into each column using a different formula on each row)
View 9 Replies View RelatedI've looked at the data validation tips and understand how to make a second dropdown dependent on the 1st, but how about a 3rd dependent on the prior 2 selections and so on.
I would like to have dropdowns based on a dataset of 4 columns (Location, Department, WorkGroup, Employee) that frequently gets updated. I would like to have the user select a location then a department then workgroup and employee. I would then fill in a worksheet with other pertinent information. I can get the 1st two dropdowns to work, but haven't found the key to being able to continue to drill down based on the 1st two options.
I have values in Worksheet 1, spread over A1:D25 and A200: D250.
In worksheet2 I have values again from A1:D25 and A200:D250.
Is is possible to only get the unique values of those 4 ranges with the advanced filter? They all need to be shown in eg worksheet 3 starting in A1, (so kinda merged in a sense)?
Is that a thing more for a UDF, or is there a excel function/option that does exactly that?
I have been looking for ages for that kind of function/option, since I thought it must be possible. But this sure does not look to be a standard functionality, or is it?
Is there a (free) add-in that might do this kind of thing?
I found this code on some office help page:
Sub SortAllRangeData()
' Place column header for temporary sort area.
Range("IV1").Value = "Numbers"
It kinda does what I needed, but it lists the actual data in the same spot it used to be. I want to be able to list the sorting in a different column on a different sheet and in 1 column only. Is this difficult to modify so it becomes a UDF or is this something totally different?
I am working on a spreadsheet for my Building company. I'm building a tool to price for Fascia, Soffits and Cladding.
I'm using Macbook Pro Microsoft Office for Mac 2011
Please see attached file
The first sheet holds all the lists of products, Category Headings list is in column A and then all the relevant products and prices are then from B to BI. I have defined each category with a name by selecting the cells and entering a name in the name box.
The second sheet is a Calculator in which I would like a to have a drop down list in the Category Column (Which I have worked out how to do myself - good old google) and then a drop down list in the second column which lets the user select from a list of results based on the selection from the previous column.
Example:
Category(B3) - 18mm Fascia/Replacement Board (Square White) - Drop down menu taken from sheet 1 A3:A33
Description (C3) - Drop down list containing all the options from D2:D15 Named "FasciaReplacementBoard18mmWhite"
So basically, whatever the user selects in Column B (from the category list) a drop down list would be available in Column C
The Value column would then show a value based on the options selected.
I am trying to setup a file that has three tabs - LIST, COLOR AND PLANT.
I want to be able to select a color or colors (red, blue, yellow, green) and a plant or plants (tree, shrub, flower, grass) using radio buttons, maybe?
If i chose red and blue for a color and chose tree, shrub and grass for plants, i would like to populate the LIST tab with these items.
I'd also like to give an associated value to each color and plant. for example, for the colors, i could choose values between 1, 2 and 3. if i chose red 1, it would add "red 1" to the LIST. if i wanted to later change it to 3, i could do that on the COLOR tab and the LIST tab would update automatically.
I am trying to automatically construct a unique consolidated lists from several other lists. I want this to be formulated in such a way that the mother list is uptodate and reflect all changes made to the subsidiary lists. I have attached an example of what I am trying to achieve.
View 8 Replies View RelatedI expect to deploy over 200 iterations of a workbook (templates if you will) containing a mix of up to one hundred different attributes each with its own list of valid values. My problem is the attributes don't always default to the same column in every template workbook. Therefore, to make things easier for users I would like to create attribute valid value dropdown lists on the fly using the "Valid Values' table as the source data (see the attached). Since the attribute names will be the same from one template workbook to the next, I wanted to come up with a macro that would reference a source table (a separate workbook) containing all valid values for all attributes which users would be required to download to their local machine.
See the attached example workbooks and an example of a "Valid Values" list. Here's what needs to happen: When a user opens template workbook #1 I need drop down lists containing the valid values for the attributes contained in cell B3 (User Code),C3 (Department) and D3 (Category). Likewise, when another user opens template workbook #2, I need drop down lists containing the valid values in cell B2 (Department), C3 (Group), D3 (Category), E3 (Level), F3 (Material) and G3 (User Code). Notice that "Department", "Category" and "User Code" are data elements common between both workbooks but resident in different columns.
ive had to retype this so please bear with me. ive utilised the resources of this site before but ive come to the time where i need to ask a question of my own.
now i will refer to my attached document as what im trying to achieve.
my project is to create membership cards for our social club at work, out of a list of people we have on our manifest.
If you refer to column A, this is where i will dump the manifest of members as time progresses (keeping in mind that more names will be added to this list as it goes).
What i am hoping to achieve, is to start at Cell A2 (highlighted in yellow). For the example the membership name is "a". i want to hit the macro button and the following will occur.
1. data "a" from Cell A2 will be copied to B2 (highlighted in green) and G6 (highlighted in blue). This moves the name from being on the unregistered list over to a membership card and to the registered list, which allows me to cross check that im not doubling up on names with new applicants and current members.
2. once this occurs, i was cell A2 to delete and cell A3 to be moved up, so now, cell A3's data "b" (highlighted in orange) will be moved to A2.
3. a similar process as point 1 will now occur, where A2 will be moved to the highest empty cell in Column B, which would be B3 now. It will also copy to G18 into the 2nd membership card.
4. this process occurs until column A is empty in A2.
5. i wish to really only have an A4 page worth of membership cards before i print, so i wish to be able to "reset" the membership card boxes, so that instead of the membership cards continuing all the way down the worksheet, it will do say 12, then print, then start back in the 1st box. Ideally this is a loop so that it occurs until there are no more names in Column A.
This is a huge feet for me as an idea because ive never gone beyond using basic recorded macros, however im the type who just HAS to learn how to do stuff, so i will lap up any information or assistance i can recieve from anyone here.
I have two lists. A1:A7000 has 7000 items and B1:B10000 has 10,000 items all in random order. List B includes all the items on list A, but with an additional 3000 items. How can I extract the 3000 items that are exclusively on list B? I would like to put these on a new list C1:C3000.
View 6 Replies View RelatedI transposed them into a separate sheet and used that for my lists. I have to share the file with macs.
My pc is on Vista running Excel 2003. The macs are OSX Panther (not Leopard) and running Excel 2003.
Solution (?): if I make the combobox lists programmatically, it will work on the macs?
The UserForm1 with 3 comboboxes:
The comboboxes are not dependent on each other.
For each combobox, I need to have 2 columns.
For each column, I need to transpose 2 x ranges (they are NOT contiguous)
My attempts have been embarassing and futile.
What it should be: ....
My problem in the given excel file
1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list
2. Sheet2; how to remove "B000" or "B00" from the list
3. Sheet3; how to highlight difference or find out the difference between to lists
Update_List.xlsx
I have a worksheet and in one of my columns I can have multiple values, this could either be a valid numeric value, .e.g "Trace" or a value such as >1.5 or it could be blank.
Depending on the value I then want to return a different result in another cell.
In the example I have tried my data is in cell E8
I have tried the following formula and failed miserably, I'm not sure whether IF or IF OR can cope with this.
=IF(E8>=0,"1",IF(E8<0,"Invalid",IF(E8="Trace","2",IF(ISBLANK(E8),"4",IF(LEFT(E8,1=">"),"5")))))
If E8 contains a numeric value equal or greater than 0 then I want it to return a value of 1, this works
If E8 contains a value that is negative then I want my formula to then display "Invalid"
If E8 contains the word "Trace" to return a value of 2
If E8 is blank then to return a value of 4
If the first character in E8 is ">" then return a value of 5
I need to look up a value from a worksheet using multiple criteria, and return MULTIPLE results.
I have a table with comments written by different people about specific items. I would like to be able to type in that item name and return all those comments. BUT - it's not JUST the item name, but also on a specific date or some other criterion.
Currently I have managed to do this successfully with a single criterion, as in this array formula:
=IF(ISERR(INDEX(F$1:F$1001,SMALL(IF(A$1:A$1001="ABC",ROW(F$1:F$1001)),ROW(1:1)),1)),"-",INDEX(F$1:F$1001,SMALL(IF(A$1:A$1001="ABC",ROW(F$1:F$1001)),ROW(1:1)),1))
I then copy that formula to a few more rows and I indeed get a list of different names (F1:F1001) which match my criterion "ABC". But - I have at least one more, or even two more criteria. I've tried a whole lot of different things and I just get errors.
Has anyone tried something like this? If you have - how should I modify the above formula to allow for additional criteria? For example, not only A1:A1001 equal to "ABC," but also B1:B1001 equal to "XYZ", and so on.
I want to compare 2 lists in separate sheets based on multiple criteria and delete the duplicates
Sheet 1 - new list in column A:E
Sheet 2 - old list in columns B:F
So here is what I need: the macro should generate single IDs made of Sheet 1 Ai,Bi,Ci,Di,Ei cells for each row i to the end of the list + generate single IDs made of Sheet 2 Bi,Ci,Di,Fi
If . Evaluate (Sumproduct (IDs made of Ai,Bi,Ci,Di,Ei from sheet1) & Sumproduct IDs made of Bi,Ci,Di,Ei,Fi from sheet2) >1 then delete the entire row in Sheet 2.
This will leave me with only updated items (rows) in Sheet 2
What I am looking to do is this... (using Excel 2007)
I need to have more dependent lists based on the selection in Column A. (My column B is already set up and works perfectly using INDIRECT)
For example:
Column F would be a dependent list of colors based on the sales rep chosen in Column A
Column I would be a dependent list of managers based on the sales rep chosen in Column A
Column M would be a dependent list of part numbers based on the rep chosen in column A
without using VBA? (If I HAD to - okay, the other person maintaining the sheet would be lost....) I haven't been able to find anything close to what I want to do online - maybe I am just not using the correct terms.
I am trying to create challenge/response cards. Each card will have a 5-digit number in the upper-left and lower-right corner along with a series of rows containing a line number and 3 pairs of challenge/response words.
Rather than have have copies of these cards, I want the central office to have a macro-enabled workbook wherein they can enter the two numbers from the card and have the challenge/response matrix regenerate on a worksheet.
So, essentially, I want the challenges random number generator seeded from one of the 5-digit numbers and the responses random number generator seeded from the other 5-digit number.
I am trying to use
Code:
Randomize(Seed)
where Seed is one of the 5-digit numbers.
The problem appears to be that, no matter what I use for Seed, the RND(-1) function is not resulting in different numbers.
I am using Excel 2007 and Excel 2010.