Create Common Dropdown Lists Across Multiple Workbooks?

Apr 28, 2014

I 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.

View 2 Replies


ADVERTISEMENT

Template Using Validation Tool To Create Dropdown Lists

Sep 17, 2012

I have been working on a template using validation tool so as to create drop down lists. Now I have to select data from a combination of two fields.

Example: I need info from Dataset A and Dataset B to create a new drop down list from Dataset C.

I tried with If AND formula =IF(AND(B4="Test";B6="de10");"ok";"no") where due to the selected values I got Ok as a return answer. However I need to have a dropdown list of relevant values selected and I am having some issues.

When I name my group (Dataset C) as DE10 so that it can be linked to DE10 value (dataset B) so that I can do a validation then I get an error as DE10 is an excel field name; therefore I thought of the IF AND solution as I was thinking of doing so that I can name Dataset C as XYZ (instead of DE10) on the data sheet however I am having some trouble in getting the drop down list to work.

Basically I need a drop down list from dataset C when certain info is picked from Dataset A and Dataset B.

View 1 Replies View Related

Using VBA In Excel To Create Formatted Document From Dropdown Lists

May 18, 2014

We have a proposal generation tool that we use that is based in Excel, and it works very well indeed. However, we're wanting to add a 'Custom Contract Generator' tab to the spreadsheet, and I'm at a loss with how to build it.

What it needs to do is :

- Allow the end user to select which contract clauses are required for the proposal they are working on, ideally via a range of dropdown boxes.

- Use that selection of clauses via dropdown box to create a compiled, formatted text list made up of those clauses.

- Enter that information into a landscape orientation Excel tab in such a manner as allows for tidy, business-suitable printing.

In short, I want the ability to pick what clauses we want to use, and have Excel generate a custom contract Terms and Conditions page based on my selection. And I've no idea where to start.

View 1 Replies View Related

Excel 2011 :: Multiple Dependent Dropdown Lists

Feb 26, 2013

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.

View 14 Replies View Related

How To Get Multiple Dropdown Lists To Populate Results In Named Cell

Sep 28, 2012

I 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?

View 1 Replies View Related

Autofill Cell Based On Results Of Multiple Dynamic Dropdown Lists?

Aug 25, 2014

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

View 2 Replies View Related

How To Create Multiple Checkbox Lists In Each Cell

Feb 10, 2014

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.

View 1 Replies View Related

Create Unique Lists From Multiple Ranges

Mar 12, 2008

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?

View 9 Replies View Related

Match Two Lists With One Common Column?

Oct 17, 2008

I have two lists, let's say:

A B
Eva 1982
Anna 1981
Lisa 1983
John 1980
Steve 1972
...

And

A B
Eva Female
John Male
Lisa Female
...

Can in any way match the above lists automaticly, so the information from List number 2, column B, appears at the correct place in List number 1, Column C? For empty cells, no information is fine.

View 3 Replies View Related

Find Common Values In Two Lists

May 10, 2007

I have two lists, one is 15,000 records, the other 100 records. I want to find the common records between the two.
I am using this formula currently, but is returning an incorrect result--
=INDEX($B$2:$B$11157,SMALL(IF(COUNTIF($M$2:$M$100,$B$2:$B$11157)>0,ROW($B$2:$B$11157),1000),ROW()-ROW($P$2)+1)-ROW($P$2)+1)

this is the record number in M2
BU1HAD80

it is returning
BI1METBRSM
as the common record. This formula is from Joseph Rubin's book F1 Excel Formulas and Functions

View 9 Replies View Related

Mark Common Cells Between 2 Lists

Dec 16, 2007

I have 2 lists in column A and column B. For each cell in column B, I want to put a "YES" in the corresponding cell in column C if the contents (of the cell in column B) are somewhere in the entire list under column A (I think the list in both columns is about 5000+).

I have logged in after quite a gap and found that all my subscribed threads (gathered over a year) have vanished

View 3 Replies View Related

Count Of Zip Codes Common To 2 Lists

Apr 29, 2008

list of 30+ zipcodes, in a word document and a list of 90+ zipcodes in an excel document (which contain the 30+ from the word doc) I need to:

get a total count of only the 30+ zipcodes from the word document that exist in the excel (ignoring the other 60+ zips). I do not need:

to get a count for each single zipcode

View 2 Replies View Related

VBA Macro To Create Multiple Data Validation Lists From Variables & Named Ranges

Nov 10, 2008

I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is

Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName

It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.

View 4 Replies View Related

How To Create Dropdown List Of Multiple Macros

Feb 18, 2013

I have 6 macros recorded and I need to create a drop down list of macros so that I know what kind of Macro I am using.

