Data Validation - Cell Reference To Named Range

Nov 7, 2012

If I have two cells and a named range mexico

A1, A2.

A1 contains the string: "mexico"
A2 is the cells that has required data validation (drop down list).

I want A2's validation reference to be dynamic, in the sense that I can it reference it to A1; converting the string "mexico" to a named range mexico

I tried inputting = INDIRECT(A1) into A2

But I lack clarity in my understanding of referencing.

What is the correct procedure here.

View 2 Replies


ADVERTISEMENT

Using A Named Range In Data Validation

Feb 11, 2010

Is there any way to use a Named Range in the "Source" field in a Data Validation? I have multiple sheets in a workbook that have the same layout, and I am using alot of different Data Validations. My problem is that whenever I need to change, or add a choice to one of these, I have to go to each sheet and change the Data Validations that are effected by that change. Is there a way to use a "Named Range" or something to put all of my acceptable choices, for that Data Validation, in one spot. And make any changes I had to that field.

View 2 Replies View Related

Data Validation From Two Different Named Range Alternatives

Jan 28, 2013

I have a cell for which I need the only options for data to be entered from a list in two different tabs from which I understand cant be done but is it possible to have two drop down options for the same cell?

The attached spreadsheet should explain this slightly better...

View 9 Replies View Related

Data Validation List With Criteria Using Named Range

Oct 30, 2012

I have been tasked with creating a data validation list. My data is contained on a separate sheet from my data validation list. I am using a named range to use the data for my data validation list. However, I am looking to add a criteria to my data validation list so that it only is populated with values based on another column on my data sheet.

For example, if I have a data sheet called "Employee" and I have two columns. Column A is a list of employee names and I have named a range in column A as "EmployeeName". Column B is used to define the category of employee. This value is a letter, for example, "A", "B", "C", or "D". This column does not have a named range as I don't know if I need one to get this to work.

I would like the data validation on one sheet to use a list of employees of a particular class from another sheet. Straight forward validation is easy using a validation list with a formula =EmployeeName. But how do I only list employees that have a category "A"? For some reason, I cannot get some of the formulas I have found while searching this message board to work.

View 7 Replies View Related

Data Validation Fails With Named Range Of Array Of Constants?

Oct 27, 2013

I've set up a Named Range that refers to an array of constants, e.g. MyRange ={1,2,3,4,5}. This appears to be fine since I can output the named range to a range of cells in a row (e.g. A1:E1) and see 1,2,3,4,5 (one value in each column).

I'd like to use MyRange as the source for a Data Validation list in a cell.

Normally data validation list sources must be vertical, so I've set MyRange =TRANSPOSE({1,2,3,4,5}). This appears to be fine since I can output the named range to a range of cells in a column (e.g. A1:A5) and see 1,2,3,4,5 (one value in each row).

When I set MyRange as the source for data validation however, it resolves to an error.

why this setup doesn't work?

Workbook detailing the problem attached. Attachment 273788 NamedRangeDataValidationArrayConstansts.xlsx

View 3 Replies View Related

Copy Data Over Based On Reference And Named Range?

Jul 22, 2014

I got a very technical sheet which needs your experience to work.

open the attached file below to see the workbook.

It has 3 different sheets. "RESULT", "INPUT" and "SWITCH"

On "RESULT" Cell (O11) is where the person would put a Reference.

This reference is then checked on the "INPUT" sheet column A.

From the first cell it appears in Column A, until the last cell it appears in Column A should be the Row Range (Blanks included).

But I want that range (Column A to G) to be copied to "Switch" sheet. Pasted in Cell A1 downwards.

In the "SWITCH" sheet I have already given all the GREEN Cell formated areas a name - INFO.

I want all the data in INFO to be inserted into "RESULT" sheet between row 18 and 19.

I have colour coded most of the areas for you.. And added a button from which the Macro / VBA should run when clicked.

View 1 Replies View Related

Passing A Value In A Cell To A Named Range Reference

Apr 8, 2008

I have a formula which is using named ranges-

=SUMPRODUCT(xxx-30,Ship_30)/SUM(Ship_30)

where Ship_30 name references the following-
='sheet 1'!$C$20:$C$38

The value of 20 is static, the value of 38 is dynamic. Each new time period adds another value (i.e. 39,40,41)

Having to edit each name range reference in the name manager is not acceptable since there is potentially over 30 to edit each month.

I would like to edit a cell which would have contained the value 38 (or C38) with a new value of 39 (or C39) so the range reference would be updated with the new value.

The goal would be to have a couple of rows with the following that could be edited with new values

View 9 Replies View Related

Convert Named Range To Cell Reference?

Sep 18, 2012

I'm working with a very complex financial workbook with over 3,000 named ranges and would like code to replace the named ranges with the cell references and display it in a message box so the user can easily trace the references without removing the named ranges from the formula. There is a lot of code with the named ranges, so actually converting them to cell references is out of the question.

I found this code, but obviously it physically replaces the range names in the cell. Hopefully it can be converted to display it in a text box.

