How To Perform Consolidation Of Data And Sort Through Dropdown Menus

Nov 5, 2013

On the spreadsheet there is 31 days (a separate worksheet per day).

at the bottom of each sheet there is the case notes for that day. I would like all that information collating on the last sheet of the workbook (named Cases).

I would like to be able to sort through that data with the use of some drop down menus.

All i need to sort is the Brand and if the case is open/closed.

what to do so I can try myself or using the attached spreadsheet be able to sort this problem out. If you choose to use the spreadsheet to meet the requirements

View 14 Replies


ADVERTISEMENT

Data Validation - 2 Dropdown Menus

Jan 31, 2013

I am trying to add two drop down menus - part number and material description. I would like to be able to choose an item from either menu and have it populate the rest of the row with the item information.

I know how to create the drop down, but I don't know how to make it work so I can choose from either one of the two drop downs and have the other automatically populate.

View 1 Replies View Related

Validation Dropdown Menus Disappearing

Dec 10, 2004

I am compiling questionnaires in Excel and need to constrain the answers. I've successfully used validate, list and directed it to a list (containing, for example, 'USB', 'Firewire', 'SCSI'. When I first set this up, the drop down menu appeared (and the prompt message I had created). After I copied the first worksheet to use as the base for my next questionnaire, the drop down menu disappeared, although the prompt still shows and the validation still works.

Have I missed something fundamental?

I've tried creating one on a brand new workbook and copying the worksheet and it seems to function correctly. Why has my drop down menu vanished???

View 9 Replies View Related

Select Multiple Dropdown Menus At Once?

Mar 9, 2013

I have a 'diet diary' that I have built in excel that has many of the common meals I eat and details calories, carbohydrates, fats, etc and I use it to keep track of what I have eaten in the day.

However, one of the features is that on the front sheet I have lots of drop down menus that I select the different things (example, milk, banana, spaghetti bolognese) which in that puts in the nutritional info. However, each day I have different numbers of items to put in so I have to change the slots that no longer have an item in back to the default value so that it doesn't add calories. What would be ideal is if I could highlight a bunch of boxes at once and then make them all select the 'no-item' option in the drop down box simultaneously rather than selecting them all individually.

View 3 Replies View Related

How To Use Dropdown Menus Pointed To Value In Equation

Jul 18, 2013

What I would like to do is use a drop down list Type 1, Type 2, Type 3 etc and then be able to use whatever is selected to reference a value in another cell.

Excuse my ignorance but something like Cell 1 * (If Type 1 from the list is selected use figure in Cell 3, If Type 2 from the list is selected use figure in Cell 4..........) etc etc

View 2 Replies View Related

Linking Between Sheet Dropdown Menus

Oct 19, 2012

I need to link this summary sheet projects to this plant and equipment list sheet to show each project numbers separate weekly cost hire on the summary sheet.

View 3 Replies View Related

Dynamic Chart Based On Three Dropdown Menus?

Jun 25, 2014

Create a dynamic chart which is based on multiple dropdown menus. I'm trying to design a chart which shows figures per city. In the first dropdown menu you can choose a city.

The other two dropdown menus will determine the date range of the chart.

How to create this chart ....

View 2 Replies View Related

Create Dynamic Dependent Dropdown Menus

Mar 11, 2013

I need the drop down menu to update when the user adds a new entry. For example, if the user decides to add companies to the financial table, no matter how many new rows they add, our drop down menu at the top will update to include the new the entries. I want this to be the case in every table. How do I do this?

View 5 Replies View Related

UserForm Dependent Dropdown Menus Refresh?

May 5, 2014

I have put together a user form with two ComboBoxes, one dependent on the other. The first box has a drop down menu from which the user selects a name, and the second drop down populates a list from a named range according to the user's selection in the first box. The code I used is as follows:

[Code] .....

