Dynamic Linked Validation Lists

Jan 21, 2008

I have a spreadsheet where I keep all records of all the rock climbing I have done.

I am after a way of choosing a climbing area either by data validation or combo box (already set up as a named range) and then the climb names available in another validation list shows only those from that particular climbing area.

From what I have seen from various web sites it seems the climbing area names need to be in a unique list (is this correct). My sheet looks like the following:

Column A | Column B (climbing area) | Column C (climb name)
Date | Mt Arapiles | Agent Orange
Date | Mt Arapiles | Auto Da Fe
Date | Morialta | Asgard
Date | Morialta | Al Sirrat
Date | Buckaringa Gorge | Agent Orange

The name in the climbing area column will be repeated often.

The climb name should be unique to each climbing area, but the climb name my be repeated in another climbing area.

The climbing areas are anamed range on a separate worksheet.
The details of the climbs has all the details of the climb i.e. the Climb Name; Climbing Area (VLookup); Grade ; Distance ; Single or Multi Pitch ; Lengths of each Pitch.

I already have a working example of the spreadsheet (220KB) but it currently works backwards i.e. I use validation to find the climb name and then this uses the INDEX function to return the climbing area.

I would like to be able to choose the climbing area and then the next available choices would be limited to that climbing area.

View 3 Replies


ADVERTISEMENT

Validation Lists Linked To Each Other

Nov 5, 2006

I've create a very large list using the procedures described in MatchingList sample workbook. For the most part everything works great however, there are about 4 list where when I name the range to capture the assoicated data, the name does not stick. I.E., I add a name with the title in $F$1 as "Address 1" and specify the range as $F$2:$F$8. When I select "Address 1" from the first cell and then go to the next cell to select the options, nothing is there. When I go to any of the cells below $F$1 I do not see the range name in the Name Box. Can someone explain why some of the names work and others don't.

View 2 Replies View Related

Set Up Spreadsheet With Linked Dynamic Dropdown Lists?

Apr 24, 2014

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

View 2 Replies View Related

If Then Statements Linked To Data Validation Lists On Different Sheet?

Apr 13, 2014

On Sheet1 I'm attempting to auto populate specific cells in columns B,C & D with information found in Data Validation lists (found on Sheet2) based on the "value" chosen from a list in column A. For instance,

If A2=Pig Then B2=Slop, C2=Pen, D2=Food

Is this best accomplished through VBA or a basic Function?

View 5 Replies View Related

Linked Validation Lists & Lookup Based On Choice

Aug 12, 2007

I'm trying to continue on this post: Populate Cells After Choice From Drop-Down

I'm trying to expand on CNL's idea. What I need to do is create a spreadsheet that autopopulates two blocks based on the dropdown box that preceedes it. I've got it to partially work, but can't figure out the next step. The differnce between my sheet and CNL's sheet is my drop-down is an indirect data validation.

