I did all the search for multiple drop box or list box that can connect to other list boxes but no luck.
It's the same as when you surf with Internet Explorer, you can click "Views" in menu bar, list box appear and then choose "Encoding", another drop box appear, then when you choose "More", then another box appear.
Is it possible to have this sort of hierarchy list box in excel where drop box connect to another drop box?
I need to create a table of non numerical data that I can refer to in a formula, which will compare two pieces of data (both also inside this table), and based upon which is "higher up in the hierarchy" - the cell will display a certain result.
Basically it will be an IF function using non numerical data that has a numerical value / "rank" attached.
Specifically - I need to allocate values to this list in cells A1:A5...
EUR
GBP
AUD
NZD
USD
....where EUR is highest ranked and USD lowest.
Then in column B and C will be listed each of these values but in various combinations. In column D I will build simple IF functions to show the highest ranked in each combination. eg B1=EUR C1=GBP D1=IF(B1>C1,B1,C1)
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 create a simple user interface type thing so that someone is able to select from drop down lists someones information, such as whether they are male or female, aged between 19-35 or 35-67, whether they are studying in a business area, legal or construction etc (there are 6 variables in total), This will then give the probability of success of the person passing this course based on probabilities which I have already worked out. I have worked out how to do the first stage of creating a drop down list showing alternative choices with Sex, Age etc in the data validation options, however:
There are 517 possible combinations, as in Male aged 19 to 35 studying Business (with other variables) or Male aged 19 to 35 studying Law (+ other variables) etc etc etc each with their own probability of success. Due to the long nature of writing out Male1935BusinessNorthWestWhiteBritishCollegeBrown I have rewritten it so it appears in the excel file as M1935BNWWBCB, which obviously wouldn't make any sense to someone if they had to select M 1935 B NW WB CB from drop down lists.
Along side the M1935BNWWBCB there is the probability of success specific to that type of person. So for example I could would have:
M 60%
M1935 64%
M1935B 35%....
The below formula was provided by PGC, and works great:
=INDEX($E$15:$E$18,MAX(IF($A$2:$A$8=A15,MATCH($E$2:$E$8,$E$15:$E$18,0))))
In post:
Complex Array(?) Search
Now, I would like to add the ability to perform this same action/concept, but using two different criteria.
So I want to keep the hierarchical listing of importance, but lookup/match within using more than one criteria.
Example:
I have an order of superseding to apply to results of a search for Fruit.
Great
Good
Fair
Poor
(so Good supersedes, Great; Fair supersedes, Good; etc.)
Column A____Column B____Column E
Apple_______Red_________Fair
Apple_______Red_________Poor
Apple_______Green_______Great
Pear________Red_________Great
Pear________Yellow_______Great
Pear________Red_________Good
Pear________Red_________Fair
I want my verification to return all the worst Red fruit:
For Apple Red: Poor
For Pear Red: Fair
How do I create drop down menus in a spreadsheet?
View 4 Replies View RelatedI have some code to create a personal menu drop down to execute macros, which is a follows:
View 2 Replies View RelatedI am trying to create drop down boxes in a spreadsheet so I can create job sheets from them, I have a small business building cubby houses and I want to create a job sheet from a spread sheet that will have customer name - job number and so on thats the easy bit but I need to have a few drop down boxes so I can just click on what type of cubby they want from a list and what colour roof and what side door will be on and so on instead of having to type all this in for every job.
View 6 Replies View RelatedI want to put a drop down list in Cell D3 with the list of names from column B. There are 14 different names, but when i try and create the list it gives everyname from Column B.
Also once the drop down list works i would like the table to be populated with the managers supplier, so if Dan was chosen in D5, Suppliers 1 to 9 would appear below D3.
I have a form where I have to have the following info on the top left corner of the form for our clients:
TO:Name of client
Address, city, zip...
Phone number
Fax Number
I need to find a way to create a drop down list where I have my contacts information so that if I pick a name, it will show up on my form but also have the address, phone....in the format as above so that I don't have to keep inputting the information manually as there are over 20 names.
I'm writing a purchase order entry form for my company. I've used the above formulas to create drop down lists with addresses for each of our stores and vendors, but I need form to automatically select the correct "ship to:" location. Some stores will always have merchandise shipped to the store regardless of the vendor. Other stores will always have the product shipped to a warehouse location, but in a few stores the product will be shipped either to the store or a warehouse depending on the vendor.
I've been able to work out the formulas for the stores that have shipments always to one location by creating a second address table and linking the selected store to it.
Here is basically what I need:
Store A + Vendor A = Store A
Store A + Vendor B = Store A
Store B + Vendor A = Warehouse
Store B + Vendor B = Store B
Store C + Vendor A = Warehouse
Store C + Vendor B = Warehouse
I have created a list in a Microsoft Excel Workbook (names and corresponding numbers) and named the list.
Then, I created a drop-down menu through Data/Validate/List that included the names.
I can look the names up in the drop-down box, but I would like to be able to type a few letters and Excel auto complete the rest of the name. For instance I would type Ja...and James Doe would come up with the mes Doe in black (highlighted).
Is there a way to do this in Excel?
this is my first attemp at doing a "userform". I am looking for some help in creating a user form that enables users to choose items from drop down boxes, which shows next level drop down list items, then down to final drop down list with information based on first two choices. I've attached the sample file for reference.
View 3 Replies View RelatedI have created a hierarchy chart using smartart. Is there a way to hyperlink the texts in each box to other sheets in the workbook?
[URL]
My question is about creating a hierarchy of Data Types.
I need to code 3 Data Types:
1.) MealPlan
2.) Meal
3.) MealItem
MealPlan represents a daily meal plan. It should have a certain number of meals. An example of MealPlan would be "Healthy" and have a total of 3 meals. Meal represents a single meal. It should have a certain number of meal items. An example of Meal would be "Meal 1" and have a total of 3 meal items. MealItem represents a single meal item. It should have a certain number of protein calories, fat calories, carb calories. An example of MealItem would be "Broccoli" and have protein calories of .2, carb calories of .8, fat calories of .1.
By using Data Types, I hope to create meal plans, which are made up of meals, which are made of meal items. The following code creates a Data Type hierarchy for two Data Types. I've examined it closely but i'm still having a hard time recreating it for my needs as stated above.
I have case where i need to roll up data based on the hierarchy. I have hierarchy of 4 level as follow:
Level1 Level2 Level3 Level4 Country Region1 Dept1
Unit 1 Country Region1 Dept1
Unit 2 Country Region1 Dept1
Unit 3 Country Region1 Dept1
Unit 4 Country Region1 Dept1
[Code] ........
The data in all sheets is identical...so I created a macro to create template based on the rage. The problem is that the template is good for level 4 but not good for other level.
How do I use VBA to rearrange the data from the hierarchy format of Table 1 into the flat format of Table 2. See attached file >Data_Belinda_June-5-07.xls
Table 1: Each record has information arranged in a hierarchy format.
Level 1 information is indented by one space on one line; Level 2 information is indented by 2 spaces on the next line, etc.
Table 2: Data from Table 1 have been rearranged into 7 columns. Another column has been added to create a field for the Reference ID.
Reference ID: Two types: i) Created by prefixing with the letters BI , adding the first four letters from column 4 and the first four letters from column 5; ii) As in (i) with the addition of the entire word from column 6.
The report I have may have more than one hundred records and there may be more than ten people within each section.
I have been trying to think of a way to get a hierarchy chart automatically with given data through macros
In the attached excel sheet
Org. Charts (hierarchy) question.xlsm
Would it be possible to get the chart to do by itself if say I were to add items or remove items? Is there any way to do it for data automatically?
I extract the data from primavera as attached and I want to make the chart in excel automatically.
The accounts listed on this report change on a monthly basis. I am attempting to create a macro or write a formula that can but used each month to fill in this list automatically. On Sheet 1 is a short example of what the report looks like when exported into excel. The numbers on the left are the numerical hierarchy. The long set of numbers are the account numbers and the others are the categories that each are located under.
Sheet 2 is what I need the end result to look like. One of the problems I am running into is how to handle the fact that the hierarchy re-uses numbers. The hierarchy always runs vertical, therefore "5" always falls under the "4" that is directly above but when there is another "4" it should be ignored and continue on up the chart until it finds the first "3" and place that in the cell to the right then find the first "2" and so on.
I have an excel file (see sample attached) with a hierarchy with parents and children spread across different columns and rows. However, I am trying to condense it into one column with parents and one column with children. The only way I've been able to do this is by copying and pasting parents onto children rows, which is both a manual and time-consuming process (when you have a lot of rows). I feel like there must be a way to use VBA or a formula or something to get it to work. I have attached a sample, so you can see what I am trying to do (current data and desired data).
View 6 Replies View RelatedI have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
I am supposed assign macro to track all employees hierarchy in an organisation.
In Sheet1 I have Employee ID's in D Column and Supervisor ID's in N Column.
And In Sheet2 I assigned macro...
What exactly I need is when I give number and click the button I need all the employee id's in his hierarchy
Concept:
The number which you give should search in N Column and Display D Column Values...and those D column values should search in N column and display D column values....so on.....
So far, it is showing 1 level hierarchy...but I need till end...
i am use dir to put into an array all the folders in a folder then enter the first folder and repeat. the problem i am having is when it encounters a file it also puts it into the array and then errors once it trys to enter that "folder" (which is a file)
right now the code doesnt do anything but the plan is then to call another subroutine that lists all the files im looking for in a folder (that sub does work)
what am i doing wrong? all the things i can find on google show it the way i am doing it.
Sub GetDirList(topfolder As String)
Dim FolderArray() As Variant
Dim FolderCount As Integer
Dim FolderName As String
FolderCount = 0
FolderName = Dir(topfolder, vbDirectory)
' Loop until no more folders are found
Do While FolderName <> ""
If Not FolderName = "." Then
If Not FolderName = ".." Then
FolderCount = FolderCount + 1
Redim Preserve FolderArray(1 To FolderCount)
FolderArray(FolderCount) = FolderName
End If
End If
I'm working with 2 drop down lists created through Validation.
I'm trying to make this scenario work.
If A is entered in B1, then the drop down list in C1 would appear in D1.
If B is entered in B1, then the drop down list in C2 would appear in D1.
List C1 has 12 choices.
List C2 has 8 choices.
I'm making a data entry tab so for the sake of simplicity I only want one list to show up at a time depending on the variable A or B
I have a list of car-dealers and licence plate numbers. f.e. data in tab 1 Supplier-Cars.jpg
In another tab (let's call this tab 2) I have to make drop-down lists, depending on the value in this drop-down the cell next to it must give the list of cars from this supplier. f.e. if I select in the first line Supplier 1, I get a drop-down list where I can select Car 1 if I select in the second line Supplier 4, I get a drop-down list where I can select Car 4, 6 or 7 etc. Because of the number of suppliers, I can not work with predefined lists where I put all the suppliers one next to the other...
have a drop-down in each line to go look for the cars depending on the supplier I selected ?
I am making a workbook that populates most of the data off of a data sheet, but i need it to hide the 10 other sheets until they are needed. I was hopint there would be a way to hide all but the data sheet, then use either a drop down box or if necessary a checklist (NOT my first choice, but i'll do it if i have to) to select the sheets that the tech will need to print or e-mail. Is there a way to do this:
View 1 Replies View RelatedI have the following in a Validation/Data the following tries to reference and array f2:j11
=if(d5="",teammember,index(teammember,match(f2,teammember,0)))
The array 'teammember' refers to cells f2:j11. Formula returns an error message that it can only refer to a single row or column. Is there a formula
I created a drop down list of contacts for specific accounts:
Primary
Second
Third
Fourth
When I click on each one, I want in adjacent individual cells their name, address, phone, and email. The results should only show up in the same row as per list choice.
This drop down list applies to 20+ accounts so each contact info corresponds to the accounts.
A E F G H I
So: Account... Contact Name Address Phone Email
(DropList)
And do that for all the accounts.
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 RelatedI have built a fairly complex app in excel which takes 50 webqueries and makes a table out of specific data. It generally takes ~34 seconds to run through the webqueries, and when its done, it starts all over and runs the 50 queries again on 30 second delays. My problem is that for about 5-10 of these cycles, everything works fine, but after that, the time per set of queries starts going up...in the range of 2:00 or more for the same 50 webqueries that originally took ~34 seconds.
Without posting each of the userforms and modules, is there a general way to clear out the memory or see whats bogging down the process or anything between sets of queries to maintain performance? the webquery itself is a
With ActiveSheet.QueryTables.Add(Connection:= "string" & variable
statement that gets called each of the 50 times. The only thing I can think of is that somehow variables or some other procedure isnt clearing after each of the sets of 50 queries runs. Is there any way to check that or make sure it is cleared before it begins the 50 query set again? I noticed this mostly by watching my "physical memory avaliable" drop a small amount each time the set ran, and I had to quit excel before it would return to its original amount.