I have two identical drop down lists (months i.e. Jan, Feb, Mar ...) in two different sheets but in the same workbook. Is there any way where the user changes the month in sheet 1 & the other drop down list in sheet 2 changes automatically to the same month selected by the user in the sheet 1 or vice-versa?
View 1 Replies
I need a formula to sum column C if data in column A and Column B are found anywhere else in the list. For Example
A............B.......C
0103019..C.......5
0103019..1.......2
0103019..14.....1
0103019..C......4
Column D would show
9
2
1
9
I'm working on two almost identical lists but because of their nature, some rows are repeated on one of the sheets and so on. I need help matching some cells onto the other sheet. I tried the VLOOKUP formula but it doesn't work since some rows are repeated... I need the match to link especifically a Name with it's correspondent Value, regardless of the position of the name in either of the sheets and whether it is repeated or not.
View 7 Replies View RelatedI have successfully used dynamic lists that remove items that have been selected from a dropdown menu already, however, is there any way dynamic lists can be set up to handle multiple identical items and then if there are say 3 identical items in the dynamic list, once 1 has been select only 2 remain in the list...?
View 1 Replies View RelatedI am trying to create a master price list, where the 1st file called MasterPriceList will list all of our ingredients and their prices.
It would look like this:
A_________B
ING______Price
1 Ing 1___$1.00
2 Ing 2___$1.50
3 Ing 3___$2.00
4 Ing 4___$3.00
My second workbook is is a template for when we need to formulate blends.
It looks something like this:
A______B______C_______D__
1 Ing 1__50%__$1.00__$0.50
2 Ing 3__50%__$2.00__$1.00
We want to pull the cost of the ingredient from the MasterPriceList and populate column C with that value.
I have tried copying and pasting a link. This works fine until I insert a row. Even If I make the link relative (='[MasterPriceList.xls]Sheet1'!$C1), it will only adjust if both worksheets are open at the same time. Since we will have over 200 pricing sheets, it would be impractical to have them all open every time we have to insert a new ingredient into the MasterPriceList workbook.
I have tried to use VLOOKUP but it will not work across workbooks and the same is true with Drop Down lists. I read a tutorial, http://office.microsoft.com/en-us/ex...995141033.aspx , which details how to create a drop down list across workbooks but when I come to the step of defining the validation list, I get a message saying that it can't be done across workbooks.
Using Office 2003.
I have created two workbooks, one which is a master price list (MasterPriceList.xls) and another which is a pricing sheet template that will be used to calculate many different products (Pricing.xls)
I have used a VLOOKUP across the workbooks (thanks to those who helped me with that) but I cannot get a drop list to work across two worksheets. When I enter the source in the "refers to" box, I get a notice saying that you can't use data validation across two workbooks. However, this was the same error message I got when I was incorrectly inserting the reference source for the VLOOKUP function, so I don't believe it Also, there are several tutorials on the web that say this can be done. However, none of them seem to work for me.
I cannot open the Data Validation box and get to the other open worksheet, so I can highlight the area I want, with the range I want to drop down. Until I close the data validation box, I cannot get out of that sheet. I have used cell ranges as well as named ranges.
When I type in a name, I get only the text I entered in the source reference box appearing on the sheet, i.e., the drop down box will only show "=C:Documents And SettingsAllenMy Documents, etc" it doesn't seem to recognize it as a source.
I have a question regarding linking the content for data validation lists stored in a different workbook.
The scenario is as follows: I will have say 50 users using a template which contains various dependent drop-down lists. These lists work by using the INDIRECT function which calls the correct drop-down based on the previous selection. The drop-down entries are currently stored in named ranges on a hidden worksheet in each user's file.
However, if I need to make a change to the content of these lists, I will have to open all 50 files individually and change each one. So I'd like to have a master file to which all the templates would be linked which would contain the entries for each drop-down. So when I update a list in the Master file, it would automatically update the drop-down lists in each user's template with any additions deletions.
My question is, how do I create the links between the user templates and the master and have it so that the list ranges will be dynamic. So if a drop-down list contains say 10 entries and I add 2 more in the master, that those 12 now appear in each user's template?
I'm trying to work out how to allow a formula to be changed by pointing part of the code at a drop down list containing worksheet tab names from another workbook.
View 2 Replies View RelatedI 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?
I've created a name range and i'm now trying to link this to a series of pics
For example
Sheet 1 I have in A1 the drop down box (to be about 50 long)
Sheet 2 I have all pictures named as per the list in sheet 1 e.g
Named dropdown list
Apple
Banana
Pear
Corresponding pictures named
Apple
Banana
Pear
What I am trying to achieve is when "apple" is picked from the dropdown list that the "apple appears say in C1 on sheet 1
PS. Forumula based such as IF or Index etc ican't use VBA or macros yet !!!!!
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 RelatedI have an array of data that I want to be able to filter based on the selection in a dropdown box. For instance, If I select district manager, I want to show the data of only the people who are successors for district manager. For my own reasons I want to use this method rather than just applying the standard filters to the column headings.
I have attached a sample workbook : DummyData.xlsx
I got the attached dashboard example from a source online.
I was wondering how they linked their pivot chart with a drop down (data validation). This is the pivot chart on the bottom right that is labeled "Brick Styles".
Dashboard_Your_Scorecard_Complete_2010-1.xlsx
I've had a search on here, but can't quite find what i'm looking for. I have a cover sheet with a drop down list which has customers in it. under it I have the customers address, phone etc. What I am trying to do is when I click on a particular customer, I want the address and phone numbers to all fill in automatically. I have tried a few things, but to no avail.
View 12 Replies View RelatedI'm trying to figure out how to make a spreadsheet where in one column a type of asset is entered through a drop down menu and then the next few columns are attributes - model, pattern, manufacturer, etc. - that would all have values entered through drop down menus populated based on the choice of 'type of asset' in the first column.
I can get a drop down menu for the 'type' column and a dependent drop down in the second column based on choice of 'type' using =INDIRECT(), but I do that by naming the dependent lists of models for each type after the types listed in the list of types. Once I've done that I can't name the lists of patterns after the types because those names are in use by the model lists. So how would I make multiple lists of attributes associate with the same list of types?
I have a work book that contains various sheets with different information.
I have two different categories in one of the sheets,1 which contains counties and the other containing districts.
I have done a validation for the county which allows me to pick a county from a drop down list which works fine.I would like to create a drop down list for the districts aswell such that when i select i.e southyorkshire from the counties dropdown list,the districts drop down list will bring only districts in southyorkshire,excluding all the other districsts.hopefully i'll like the same to apply to all other counties.
I have a workbook with several drop-down lists. The names for the lists are on a seperate sheet in the same workbook. I have used data-validation and allow list in all the cells with drop-downs. I have used =NAME in the source. I opened the workbook this afternoon for the first time in a couple of days and I am no longer able to select from a dropdown. I can type in the cells, but if I type something not included in the list, I get the "The value you have entered is not valid" error. So I feel like the validation is working on the cells, I'm just not able to select from a drop-down list. "In-Cell dropdown" is selected.
View 9 Replies View RelatedIm trying to create a spreadsheet which has a drop down list in a cell, then when a selection is made a predetermined dollar figure will be entered in the cell next to it.
For Example:
Cell A1 Drop down list I have 20 cities listed, If I select St. Louis, I want the cell next to it to populate with $250.
Every city has a different dollar amount tied to it. So any time I select a city I want the specified dollar amount to populate the cell next to it.
I have been able to use the previous information to create dependent drop down lists but I have been unable to apply the settings to entire columns versus just one cell.
Is there a way to get the dependent lists to correspond to the cell beside it without having to manually type in each cell name?
I am trying to attempt to create a payroll spreadsheet with certain aspects and with multiple worksheets. so what im trying to accomplish is this
1/ on my main worksheet would have the payroll template there would be 2 columns (1) would be "routes" (2) would be the dollar value of that route.
2/ i want to be able to pick from a drop down list in colume (1) which will have various routes such as A,
B, C, D, E etc
3/ once i have selected a route in colum (1) i want to have the dollar value that is associated to that route to show up in column (2)
ex/ route A worth $1
route B worth $2
create a dropdown list but thats about it, i have not been able to link any values together.
I'm trying to simplify a template we use regularly at work. On the front end page, the one that eventually gets turned into a PDF and sent to a customer, I have a selection of drop downs, which I am pretty comfortable with. I also have some other fields that are populated by vlookups, and again, I have no problem there. What I am struggling with is creating a second drop down list that only has options relative to the first drop-down list. From what I gathered by searching on this, I may have to change the format my data is in, note that I have a pretty basic knowledge of excel.
Master Data (from where I want all the drop downs to come from) - see attached screenshot.
Capture.JPG
So, on front end, I have a drop down that selects from column A. Beneath that there are two rows that auto-populate depending on what company was selected in the drop-down.
What I want is a second drop down list from D, E & F.
So, If I select ABC Limited in the drop down menu, the second drop down should only have John, Jim & Jane as options.
Is it possible to do this with the data in the format I have, or would I need to change the format? I have +/- 500 entries to do, so obviously I'd like to not change my format if possible.
In worksheet 1 I have a list of employee numbers (column A) and and names (Column B)and in worksheet 2 I have a list of employee numbers (column A) and their payroll contracts (Column B). 1 employee can have multiple contracts).
What I'm trying to achieve in workshet 1 is to have a dropdown list ( Column C) which returns just the contracts that relate to the employee number in column A.
how to make few dropdown lists depending on one before.
Example: I have table with next data: COLUMN A: (A1) Expense Type ( in all columns under A1 are types of expenses );
(A2) total trip cost;
(A3) Prize promo tour cost;
(A4 ) court renting cost;
(A5) Prize IPP cost
In columns B1, C1, D1, E1 are cities. Under each city is value for type of expense. I have uploaded worksheet for example.
Now, problem is next: I am trying to make drop down lists ( in new work sheet ) by using depending data from data table.
1.I made drop down list in first cell (let's say it is an A1 cell ) where I can chose city ( for example City of Zagreb ).
2.Then in next cell (B2) I would like to choose type of expense in drop down list ( for example Total trip cost ).
3.Finally, in C3 cell I would like to make drop down list that offers me just expense value for combination of chosen city ( Zagreb ) and chosen expense type ( Total trip cost ).
I have a dashboard with 2 validation dropdown lists - 1 for Brand name (text) and one for month (mmm-yy) which go and run various pivots, etc.This works perfectly - except the dropdown list is so small its unreadable.
So I've tried a workaround using Combo boxes which I found here:
This does work, but when I select a month on the 2nd Combo Box, it is then displayed in number format eg, 41699. I've tried changing the format to mmm-yy but it keeps displaying as a number.
Is there any way I can make it display as mmm-yy?
I am trying to get a listbox I created in from DataValidation to be dependent on another list.
For example, when I select Course 1 (a list item) from the Course List drop-down, I want the cost to auotmatically populate in the Cost drop-down (the other list).
How do I get this to happen? Here is the code I created in VB, and I am not sure if this is the right course of action:
Sub Automated()
Sheet1.Cells(2, 1) = "MATLAB"
If (Sheet1.Cells(2, 1) = "MATLAB") Then
Sheet1.Cells(2, 3) = "31"
End If
Sheet1.Cells(2, 1) = "INCA"
If (Sheet1.Cells(2, 1) = "INCA") Then
Sheet1.Cells(2, 3) = "41"
End If
[code]....
I don't want to use the user form, however from VB. I want to use the regular drop-down. The code seems to work, but, each list item in the drop-down is not pulling the data I want it to. I need to activate the drop-down as a click event somehow. It's just recognizing the cell A2 as a whole and not the individual list items. I want to each list item to be their own object, and to automatically populate the cell C2 with their cost when they are selected.
I have dropdown lists in a form and i want to make it so that the user can not input a value other than the ones in the list, how can i do this?
View 7 Replies View RelatedI am trying to set up a spreadsheet with linked dynamic drop down lists, based on a table.Using the OFFSET function, I have managed to get this to work if the table is on the SAME sheet at the drop down lists. But I want to be able to put the drop down lists onto a different sheet, so that I can lock and hide the table.
I don't want to use the 'name based on the previous column' idea, as the table is large and will change weekly. the drop downs show all values, even duplicates, which I would like to get rid of.
I'm using Excel 2007 and am trying to set up 3 columns of drop-down lists each dependent on the choice selected in the previous column's selection. It seems like it would be easy to figure out but I can't seem to do it.
I've already set up the arrays for the lists and "named" them but can't get the cell to be formatted automatically according to the previous columns selection.
I have three drop-down boxes in 3 adjacent columns. Column 1 is free-choice, Columns 2 and 3 drop-downs are variable dependent on what is in Column 1. That works fine.
I have an issue with over-type but I can solve that with protection. Again fine.
BUT:
1. How do I make the user choose something i.e. not just leave the cell in column 2 or 3 blank by ignoring it (i.e. blank is an error but only after drop-down in column 1 is activated)
2. Also, if the user has completed the line (columns 1,2 and 3) and then changes column 1, columns 2 and 3 are now reading from the incorrect drop-down boxes (i.e. they are now in error but this is accepted and not flagged).
Do not use validated drop down lists in merged cells.
It will only be a matter of time before it breaks Excel.
(Both 2007 & 2010)