Code:
Sub FixReferencesToCellNames()
Dim c As Range, n As Name
For Each c In Range("A1:IV65536").SpecialCells(xlCellTypeFormulas)

[Code]....

What I am looking for is that a user select a cell that they want to see the references, run the code to see the cell references for that particular cell in a message box.

View 5 Replies View Related

Reference Last Cell In Dynamic Named Range

Nov 14, 2006

In the attached spreadsheet I am trying to always reference the sum column from Qty in Det Est.

This is because the number of roads changes for each project.

I created a dynamic range name surfacing with the following formula:
=offset(qty!$a$1,0,0,counta(qty!$A$A), counta(qty!$1$1)).

My question is what formula do I use to reference the sum column for each row on the Det Est Sheet.

View 4 Replies View Related

Data Validation List :: Reference A Range Other Workbook

Nov 23, 2007

I'd like to create a list via Data validation-list. Anyone knows how to reference a range on another sheet of the workbook?

The range is =$Q$5:$Q$13

Is it possible to reference another sheet? If yes, how is is done?

View 9 Replies View Related

Using Cell Reference As List Title When Transposing Named Range

Jun 21, 2014

I am building an interactive sheet to profile a number of divisions based on a summary sheet of about 1200 rows and 90 columns of profiling criteria

I have attached an example but essentially:

1. I would like to be able to select from a list a value which would also be a named range title
2. Have the range information transposed into a row

What works:
1. I can transpose the range information when I use the following formula
I used OFFSET(Governance,COLUMN()-MIN(COLUMN(HGovernance)),0)

The problem:
I cant get it to use a cell reference (i.e. When I select Governance out of a Drop down list of Finance, HR, Governance etc). Having to type in "Governance" to bring back governance range information is fine, but not great when I am trying to profile the Finance or HR division as it then becomes too manual.

View 11 Replies View Related

Reference Data Validation Cell From Another Workbook

Sep 17, 2008

I'm trying to link a range of cells in one workbook to a range in another workbook, both in a network drive. Everything is working out well except for the cells that have a data validation list on them. the formula linking one cell is ='G:Destination To[Workbook2.xls]SheetX!B13 so I'm just trying to get the value in the cell. I don't need the validation list to transfer just the value of what was selected. If I go to that cell and type something in then it transfers over just fine. If the validation list was used in Workbook2 then I get a #N/A in Workbook1 that is linking to the Workbook2 cell.

How can I link the cell with a data validation list in it to a cell in another workbook so that I can get the data in the cell (whatever was selected from the validation list)?......

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

Validation List To Combo Box Without A Named Range

Dec 5, 2007

I'm creating a sheet that takes three Validation Lists into account. The first two lists are based on named regions. The third list uses the first two lists to narrow a search down so that the user can pick what they want from a smaller selection. I'm using the code from Contextures.com to allow the user to double click the validation list to pop up a Combo Box. This allows me to show more than 8 selections at a time and also gives a little freedom on font and font size.

http://www.contextures.com/xlDataVal11.html

Since the third list is not based on a named region but rather code to compare the two previous lists, when I double click the third validation list a blank combo box pops up. it possible to pop up what ever lists are in the validation boxes.

This is the code that I have in the third validation box which States "The Source Currently evaluates to an error" but works fine for some reason. =OFFSET(CategoryStart, MATCH(B4, CategoryColumn, 0) - 1, IF(A4 = "Description", 2, IF(A4 = "ExtDescription", 3, IF(A4 = "PartNumber",4))), COUNTIF(CategoryColumn,B4), 1)

I tried making a named region that was based on the code, so that the list could change as the user selected different options from the first two lists. This gave me the same problems as I had before.

View 2 Replies View Related

Using INDEX With Named Range Reference

Nov 14, 2008

I'm trying to use the INDEX function to return a value from a named range. But I'm not directly typing the named range into the INDEX argument. Instead, I'm grabbing the named range from a vlookup table (essentially, another named range). So, if "vh" is the name of the named range, I'm not using the formula:

View 14 Replies View Related

Validation List Only Partially Sees Named Range

Feb 25, 2009

I've got a named range which defined using a custom function. Lets call this named range [range1] which is defined by:

View 14 Replies View Related

Embed Named Range Into Table Reference

Sep 15, 2014

I have a named range called ReportMonth and I want to embed it into this Table reference to make the number of columns that are summed dynamic:

=SUM(tblFcastTemplate[@[Column1]:[Column28]])

I tried ="SUM(tblFcastTemplate[@[Column"&(ReportMonth+1)&"]:[Column28]])"

View 4 Replies View Related

Creating Column Reference From Named Range?

Apr 22, 2014

I am attempting to obtain the last non-empty row in a column of a spreadsheet by using the following formula

=SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A))))

This works fine.

However, I'd like to make it more dynamic and be able to obtain a usable column reference (i.e., the A:A portion of the formula) from a named range (single cell).

View 1 Replies View Related

Indirect Function To Reference A Named Range

Apr 30, 2009

How would I used the indirect function to reference a named range in the workbook in formula creation.