This has worked so far, however, if one selects a name in ComboBox1 and then changes their selection (ex. Initially selects John Smith from the first menu, and then changes their selection to Mike Roberts) there is an error. I am not sure why - I think the issue stems from the 'Clear' function but I am not sure how to resolve it.

View 2 Replies View Related

Dynamic Dropdown Menus With Offset Formula

Sep 29, 2011

I have a table in cells A1:B11. I have two drop down menus in cells G3 and H3. When I select red in G3 I need Bob, Sam and Sarah appear in the drop down menu in cell H3. Instead, with my current formula, only Sarah appears in the drop down in cell H3. When I select green in G3 I need Tommy, Bob, Sandy, Carl and Phil appear in the drop down menu in Cell H3. Instead with my current formula only Phil appears in the drop down in cell H3.

I linked cell H3 to the defined name Names which equals
OFFSET(INDEX(Sheet4!$B:$B, MATCH(Sheet4!$G$3, Sheet4!$A:$A,0),0),COUNTIF(Sheet4!$A:$A, Sheet4!$G$3)-1,0).

What should I add to this formula to make it work correctly?

ABCDEFGH
1RedBobRed2RedSamGreen3RedSarahBlueRedSarah4GreenTommy5GreenBob
6GreenSandy7GreenCarl8GreenPhil9BlueCarl10BlueSam11BluePhil

View 9 Replies View Related

Dropdown Down Menus Aren't Sorting With Their Cells

Oct 21, 2007

when I sort data columns that contain drop-down menus. My spreadsheet has several columns: "Patient name", "Room #", and "Phone #". Each cell within the "Phone #" column should have a drop-down validation list containing two possible entries: the room's designated telephone number, or NONE. Whenever I add a new patient to a room, these three columns need to be sorted alphabetically by patient name. But when I sort all three lists simultaneously, it looks great- but then I realize that the drop-down lists associated with each "Phone #" cell haven't sorted! They stay in their original location and don't sort with their cell. So the designated telephone numbers for each room are all screwed up.

View 9 Replies View Related

Creating Dropdown Validation Menus With Reduced Ranges?

Apr 1, 2009

i have a table of tools and their properties which i am using to provide validation lists for a number of other worksheets that record when those tools were last calibrated/tested and when they need to be calibrated/tested next.

many tools require more than one form of testing (electrical and/or calibration), but not all tools require both.

can i create some kind of drop-down menu the looks at the tool master list, but only gives the option of those that require the particular type of testing i'm recording on that particular list? how can i create a drop-down list that is more limited than the range i have selected for it to display?

View 10 Replies View Related

Creating Dropdown Menus With Extensive Changing Content

Jun 11, 2013

I have a problem (I have intermediate knowledge of Excel): lets say that I have two model years for Cadillac: 2013 and 2014, and have 3 car models for year 2013 (ATS, XTS, and CRX) and two car models for year 2014 (ELR and CTS Sedan). I want to create a dropdown menu under "Model Year" that will show me 2013 and 2014. When I click on either one, a dropdown list for all car models for that year can appear in the next column under "Models". Then, for each car model, there are different characteristics that are in other columns, such as horsepower, mileage, etc...I want all of the content to stay on one line (row), but change when I click on a different model.

View 2 Replies View Related

Index Arrays - Conditions Are Based On Dropdown Menus

Mar 16, 2014

I am looking to create an index array. the conditions for the array are based on drop down menus. I want to have it that if the drop down menu has * in it, the array should calculate everything rather than a specific value.