The easiest would be to have my data in one long list, but my goal is to make it easier on the user to find the specific procedure (I'm trying to build a budgeting spreadsheet for a medical office). So in box A15 there is a list of the different service area, which gives a list of choices in C15 based on the choice selected in A15. My goal is to have G15 and G16 list the cost and charge respectively.

M1 - O3 is the data for Radiology
Q1 - S4 is the data for Labs
U1 - W4 is the data for Office

View 9 Replies View Related

Dynamic Data Validation :: Dynamic Lists

Jun 24, 2009

I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-

1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection

Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?

View 5 Replies View Related

Dynamic Named Range As Validation Lists

Nov 13, 2006

In the attached example I have a column of data on worksheet 2. Extra rows will frequently be added and removed from the column. I want to use that column of data to populate a validation drop down box for a whole column of cells on sheet 1. (Target cells coloured yellow for demonstration purposes)

I don’t want the validation drop down to be full of blank rows so I set up a dynamic named range to refer to the column on Sheet2. It all works really well...but...only for cell D6 on sheet 2. It doesn’t work on any of the other target cells on sheet 2 or on sheet 1. I have been looking at this for far too long and it has begun hurting my brain.

View 2 Replies View Related

Dependant Validation Lists With Dynamic Named Ranges

Jun 18, 2008

in a sheet I have two cells using data validation and dropdowns - the source for the first cell is a static named range - no problems. The source of the second cell is dependent on the value in the first cell and the sources are dynamic named ranges.
The dynamic ranges in cell#2 is named according to the value in cell#1 and I therefore have referenced the ranges using the INDIRECT function - but this only works with static ranges.

In the data validation source field for cell#2 I have the following formula:
=IF($A$1="",the_full_range,INDIRECT($A$1))

View 7 Replies View Related

Obtaining Results From Linked Lists Automatically

Dec 4, 2008

I want to know how to link te two lists in my register so that when i select a particular route in one column the corresponding column automatically selects the transit time from the reference list.

View 2 Replies View Related

Data Validation :: Selected From 4 Validation Lists

Sep 26, 2007

I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?

for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...

(I do not want to use a combo box for this)

View 9 Replies View Related

How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

View 9 Replies View Related

Dynamic Named Ranges With Linked Data

Feb 4, 2010

I am using OFFSET to make dynamic named ranges- the only problem is that I'm having trouble using it with linked data-

The Situation:

-Cells A1:H500 are linked to another sheet, with each cell containing a formula like =IF(ISBLANK('C:...[workbook.xlsx]worksheet'!A1),"",'C:...[workbook.xlsx]worksheet'!A1)

-Currently only cells A1:H100 have data, but this is dynamic and will change

-I want a named range that will only select the cells with data in them...the problem is that COUNTA counts the link formula even if the cell is "blank" (because it isn't blank).

-I hate hard-coding ranges...the solution for the named range =OFFSET($A$1,0,0,500-countblank($A$1:$A$500),8) is ugly!

Any solution to making dynamic named ranges with linked data that may or may not be "" ?

View 1 Replies View Related

ActiveX Combobox With Dynamic Linked Cell?

Feb 4, 2012

I have a golf teetime reservation spreadsheet. For each tee time there can be up to four players names so I use 4 columns. Times are listed in rows every 10 minutes beginning 6am until 5pm for a total of 66 rows per day. I have a member/customer list that I have on a separate sheet that I've named. I am using the ActiveX combo box next to each possible entry (so 4 columns times 66 rows = 264 combo boxes). It's a little slow and takes a long time to open. I like the ActiveX combo box because it allows me to define the box format and display and it allows search/read/write capability. The idea is the customer calls, I ask what time they want and we arrive upon an available time. I ask for a last name, I click the combo box, type the first letter of the first name and then scroll if necessary to the name and click it to add the name to the time/player cell. If no name in my list we simply type it in and I'll capture it next time I refresh my customer list.

way to dynamically define the linkedcell in the properties so that instead of 264 combo boxes, I might have one combo box that I can click on and then direct my selection to the cell I want it to go into.

View 5 Replies View Related

Popup Form Linked To Dynamic Graph

Aug 6, 2008

This may be a tough one. Through a button, I am trying to create a popup window displaying an excel chart, viewed always on top, dynamically linked to my spreadsheet data. The idea would be to see it evolve as I input data, and be able to open as many as I want. I guess I could create a macro to paste/clear the graph, but a pop up window would appear more professional and more user friendly.

View 2 Replies View Related

Dynamic Named Range Linked To A Drop Down List

Feb 27, 2007

I purchased the Excel Hacks book, and when attempting to do hack #53 with a drop down list, I can not get it to work. I do exactly what the book says to do, however, my chart and my drop down list are not linked together, and when I change a name in the drop down list, the information on the chart does not change.

View 2 Replies View Related

Excel 2007 :: Data Validation List Linked To Different Sheet?

Dec 11, 2012

I am using excel 2007

I am attempting to put a drop down list using "Data Validation". I can get it working when my list is on the same sheet (sheet 1) and the column of cells I want the drop down list to show up in....(you know..when the drop down list shows up in each individual cell)......BUT...when I put the list on another sheet (sheet 2) and try to do the "Data Validation" back on sheet 1, excel won't let me go highlight the list on sheet 2.

I even tried writing sheet2 and the range and that still doesnt work.

View 1 Replies View Related

Validation Lists Using Vba

Mar 30, 2007

I need to find a solution that will prevent users from pasting values into cells that do not match values in a named range or validation list.

I think the best method would be to have it trigger with a SheetChange event. I would like it to check to see if the enteredpasted value(s) in a range of cells is equal to one of the following:

K
1
2
3
4
5
6
7
8

If it does not, I want the cell cleared and a message box shown.

I am sure this is simple, stock code for someone, but I am teaching myself vba and am fairly green. Does anyone have this handy in thier code library?

View 13 Replies View Related

Dynamic Related Lists

Aug 26, 2008

I have a vehicle intake-form in which users have to enter vehicle data.

I also have a huge list containing make/model information.

I would like to be able to use this database in the following way : I want the user to select the make in cell A1 , and then the model in cell B1. It must be possible to choose for example the make "BMW" in cell A1 and then cell B1 should only display the BMW models, and not all others that are present in the list...

I tried to use Data Validation with a list of choices, this works fine for the make (A1) ; but how do I make the list used for Data Validation on B1 dynamically related to the value in cell A1 !

View 7 Replies View Related

How To Cascade Validation Lists

Jul 19, 2014

I will have users filling out cells (e.g. on Input sheet) and want to cascade the drop-down lists based on selections made. The basis for these selections is on the LISTS sheet.

So for Area, if they choose AREA1, then the Main Activity should only have 1,2,3,4,5 as available drop-down options and if they choose 1, then Secondary Task should only have A or B as options in that drop-down and finally Supervisor option should only be XX.

My question is, is there any quick way to create these cascading drop-down lists from the structure I have currently in place on the LISTS sheet. Or am I going to have to rearrange the data on a separate sheet? I will have up to 40 'AREAS' with dependent selections.

View 10 Replies View Related

Validation Drop-down Lists

Oct 28, 2008

How to make a drop-down list appear when a cell is double-clicked as opposed to just clicking on the box to the right of the cell that appears when you click in the cell? I want to be able to use the ability to drag-copy the cell contents from the little square in the bottom-right of the cell, but cannot do this when there is a validation drop-down list!!

View 14 Replies View Related

Lookup And Validation Lists

Jul 3, 2008

I have been going around in circles trying to work this one out... I have set up my "Work Packages" sheet ready to use with an excel timesheet. I have entered all ......

View 9 Replies View Related

Conditional, Dynamic , Unique Lists

Jun 20, 2008

I am trying to create a dynamic, unique listing. I have two data lists (expanding 45,000 rows). They are as follows. I CANNOT use macros due to security constraints :

ARGENTINE PESO ··············Cash Account 1
ARGENTINE PESO ··············Cash Account 2
ARGENTINE PESO ··············Cash Account 3
AUSTRALIAN DOLLAR ·········Cash Account 1
AUSTRALIAN DOLLAR ·········Cash Account 4
AUSTRALIAN DOLLAR ·········Cash Account 7
AUSTRALIAN DOLLAR ·········Cash Account 9
AUSTRALIAN DOLLAR ·········Cash Account 15

I basically want to be able to type "Cash Account 1" in my control cell, and have a dynamic dropdown created that only summarises those currencies available in that cash account, so in this instance, it would be both ARGENTINE PESO and AUSTRALIAN DOLLAR. I have created a solution, but due to the 45,000 row range, it is very processor heavy, and I would like to be quicker i.e. type the cash account you want to analyse, and the combo box / data validation dropdown(?) will only show relevant currencies. I do not mind using helper cells and / or a master sheet to analyse each currency for true/false. Also, the columns can be reversed if that makes it easier.

View 9 Replies View Related

Dynamic & Dependant Lists On UserForms

Mar 27, 2008

what i need to do is display a list of items, with a dropdown box next to each item. the dropdown boxes are identical. for each item in the list (e.g. each musician), the user should be able to select an item from the box ("percussion", "horn", "string").

my problem is that the number of items in the list is not set, so the list of name labels, dropdown boxes, etc needs to be generated dynamically. is it possible to generate a list of labels/dropdown boxes using a loop? (as i understand it, this would involve writing code to write code.)

View 6 Replies View Related

Merge & Sort Dynamic Lists

Aug 5, 2008

I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..)

Detail:
I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employees. I've been able to successfully define named ranges to work with these as dynamic lists. I can append names, or delete names, and the Chicago, or Seattle boxes (drop downs created with data validation formulas inside named ranges) will reflect the updated names correctly. I have been unable to make a single drop down with all the names from both offices, that is updated dynamically. I've attached a sheet so that this is easier to understand. Basically I need the "BOTH" drop down to actually work.

The Chicago range is defined as:
=OFFSET(Sheet1!$A$2:$A$11,0,0, COUNTA(Sheet1!$A$2:$A$11),1)
The Seattle range is defined as:
=OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$B$11),1)