For instance, if in cell A1 I have entered the text "Sales" which is also a named range in the workbook. Then in cell A2 I tried entering the following formula to sum based on the entry in cell A1 using the indirect funtion. For instance:

=sum(indirect(A1))

In this instance I was hoping this formula would then sum the amounts in the "Sales" named range. And, if I changed the text in cell A1 to "Cost" for instance (another named range in the workbook), it would sum the amounts in the "Cost" named range. Allowing for a dynamic formula based on the entry in cell A1.

I'm using Excel 2007.

View 9 Replies View Related

Access/Reference Named Range In Another Workbook

Oct 5, 2006

I have two workbooks wkA and wkB. I have a named range in wkB called BName.. can someone tell me whether it is possible to set a range variable (say, raA) from wkA to this named range in wkB. I have tried something to the effect of set raA = wkB.range("BName") but this doesn't work.. I've also tried set raA = [wkB.name].range("BName") to no avail. I'm wondering whether named ranges can actually be read from other workbooks.

View 4 Replies View Related

Reference Non-Contiguous Named Range In Function

Feb 14, 2008

I have a split named range covering non-contiguous areas, ie:
Name: status_web
Range: =$F$14:$I$122,$F$700:$I$746,$F$798:$I$830,$F$905:$I$933

This appears to work fine when selecting the name however it returns a #VALUE! error when using it in a formula, eg: = COUNTIF(status_web, "*p*"). Is there any way to get this to work, or do I need a separate named range for each contiguous section? NOTE: Not sure whether this extra fact would through a spanner in the works, but I am referencing this named range from a separate workbook.

View 3 Replies View Related

Selecting Named Range From Dropdown Validation List To Use In Formula?

Jan 18, 2013

Basically on a summary page, on sheet 1 say I have a formula which picks up the unit cost (I have this as an array across 5 years)

What I then want to do is multiply this by a volume driver. I have a list of these drivers on a seperate page, so for convenience lets say DRIVER_01,DRIVER_02 etc. These are also 5 years arrays.

So what I want to do it be able to select which driver I want to use for volume on the summary, from a drop down box I have set up, and then the formula pick up that named range, and not just recognise it as a text cell.

I need the ability to change the driver in the future, hence the drop down box. I can also get the unit array part to work fine, I'm just struggling to get this driver array to work..............

View 2 Replies View Related

Reference Named Range By Position Macro Code

Mar 11, 2008

This seems really basic, but I can't seem to find it...

Using Visual Basic, if I have a named range, lets say like "NAMEDRANGE", how can I refer to cells in that range by their position in the range? For example, if I want to refer to the cell in the 2nd row and 3rd column of the range.

Also is their an easy way to refer to the first(top-left) cell in a named range?

View 3 Replies View Related

Circular Reference With Dynamic Named Range Used In Formula

Aug 13, 2008

I have a problem creating a circular reference with dynamic ranges. I have searched already but can't find the specifc problem. I have a spreadsheet where I use vba code to insert extra columns. Within the worksheet there are sum calculations at the end of each row. When I add a new column within the table I would like the sum calculation to expand to account for this extra column.

Now, I thought that dynamic ranges would be the way to go but the only way I can see to do it creates a circular reference as the CountA function trys to count the cell with the sum function in it.

View 2 Replies View Related

Data Validation With Two Named Ranges

May 8, 2007

I want to validate data in a cell, using a list of two named ranges, so that in dropdown box the items from first range and then the second one are shown.

View 7 Replies View Related

Determining If Cell Is Part Of Named Range And What That Named Range Is?

Aug 16, 2014

Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:

Code] .....

here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.

View 5 Replies View Related

Copy/Paste Cell To Named Range Named In Adjacent Cell

Sep 4, 2007

I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.

The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.

Sub PopulateWithImportData()
Dim counter As Integer
counter = Sheets("Imported Data").Range("Counter")

Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select
Range("a1").Select

i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1
Loop

View 8 Replies View Related

Dependent Data Validation With Dynamic Named Ranges?

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

Apply Data Validation Using Multiple Named Ranges

Oct 20, 2009

I am trying to apply data validation to a column of cells using named ranges. However, each row has a unique associated named range. For example:

A_______B
Birds____*
Dogs____*
Cats____*

I can easily apply data validation to these three rows separately using named ranges.
Ie three separate named ranges:
=Birds
=Dogs
=Cats

However, I need a way to quickly apply data validation to column B using different named ranges for each row because there are about 2,000 rows. Is there a way to reference text in the cells of column A that contains the name of the named range? Or maybe a bit of VB code that could do it quickly?

View 3 Replies View Related

Data Validation List With Multiple Named Ranges?

Mar 25, 2014

Can you create a Pull-down List that contains data from Multiple Ranges.

If I have a List of Names Running down column A (A2:A10) another List of Names Running Down B (B2:B25) and a Third down C (C1:C15) each of them named Ranges ("List1", "List2" & "List3"), can I create a Pull-down list in Cell A1 that would include the names from all three ranges?

View 4 Replies View Related







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