Return A Dynamic Column Via Data Validation
Feb 2, 2010
I am a personal trainer/strength coach, and i use excel to write my workout programs.
To save time in writing programs i have made lists of all of my exercises in a separate workbook. I have them all linked via data validation in drop down lists to different templates for different populations ;weight loss, injury prevention, sports performance, ect. And it has been working great!
However here is my problem, I would like to be able to return an entire list of an exercise circuit with one drop down list and have it input all the exercises in cells below the drop down list.
So for example i have three columns on a separate sheet.
Column one
Circuit A
exercise 1
exercise 2
exercise 3
Column two
Circuit A
exercise 4
exercise 5
exercise 6
Column Three
Circuit A
exercise 7
exercise 8
exercise 9
In cell A1 i want a drop down list that that has circuit A, B, C,
using the drop down list if i pick circuit A it will return exercise 1, 2, 3 in the cells A2, A3, A4, receptively.
View 9 Replies
ADVERTISEMENT
Jun 27, 2008
I am applying data validation in my workbook. The data in ColS consist of:
ColS
(Row2) X
(Row3) Y
(Row4) Y
(Row5) Y
(Row6) X
In ColT, I am to apply data validation: i.e. If in concerned row of ColS is having "X" value, then I am to show an error message. Else, values from A2:A5 should be shown in the as list and these should be accepted in ColT.
View 11 Replies
View Related
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
Aug 4, 2014
Sheet A - The user input form. There is a column which needs data validation. I want it to look at Sheet B and ONLY accept values based on a column value from the other sheet
Sheet B- Contains the list we want the data validation from. One column is considered the lookup value, while the other is what we want the user to be selecting from.
Here is an example list:
Predecessor Entity Key---- Item ID
2659407------------------- SHELL - SOUTH TOWER
2659407------------------- MARKING / LABELING
2659407------------------- FOUNDATIONS & SUPPORTS
2659410------------------- UPPER HEAD - NORTH TOWER
2659410------------------- FOUNDATIONS & SUPPORTS
2659410------------------- LOWER HEAD - SOUTH TOWER
So, in Sheet 1, if one of the triggering values is 2659407, I want my data validation to ONLY Allow the first 3 values from the Item ID column.
The issue I am having is using a pretty messy formula to try and produce a dynamic range for the Data Validation to use.
Here is what works:
=INDIRECT(CONCATENATE(ADDRESS(MATCH(J4,A:A,0),2),":",ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(MATCH(J4,A:A,0),2)),COUNTIF(A:A,J4)-1,0)),2)))
where J4 is the lookup value/1st column depicted in our simple example
This is done on the SAME sheet. So I figure, just add "'Sheet Name'!" for the first argument in CONCATENATE. Unfortunately, this does not work. How to get this to work on an outside sheet?
Update:
Also tried:
=INDIRECT(CONCATENATE("'Profile Item Library'!",ADDRESS(MATCH(J4,'Profile Item Library'!A:A,0),2),":",ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(MATCH(J4,'Profile Item Library'!A:A,0),2)),COUNTIF('Profile Item Library'!A:A,J4)-1,0)),2)))
The error I get is that I cannot Reference other worksheets. However, If I type in ='Profile Item Library'!B1:B18 for the Data Validation argument, it works.
View 2 Replies
View Related
Dec 17, 2013
I need to setup a dropdown list (I already have the dropdown created) so that once a value in the list is used, it disappears from the list. Now for the monkey wrench...It need to completely re-populate once the list that is being generated from it is cleared.
I also need to copy a value from one sheet to another sheet, however, the location needs to be matched with a corresponding value from the first sheet. Hopefully it will be easier to understand within the workbook (I feel like I'm not explaining it well).
I have attached a sample copy of the workbook and what I am trying to accomplish.
Mens_Dorm_Sample.xlsm
View 2 Replies
View Related
Dec 2, 2006
I am looking for a solution for my problem: I work for a bus company, I need a spreadsheet where I will record the bus numbers into a sheet but I only want to be able to add the number once ie. error if duplicate bus entered. Also the numbers will change from time to time, old buses sold when new ones arrive. I have the following code that someone helped me with wich works fine provided you don't have to change the numbers.
=And(Or(And(E42>=121,E42<=125),And(E42>=149,E42<=156),And(E42>=320,E42<=363),And(E42>=700,E42<=799),And(E42>=800,E42<=991)), COUNTIF($B$3:$E$46,E42)<2)
View 2 Replies
View Related
Jan 24, 2008
I have a spreadsheet that I use to store my rock climbing records.
how to create Dependent Validation for this spreadsheet.
The spreadsheet has three worksheets:
1. Climbing Area
This is a unique list of various rock climbing areas.
Data in column A only
2. Climb Details
This is where all the details of the climb e.g. pitch lengths, grade, etc are recorded. Column called Climbing Area uses a named range validation from the Climbing Areas) sheet.
Climbing Area data validation in column A and Climb Name in column B, grade, pitch lengths in other columns.
3. Dates & Pitches
This is where I record the date I did the actual climb. Once again the Climbing Area uses data validation from the Climbing Area worksheet.
What I am trying to work out is how I can get the next column Climb Name to be dependent upon the previous column. For example if I select a climbing area of Mt Arapiles, I only want those climbs from the Climb Details worksheet that a recorded as being at Mt Arapiles.
Column A is date when climb done. Column B is data validation from Climbing Area. Column C is Climb Name (would like this dependent upon column B.
View 9 Replies
View Related
Apr 24, 2014
Is it possible to have a dependent validation drop box that under certain conditions automatically produces an actual value in the cell instead of a drop-down list to choose from? For example, if I choose “Holy Avenger” in the first drop box, and the value for that choice in the second drop box is always a “2”, can you get it to auto-populate in the cell occupied by that 2nd drop box (cell B5 in the attachment)? Or, are you restricted to using Lists only, meaning that you could create a List that only has the value “2” in it, and the user would be required to choose the “2” in the drop box?
View 2 Replies
View Related
Aug 7, 2014
I am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.
See attached.
The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.
Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!
View 4 Replies
View Related
Oct 1, 2009
I'm working on trying to maintain an inventory management sheet where i basically enter all the spare parts issued to vehicles. Now there are more than 700 parts dat form the components of a vehicle, so i decided to split this list into 12 spare groups(Gear, Engine, Body, Axle, etc etc..) and then based on wotever spare group being selected my sheet wud pull dat specific spare group list.
Since i've worked with indirect function before i thought this was a piece of cake!
However when i actually sat working on getting my sheet together i realised its not gonna be dat easy! Basically my spare group lists are a work in progress and so new items would/may be added to these lists as we start working on this sheet. So i decided to make them dynamic by using the offset function. But list validation fails when i use Indirect function on dynamic ranges!
View 2 Replies
View Related
Dec 2, 2013
Trying to set up a dynamic data validation, where each of the subsequent data validation boxes change based on the selection in the first data validation box (while maintaining 4 parameters within a cell, except for the last one).
I have used the IF-statements to determine the "Week's SUM" in H5.
What I'd like to do next is to determine a formula in the "Week's SUM" cells (H6,H7,H8) based on the choices in data validation boxes "Week #" (G6,G7,G8) with the following rules in place:
1. If I made a choice in G5 to be "1" which caused data validation in cell G6 to update, then in G6 I choose "2", the output in H6 should be just week 2's value of 20 (not the sum of week 1 and 2).
2. However, if I choose "3" in G6 (after I chose "1" in G5, as above) then I'd like the output in H6 to be the SUM of week's 2 AND 3, if that makes sense and so on. (e.g. If I chose "5" in G6, then output in H6 should be the SUM of week's 2 AND 3 AND 4 AND 5).
Same rules apply for H7 and H8. I have a feeling this may involve MATCH function and INDIRECT or SUMIFS but how to approach this.
I've attached a file : Dynamic DV and Dynamic SUMS.xlsx
View 3 Replies
View Related
Jun 23, 2014
I'm using Data Validation for various fields in a form and I'm trying to make it user friendly by adding Error Alert conditions and text explaining why the error has occurred. I also want the form to be translatable into other languages.
For some VBA message boxes that get displayed I can put the English text in some hidden cells and that can be translated easily by someone without access to the code. But I cannot figure out how to make the Data Validation Error Alert messages point to a cell so that they are also translatable without forcing the translator to open each Data Validation dialog.
View 1 Replies
View Related
Jul 21, 2007
I am looking for a way to define a 'Data Validated' Cell to hold a List of Dynamic Sequential Numbers - from 1 to the Value of cell A4. For example:
If cell A4 has a value of 5 the 'Data Validated' List will be 1,2,3,4,5
I have tried to 'name' the Array-Formula {=ROW(INDIRECT("1:"&A4))} and put the 'name' as the List source but without success.
View 8 Replies
View Related
Sep 25, 2009
I can use a dynamic named range and a direct reference to the name and the DV works.
I can use a standard named range (not dynamic) and an INDIRECT() reference to a cell with the name of the range in it and the DV works.
But if I try to use a dynamic named range and an indirect reference to a cell with the name of the range in it, the DV fails.
Any workarounds out there? This project includes a LOT of DV lists, getting them to self-maintain is important if I can do it.
View 12 Replies
View Related
Jul 8, 2013
I have attached a sample data sheet which i am working on . ITs a comparitive matrix trable with Input validation between 0&2 . I also want the table to be dynamic .If i want to increase or reduce no. of rows and clumns i should do it some how . More details are mentioned in the attached file .
View 1 Replies
View Related
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
Mar 21, 2014
I am trying to have the user click on a drop down list that shows several scenarios and return a score based on the scenarios. I created a list on a separate worksheet. The list contains the following:
Comprehensive list of policies 100%
General policies with no details 75%
Purchased policy templates 50%
No policies 0%
I would like the user to see this list including the corresponding scores,but when they select one, I would like the percentage to be returned in the cell.
I tried using Data Validation with VLOOKUP and named my list; however, it returns the text in the first column instead of the percentage.
View 2 Replies
View Related
Jun 17, 2014
I have a list that has blank rows mixed in. I'd like to return a list without those blank rows to be used as a dropdown menu for a budgeting tool.
I tried using the offset formula from the link below but this just cut off my list after the first blank rows showed up.
My current list looks something like this:
Code
5000-Salary
5000-01 - Hourly
View 3 Replies
View Related
Jul 17, 2009
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D
Alabama ABC Company Active Expires December 2009
Alabama 123 Company Expired Expired April 2008
Alabama XYZ Company Active Expires August 2009
Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
View 9 Replies
View Related
Dec 11, 2013
If I have a table as noted below with the following assumptions:
- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.
A
B
C
1
Item
Calories
Include
[Code]...
How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?
I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx
View 1 Replies
View Related
Mar 19, 2014
I want a to create a data validation on a Date column that if user enter the data it must be in Date format only otherwise not acceptable.
Like
01/02/2014
same as it is without using a macro.
View 1 Replies
View Related
Jul 5, 2007
how to limit the data a user can enter into any cell in an entire column to just spaces and letters a-z.
View 9 Replies
View Related
Feb 22, 2007
Let's say I have data validation on column A, which only allows 2 values, "Number" and "Letter". Easy enough. If the value "Number" is selected in A1, when the user moves to B1, I want a validation list of 1,2 and 3, when they move to C1, I want a list of 4, 5 and 6 and when they move to D1, a list of 7, 8 and 9. However, if "Letter" is selected in A1, when the user moves to B1, I want a list of R, S and T, when they move to C1, U, V and W, and when they move to D1, a list of X, Y and Z. I tried using dependant validation, but can only get all three columns (B, C and D) to either be 1, 2 and 3, or R, S and T. The validation lists would be existing named ranges on a separate sheet.
View 2 Replies
View Related
Oct 6, 2013
I have two columns of data and what I would like to do is have excel look over one column ( I5-I379) and for each cell that has say 30 in it I want it to return the value from a corresponding column (G5-G379), to form a separate column.......is this possible?
View 9 Replies
View Related
Aug 4, 2014
I'm using a table with dynamic data to populate a column/line chart.
The data is based on monthly targets achieved and forecasted. With each month, the information will update. The graph needs to have only the current month and future months to be displayed and I don't want to include the previous months information in the chart. The information in the chart automatically updates and loses the previous month's data. Unfortunately, the graph plots the blank data and has a blank entry on the graph. This means that the information I need starts in the middle of the chart and has a line that shoots up from 0 to the current month's value.
Is there any way that I can omit the month altogether?
The data source I am using is below:
MonthTarget for month* (examples)
October 100,123,669
November 125,154,586
December 150,185,503
January 175,216,420
February 200,247,337
March 225,278,254
April 250,309,171
May 275,340,089
June 300,371,006
As you can see, July August and September are not included. I want to be able to start the graph from October in the above example. In the following month, I want to use the same data but October Figures will not be required. How can I achieve this?
View 9 Replies
View Related
Jan 15, 2014
I have a two way data table for which the two variables are changed frequently. In the data table, for ease of reference, i usually highlight the corresponding row and column of the two most current variables the intersection of which gives you the current output. But these highlights need to eb changed each time the variables are changed 9the table is copied onto another document for presentation hence the need to keep changing the highlights).
I was thinking of configuring the highlighting process to happen dynamically, i.e. the row and column highlights change dynamically when the variables are changed.
View 7 Replies
View Related
Dec 20, 2012
I have a problem with this workbook, i know how to do it by nesting if functions but i want a neater way to do this. I have tried if(index(match)) type formula and haven't got it to work.
View 2 Replies
View Related
Jun 5, 2009
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
View 4 Replies
View Related
Aug 19, 2009
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies
View Related
Jan 30, 2007
I have a sheet (database) which has dymamic validation lists based on a another sheet called "look ups". The problem I have is When I choose any of the validation drop downs there are two blank entries at the bottom and the cursor is on the first one of these. I would like the cursor to be on the first entry in the validation list
View 7 Replies
View Related
