Drop list. First of all I have sheet with all my data and i have define the name of each table.
I have about 15 categories table and a table with min 2 and max 100 items in each category.
I have made in a different sheet a drop list for my categories. Near this list i made another drop list with the items by using the formula =indirect() to arrange my items by categories.
The problem that i have is:
- Some categories they have many items and is difficult to find some items. i need to put a formula to put some letters and find what i need easily (easy searchable).
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.
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.
My main objective is speed and automatization in the competition organization.
Im almost finishing it but now im stuck in this situation:
I have a table with 13 cells
A: athlete name B: athlete weight C: random number (raffle) and then from 1 to 10 is the final Groups.
E.g
Name Weight No 1 2 3 4 5 6 7
[code].....
Now i need to create a macro to display the itens in the groups when the weighing is finished...
E.g
Name Weight No 1 2 3 4 5 6 7 8 9
[code].....
The conditions are:
The numbers should be displayed in the athletes groups
The difference between all the athletes weights shouldn't be > 3 kilos If theres is more than 5 athletes per group the heaviest should go to next athletes group and pop up one message informing that maximum athletes per group was reached...
Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total 1234Bed150 5678Bed100 1547mattress52 58658mattress188 12356bed12 8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
I have a file and it has a table on it with 2 categories.
what i want to do is select a color in the first drop down list and than in the second only select the items that match the color i have previously selected.
Is there anyway to do this with named ranges because i would like to index everything in a table.
I am building a tracking list for several items and have categorized them
Software Hardware Networking
I will have several of these and the list keeps getting bigger. I have a table above the list that summarizes these catergories and the count of them. How can I count each categorie and so that it updates each time I add one.
The formula would have to sumproduct or determine what categories are there and how many (unique list) and update when I add one.
Currently its a manual process of creating a unique list and doing a countif.
I have a long list of accounts (for the purposes of of bookkeeping - keeping a cash disbursement journal) and I use data validation that uses this list when I enter an account's name in the journal. Because the list is very long, I often times have to scroll up and down the list in the drop down menu that comes up at a cell I need to populate and when looking for the proper name of the account I needed to be there. I am looking to add a capability to my data validation list use when I would be able to enter several symbols, part of the name of the account I am looking for, and the list of accounts displayed for me to choose from would get shortened based on the symbols I enter. the symbols I enter do not necessarily have to be the first symbols of the account name I am looking for. (as sometimes, I do not remember how exactly the account is called - and so I just guess part of the name ... )
I'm working on making a monthly expense report with 2 sheets; the first sheet would include each individual expense and the expense would placed in a certain category. The second worksheet would be a summary of the total expenses for each category.
I would like column A to be a drop down menu:
Column A: Expense type (Stationery, Kitchen, Maintenance, etc..)
Column B: Amount
This would continue for as many rows as i need
Then on a different worksheet it would add each item based on its category and give a total for each category.
I have a drop down box in column N, however I would like the options in that drop down box to change depending on what appears in column M.
If column M Says "Lapse" then I want one drop down box to appear in column N, If coumn M says "NTU" then I would like column N to show different drop down options.
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 would like to populate the captions of checkboxes in a userform with the items listed in a drop down menu located in cell A1. Lets just say I don't know exactly how many items in the dropdown there will be, but I do have an estimate number. Maybe between 20 and 25.
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.
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.
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.
It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.
What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.
I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.
I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.
It's a quarterly schedule for utility meter readers, divided up into days across the top, and routes/areas down the side. There's a space with each route for the employee ID to go, depending on who's doing it.
Down the bottom of the sheet, there's a few empty lines for all the available employees who haven't been allocated to a route.
On the second sheet labelled EMP LIST, is all the employee IDs. It's also got the areas they work- it's for something I want to implement later.
I can put all the employees into a single column list, then use basic DV to give a dropdown menu by each route, to assign an employee to it. This is pretty straight forward. I've also been playing with this in combination with a countif, so that employees already assigned do not appear in the list.
This is about as far as I've managed to get with it.
I need to get the employees who aren't assigned to anything to appear in a list below the routes. I'm fairly sure this is just another countif, but I'm not 100% on the exact formulas.
I also need the list in the dropdown menu to reset for each day of the quarter, i.e. if I assign an employee to a route on the 30th day of the quarter, their ID will disappear from the list for any other route for that day, but will still be there for the other days of the quarter.
So far the only way I've managed to do this is by duplicating the employee list for every day of the quarter, which is going to get very cluttered, and also makes staffing changes difficult. I'm hoping there's an easier way to do this using one data set.
I've added an example of the sheet. It's one week and it's only got about 1/3 of the routes we would actually have on any given day.
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.
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example: FAILED - whole row changes red SUCCESSFUL - row has no fill Tested - row changes to orange
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15 A drop-down list displays the values 1 through 15. Cell A1 changes to 20 Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
why my drop down list defaults to the middle of the list when first clicking on the arrow that opens the list? I created an alphabetical list of all US airports but when I open the list, it starts at "D" instead of "A" which is the top of the list.
I need to create a 'sub' list from a drop down list.
I have 8 categories in a drop down list in column A, but i need column B to also have a drop down list of sub categories depending on what category is selected in column A.
And once column B is populated, i need column C to auto populate with a value. All data is stored on the spreadsheet.
i have a spreadsheet with several fields. i want to derive information from this data. i want to be able to control the output through the use of list/drop down boxes.
so the user will choose several criteria AND based on this criteria, the ouutput will reflect user requirements.
I have a workbook with around 35+ sheets on it and I was wondering if its possible if I could put these sheets in a drop down list in say cell A1 so it would be easier to navigate the sheets. Is this possible with out using VBA code if so how would I go about doing it?
I have a drop down list of shipping Companies. If the name of the company is not in the drop down list how do I allow the user to type in the name then have it save in the drop down list source?