View 9 Replies View Related

Create Master Workbook From Multiple Workbooks

Jul 3, 2013

I need to consolidate a lot of information from multiple workbooks all the workbooks are located in a folder, i am not bothered about running each one separately or a group at a time, each work book has ten sheets with each sheet in the workbook being different, it needs to add to the next blank row on each sheet.

View 1 Replies View Related

Create Multiple Workbooks From A Single Workbook

Jun 3, 2009

On a weekly basis, I receive a single worksheet in a workbook that contains ~30,000 rows of product sales.

Row one contains column headings that is unique to all other rows. Column A contains the store number that sold the product. There are ~50 unique store numbers.

I am trying to create a macro that will break the report up into seperate workbooks.

For example, assume in column A there are 30 rows of data for 3 different store numbers (say store 112, 386, & 798, each with 10 rows of data). I want to create 3 new workbooks and include the same column heading for each. For example, name new workbooks as follows: "Store 112", "Store 386", "Store 798".

View 7 Replies View Related

Macro To Create Multiple Workbooks Based On Data

Jul 31, 2013

I have a worksheet with the following columns:

Brand
UserName
FirstName
LastName
Email

[Code]....

This is a relatively long list - 1000's. What I'd like to do is create a macro that sorts by brand, and at every change in Brand, copy the user details (Username, firstname, lastname, email) to another workbook with those labels at the top of the table. Upon completion, it saves the workbook with the name of the appropriate brand.

E.g. I'd have 3 workbooks:
- BrandA.xlsx - with the 2 user listed
- BrandB.xlsx - with the 1 user listed
- BrandC.xlsx - with the 1 user listed

View 3 Replies View Related

Create Multiple Workbooks With Data From Single Workbook

Mar 18, 2014

I have a TEMPLATE workbook that has 106 cells (all in the same worksheet) that need to have data input in them.

I have a separate DATA workbook with 3,000 rows of data, each row has 106 columns that correspond to the cells in the TEMPLATE workbook.

I need to create 3,000 new workbooks that are populated with the data from the DATA workbook.

View 3 Replies View Related

Create Multiple Workbooks With Names Based On List

Sep 2, 2007

I have some very tedious work to do in Excel:

table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .

S7999 u ee
S7999 w aa

My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?

View 9 Replies View Related

Merging Excel Workbooks Based On Common Field?

Jul 8, 2013

I have two workbooks, Quotes and Invoices. They have a common field (column) between them called "QuoteID". Since not all quotes turn into invoices, there are more rows in the Quotes workbook than the Invoices one. Is it possible to match the "QuoteID" cell values in Invoices to just the ones in Quotes that match up, and then merge them into a new worksheet? If not, is there an add-on that will accomplish this?

View 1 Replies View Related

Create Multiple Workbooks From One Single Workbook Based On Customer Code?

Jul 6, 2008

I have an excel file which contains following data in it.

Col-A Col-B Col-C ......
Cust Cd Name Sales
=======================
101 AAA 1000
101 AAA 500
101 AAA 3000
102 BBB 800
102 BBB 200
103 CCC 200
103 CCC 200
103 CCC 200

I need to create following three workbooks with name based on Cust Cd from above excel file.

Workbook - 1 : 101.xls which contains records only pertaining to Cust Cd 101.

Workbook - 2 : 102.xls which contains records only pertaining to Cust Cd 102.

Workbook - 3 : 103.xls which contains records only pertaining to Cust Cd 103.

View 4 Replies View Related

VBA Macro To Create Multiple Workbooks From One Master Workbook By Cost Centre

Jun 27, 2013

VBA Macro to work through a worksheet that consist of static data (tab 4) cost centres and to populate a new work book per cost centre consisting of three tabs for every cost centre found in the static data.

The master Workbook has the following tabs:

Tab 1 is called travel and consist of column a which is the cost centre number (plus 14 other columns)

Tab 2 is called Mobile and consist of column a which is the cost centre number (plus 14 other columns)

Tab 3 is called Expenses. and consist of column a which is the cost centre number (plus 14 other columns)

Tab 4 Static Date column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns).

If no information found on a specific cost centre, the tab will include the headers and return the words "No transactions for this period"

Whilst splitting data into Tabs the workbooks should check against the Static Data table and include cost center description in Column B of each tab in the new workbook.

If master workbook consist of the following....

Tab 1 is called travel and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,557,

Tab 2 is called Mobile column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns) cost centres, 555, 78689,

Tab 3 is called Expenses. column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,

tab 4 Static Date - column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns). cost centres, 555, 557,78689

It should output 3 workbooks by cost centre number.

One for 555, which consists of 3 tabs, travel, mobile and expenses.