In the boxes on the right of the lists, I just have a Data Validation List formula as:
=Chicago and the other as =Seattle

View 4 Replies View Related

Disappearing Data Validation Lists

Sep 22, 2008

I have a very large spreadsheet that I work with on a daily basis. The main portion of the spreadsheet is broken down into weekly sections (for example, columns GF thru GM contain data sections for one week, which then repeats with the next set of columns, etc.). Below these weekly sections are detail and summary sections for all the data entered above. In the main weekly portion, each week contains a size drop-down list which calls to size information in the detail portion below. Recently, the data validation lists have started disappearing. By disappearing, I mean that the data validation for the cell no longer exists on a large portion of the list cells. I have tried recreating all of the data validations, saving the file, and re-opening it, and when I do, they've disappeared again. I have no idea what to do at this point. The functionality of the spreadsheet is crippled without the sizing drop-down lists (for various lookup reasons).

View 14 Replies View Related

Data Validation :: Dependent Lists

Oct 14, 2008

See the attached example. I have created a dependent data validation. The list available in B2 is dependent on the item chosen in Cell A2.

What I would like to know is how do I expand this validation down columns A and B. I don't want to have to add the validation in each cell individually!

View 4 Replies View Related

Auto Changing Validation Lists

Jul 1, 2009

I am working on a spreadsheet and using a validation list which is working fine. However i know want to have multiple validation lists that change depending on what you choose from the first one.

For example in the first list it would say, Vegetables, Frozen, Fruit etc

If i chose Fruit in the second list i want it to then list Bananas, Apples etc etc.

View 9 Replies View Related

Make Validation Lists Longer Than 9?

Feb 8, 2010

Can you make validation lists longer than 9 items?

View 2 Replies View Related

Data Validation Dependent Lists?

Dec 31, 2012

I cannot seem to add more dependents using this code. I think it is in the "If / then / else" structure that it is limited to only those three "groups". But I need to be able to add at least one more dependent list to the code.

View 4 Replies View Related

Dashboard With 2 Validation Dropdown Lists?

Jul 22, 2014

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?

View 14 Replies View Related







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