={IF(ROWS(I$14:I14)>$H$7,"",

IFERROR(INDEX('Sheet1'!$C:$C,SMALL(IF(('Sheet1'!$T:$T=Sheet2!$H$2)*('Sheet1'!$U:$U=Sheet2!$I$2)*('Sheet1'!$L:$L>0),ROW('Sheet1'!$T:$T)),

ROWS(I$14:I14))),"")}

In the SMALL array I have three conditions:

1.'Sheet1'!$T:$T=Sheet2!$H$2
2. 'Sheet1'!$U:$U=Sheet2!$I$2
3. 'Sheet1'!$L:$L>0

The array works fine but only if I place an actual value in the drop down cells which are H2, I2. The third condition is just to ensure it only calculates the array for rows that have numbers above 0. My question is how can I amend it so that selecting "*" in the drop down cells will calculate for everything?

View 3 Replies View Related

Insert Date Stamp Based On Dropdown Menus Selection

Aug 11, 2014

The worksheet is basically a sales funnel and I want to use a drop down menu selection in ever row of column Z to update the relevant cell, based on the selection, in the same row, column Q to Y, with a date stamp which will then not change as the drop down is progressively changed to suit the relevant stage of the job.

View 2 Replies View Related

Creating Spreadsheet Of Dropdown Menus Each Affected By The Previous Choice?

Jan 11, 2014

I am trying to build a complex equation that has multiple parameters filled by several choices from several different dropdown menus.

For instance:

I'm building a set of 5 dropdown menus, that each lead to filling certain boxes with a number created from a formula. I've provided a small sample to explain what I'm trying to do.

My problem is, I'm unsure of how to make it so certain drop down menus affect the information inside of the next. Like, I want to have someone select "Oscar" under the characters, and have the "Lances" list come up in the Weapons column menu. I don't want the Weapons menu to be selectable in any way unless a "Character" has been selected.

View 4 Replies View Related

Conditional Formatting Based On Selected Values Of Two Dropdown Menus

Dec 9, 2011

I am building a spreadsheet with a column having two drop down menus at the top that designate independent, but strict conditions. For simplicity, say I have in cell A1 a drop down menu with choices "alpha, beta, gamma" while A2 has a drop down menu for "1, 2, 3". These six values sit off to the side in cells X1, X2, X3, X4, X5 and X6 respectively.

In cell A3 I woul like to change its color, perhaps to green because it is an acceptable number (say less than or equal to 100) or change to red if unacceptable, value greater than 100.

I can do both of these commands in Excel, no problem. In my situation, I would like to change the value at which cell A3 goes from green to red dependent on the options in A1 & A2. There are 9 such combinations of alpha, beta, gamma, paired with 1,2,3 and I would like the combinations to change the fill color of cell A3 in the following manner:

If A1 = alpha and A2 = 1, then A3 will be green for values less than or equal to 100 and red for values greater than 100.
If A1 = beta and A2 = 1, then A3 will be green for values less than or equal to 80 and red for values greater than 80.
If A1 = gamma and A2 = 1, then A3 will be green for values less than or equal to 60 and red for values greater than 60.
If A1 = alpha and A2 = 2, then A3 will be green for values less than or equal to 95 and red for values greater than 95.
If A1 = beta and A2 = 2, then A3 will be green for values less than or equal to 75 and red for values greater than 75.
If A1 = gamma and A2 = 2, then A3 will be green for values less than or equal to 55 and red for values greater than 55.
If A1 = alpha and A2 = 3, then A3 will be green for values less than or equal to 92 and red for values greater than 92.
If A1 = beta and A2 = 3, then A3 will be green for values less than or equal to 72 and red for values greater than 72.
If A1 = gamma and A2 = 3, then A3 will be green for values less than or equal to 52 and red for values greater than 52.

How to create a cell that can change its conditional formatting based on the selected values of two drop down menus.

I have a few other conditions I would like to implement, say change the fill color of A3 to green, orange or red dependant on the values in A1 & A2, but I might be able to crack that if I can see how the above scenario is done.

View 1 Replies View Related

Indirect Function And Substitute In Order To Create Conditional Dropdown Menus

Jun 26, 2014

I work for a Machine Shop in the Toledo area. We use a quotation sheet to quote our products to our customers. It has 3 cells that describe the Customer, the Contact person at that customer and finally that Contact's Email Address. I have a drop-down menu in each of these cells. The first, Customer, is based off a simple list of our customers. The Contact drop-down then uses the Indirect Function to search the worksheet for that Customer. The drop down is actually based off of a Range Name saved as that Customer's Name.

However the problem is many of my customers have & or , and since Excel does not allow those symbols or even spaces in a Range Name I have to use Substitute to be able to keep the spaces and the & or ,.

It is important that on the Quote Sheet the Name of the Customer is identical to the actual name of the company so I cant use AND instead of &.

Currently my Second drop down, Contacts, has a formula that looks like this {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))}.