A second for 557 which has 3 tabs travel, mobile and expenses, but only with data in the travel tab.

A third for 78689 which has 3 tabs travel, mobile and expenses, but only with data for mobile data.

The workbooks will be replicates of the contents within the tabs where column a wil be the cost centre plus 14 additional columns.

View 5 Replies View Related

How To Create Third Column With Common Value

Aug 22, 2014

I have two columns(1 and 2), Column two will for each data set have two common values. For most of the data set where column two have the second common value, i can find it based on column 1, but where column 1 differs, i do not know how to get data based on column two.

See attached, column 1 and 2 data. Column 3 I took out one common value, column 4 I just did a IF function to populate blank spaces with common value, but for row 24 - 26 if does not work because column 1 does not follow same logic. Is there a way to populate a third column with one value(can be either of the two common values in column 2(note there will be multiple data sets)?

doc list.xlsx‎

View 5 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

Dependent Dropdown List - 3 Levels - Remove Common Entries From Selection - Show Relevant

Dec 4, 2013

I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.

I have attached a file with sample data which has the following format.

Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)

Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)

Column 3 = vehicle colour available for vehicle package option

Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)

Also please note that Column 3 values of colour options vary for each 'vehicle package option'.

The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.

Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.

Sample Data - Dependent Drop Down Cells Question.xlsx

View 8 Replies View Related

Macro To Create New Column From Common Words

Jun 4, 2013

I have a spreadsheet with approx 7000 rows, many of which contain the same item but with flavors and other variations on the end. An example would be:

VB:
A B
10142 6kg of whey bundle With Free protein shaker-Banana
10143 6kg of whey bundle With Free protein shaker-Chocolate
10144 6kg of whey bundle With Free protein shaker-Strawberry
10145 6kg of whey bundle With Free protein shaker-Unflavoured
10010 **Bodybuilding Warehouse Premium Whey Probiotic - 2.2kg
10011 **Bodybuilding Warehouse Premium Whey Probiotic - 2.2kg + FREE Shaker

Would it be possible To create a New column (column C) which would display all common words from row b into the New column Like below?

A B C
10142 6kg of whey bundle With Free protein shaker-Banana 6kg of whey bundle With Free protein shaker
10143 6kg of whey bundle With Free protein shaker-Chocolate 6kg of whey bundle With Free protein shaker
10144 6kg of whey bundle With Free protein shaker-Strawberry 6kg of whey bundle With Free protein shaker

[Code] ....

I've attached a larger sample of our list to get a better idea of different variations that are on the spreadsheet.

I think what we need is something similar to this thread[URL] .....

Sample List.xlsx

View 2 Replies View Related

Find Common Words In Cells And Create New List

May 29, 2005

I have a spreadsheet of part #'s, descriptions, manufacturer names, and manufacturer part #'s. (It's a list of the inventory in my warehouse). Each row contains information for just the item in that row. Row 2 references another part in my warehouse, row 3 yet another, and so on.

Many of the parts have more than one potential manufacturer and part #, (meaning that any of those manufacturer's part #'s are basically the same tool; just different brands. At one time we may get a shipment of one, at other times we may get a shipment of another). For example, a screwdriver may be listed like this:

Part # 1234 screwdriver, mfg Snap-On, part # 456, mfg Stanley, part # 789, mfg Mac Tool, part # 439.

Then further down the list, there may be another part listed like this:

Part # 9980 wrench, mfg Stanley, part #741, mfg Snap-On, part # 852, mfg Proto, part # 369.

If you can imagine that data across the cells of a spreadsheet row, notice how the mfg name 'Snap-On' was the first mfg name on the screwdriver, but it was listed as the 2nd mfg name on the wrench.

So, here's my question: I want to be able to group all of the items made by any one manufacturer together in a new list. If all of the manufacturer names were in the same column, I could simply sort the list by that column, but since I've got thousands of rows with the mfg name I'm looking for in different columns on different rows, I thought maybe a macro could search each row for the word I'm looking for, then if found, take the whole row and copy it to a new worksheet. So the end result would be, If I wanted to see all items of which Snap-On is an acceptable supplier, I could get a list of all potential Snap-On items grouped together.

I'm sorry this is so long. I may have over-worded this and it may not be too clear. I could email an example of the spreadsheet if anyone needed more info to figure out what I'm looking for and was willing to take a look at it.

View 9 Replies View Related

Two Dropdown Lists Associated With One Set Of Assets

Jun 3, 2014

I'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?

View 7 Replies View Related

Link The Different Dropdown Lists

Feb 27, 2009

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.

View 7 Replies View Related

DropDown Lists Not Appearing

Dec 10, 2008

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 Related







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