My problem is that when I give the Range Name a Name , my drop down doesn't work and Excel tells me that the above formula results in an error.

View 6 Replies View Related

Macro Code To Create Custom Menus & Sub Menus

Jun 9, 2008

I am using the following code to create a custom command menu.

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error Goto 0
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")..........................

I want to add a new button control at the bottom of the menu underneath the holiday control but it keeps adding it on the sub menu, which leads off the holiday button control. I want the menu to look like this.

Open Net 2 Access
Add Employee
Edit Employee
Delete Employee
Holidays (3 options on sub menu)
*New Control

View 4 Replies View Related

Data Consolidation And Sorting

May 15, 2014

I have A spreadsheet.

The spreadsheet contains 8 worksheets, of which I am only concerned with 7 as the 8th merely presents a graph.

The first 6 worksheets all contain raw data exported from BMC Remedy indicating the names of users who logged incidents to have their passwords reset or their AD Accounts unlocked over a 6 month period, hence 6 worksheets (per month basis).

The 7th worksheet contains raw data from another application known as FIM (A MS based interface which allows users to reset their own passwords) indicating all users who are registered to utilize this functionality.

The goal of my exercise is to consolidate the data and sort it to such an extent where I can ascertain which users phoned the service desk to log an incident for a password reset, even though they are registered to do it themselves via FIM ... I have been going through the list manually and it took me 10 solid hours to go through 265 ... ... ... ... I have 3'900 to go through!

View 1 Replies View Related

Consolidation Of Data From Different Worksheets

Sep 8, 2008

consolidating all data from different worksheets. I have use the consolidation function in excel but the result is not what to appear.

Example:
Sheet 1
nameAddTeljclag123jpasd321jmds234

Sheet 2
nameAddTeljolag135jfasd357eedds234werpoi567

Result using consolidate
AddTelBook1123jc123Book1321jp321Book1234jm234Book1135jo135Book1357jf357Book1234eed234Book1poi567wer0567

Expected result
nameAddTeleedds234jclag123jfasd357jmds234jolag135jpasd321werpoi567

View 9 Replies View Related

Consolidation Of Data From Multiple Worksheets

Dec 20, 2013

Consolidation of data from multiple worksheets of a workbook in a different workbook.

The, to be copied ranges are static at column B, E & AB. However, the data is required to be copied below each other for each sheet. Also the name of sheet is required to be assigned to every copied data in consolidated file...

Suppose i have 5 sheets with data... then in the consolidated sheet i need name of "sheet1" to be assigned to all the copied records and so on for all the sheets...

Sub trial()
Application.ScreenUpdating = False
Dim Wb As Workbook
Dim path As String

[Code] ........

View 1 Replies View Related

Automatic Consolidation Of Data From Multiple Sheets?

Jul 23, 2014

I have data that varies in string length across sheets but I would like to consolidate it based on name. Ideally, a pivot table would make sense to me but I have never used one across sheets (if it's even possible). I've attached a sample sheet I'm working with. This is very simplified, but assume that the shaded, gray fields are locked. Essentially, this is simulating that is getting pulled from software.

View 2 Replies View Related

Consolidation Of Workbooks To Single Workbook When Data Is More Than 66k Lines

Aug 24, 2012

I have around 21 excel files in a particular folder all the files column headings are same and but number of line differ from 0 to 50K. Overall all 21 files will have more then 2 Lakhs lines.

i require a code which consolidate the all 21 files in to single workbook. if first sheet in full then data should copy to next sheet, so on. I also require file name in the first column of destination file when files are being copied.

View 1 Replies View Related

Data Consolidation - Sum Up The Labor Hours By Work Order?

Mar 6, 2014

It is data for a time study to compute labor hours. I have managed to group everything together per "work order" and "Employee ID". I am trying to sum up the labor hours by work order. I know it sounds simple but I do have another question. Once the total hours are set. Are they still available in the same format when put in a Pivot Table?

View 2 Replies View Related

Custom Menus And Sub Menus For 2007

Feb 3, 2010

From this link: [url]

there is some clever code to create a customized menu in a dynamic Add-Ins section of the Excel ribbon; this section disappears when the workbook is deactivated and re-appears when it is activated. The custom menu has 2 main items each of which can launch a macro. There is also a 3rd main item called "Next Menu" which cascades nicely to a sub-menu. That sub-menu can have several items of its own. Very cool code.

However, I am trying to add a 4th item in the main menu, say, "Next Menu2" which also cascades to a sub-menu. The problem I'm having is when I try to replicate this code to accomplish this, "Next Menu2" appears in the sub-menu of "Next Menu", rather than as a separate (4th) main item below "Next Menu".

I believe there was another post (not sure of the date, but has since expired) which asked basically the same question as mine. There was a reply with code saying "This should work" but when I tried it, it didn't help.

View 3 Replies View Related

Create Toolbar Menus & Sub Menus

Oct 4, 2007

I want to create a submenu to an already existing user defined menu.

After creating and saving the sub menu using Tools ->Customize ,it works fine. But when

I reopen the Excel sheet it is not found.

I could not find any macro related to the original User menu.Also this menu gets displayed after the AUTO OPen macro in my excel has worked. This menu gets displayed for only that workbook and not for others.

I have been trying to find out a way to create Sub menu for that ,but could not?

View 3 Replies View Related

Dropdown Box To Change Sort Order

Feb 23, 2007

I am trying to do 2 things.

1) I want to make a drop down box with 3 differnent sort orders options in it.
- Sort by date
- Sort by Customer
- Sort by priority level

Once they select a view, it will then sort the rows to that view.

2) I want to do a filter box, so if I type a cutomers name, it will filter to only show the rows related ot that customer.

View 9 Replies View Related

Dropdown Lists Data Went Selected Won't Show In Next Dropdown Only Remaining Data

Mar 11, 2013

I am trying to pull data from more than one drop down but don't the same data to show if already use, example as follow:

First drop down Contains:- Pants
Shirts
Hats
Shoes
Dresses

if I chose Hats it should not show up in the second drop down

second drop down Contains:- Pants
Shirts
Shoes
Dresses
Is this done in data validation or combo Box? if so how?

View 2 Replies View Related

VBA To Perform Weighted Ranking From Array Of Data

Oct 12, 2011

I'm trying to achieve a table of weighted ranking based on correlations, a table I already have like the one given below

John / Products Owned 1 2 3 4
Shoes Socks Polish Brush Lace
Trousers Shoes Socks Belt Polish
Wallet Belt Trousers Shoes Socks

What I'm trying to achieve is this:

for all the top 4 correlations given for each product owned, i need to consider the ones not owned by John and recommend overall rank of the following nature:

For instance, the rank of socks here will be (1+2+4)/3 = 2.33, the rank of belt will be (5+3+1)/3 = 3 [ I'm considering the rank of belt as 5 for shoes, since it doesn't occur in the top 4 ]. Shoes, here will not be ranked since it is already owned by John.

I'm struggling to translate the logic into code here. Currently I perform it in a cumbersome manner, copying all columns to one column, then performing a pivot to find repetitions and so on.

Perhaps there's a function that could scan a dynamic sized table here, find the unique items and number of times they're repeated?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved