Free Microsoft Excel 2013 Quick Reference

Dynamic Data Validation List, Source on different sheet Excel 2003

I know both of these topics have been covered individually, and maybe it's just that I am using 2003, but I am unable to get a named range created using the Offset method to work when referenced in a different sheet.
I have checked the validity of my named range by doing a data validation drop down from the sheet ("Control") where it was created. As long as it is in the same sheet, it works fine.

I have tried many times, tweaking my method each time, but always end up with the Source currently evaluates to an error prompt and the list will not populate the selected cell.

Anyone know if this is possible in Excel 2003?


Post your answer or comment

comments powered by Disqus
Is it possible to use the NAMED Range for my data validation list box on a sheet that is not the sheet where the list box is displayed? I know I've done it that way on another project before (=MyList), but I'm getting mixed results on this one and I don't understand why. Any ideas? The list is expected to grow and will be too difficult to maintain on each sheet of the workbook that will be using it.

TGIF!! Cheers!
CC

Hi,

Currently I'm using a Data Validation list to display a list of values which are located on a hidden sheet. The problem with this option is that the dropdown arrow is only visible when people are located in the cell.

I already found that a Combo Box is an answer to this problem. The problem is tough, I can't find they way to link the Combo Box to my list.

Kind regards,

Tom

I have following data in my excel, attached:
Sheet "Seat_Pool":Columns:
Seat_No: Distinct entries, with no repeats
Group_Head: Head of the group to whom Seat belongs.
Status: Status of a seat VACANT or OCCUPIED.
In reality this list 350+ and always growing, Status changes all the time but seat_no & group_head mapping rarely changes.

In Sheet "Seat_Assignment":
What I want is that "Seat_No" Column (Column B), should have a Data Validation list dropdown based on Group_Head=Harry and Status=Vacant.

I believe my approach should be in following order:1. Auto-Copy or Auto-Generate seat_no of matching criteria (Group_Head=Harry and Status=Vacant), in some column. Lets Call it HARRY_VACANT_LIST
2. Sort HARRY_VACANT_LIST
3. Remove Duplicates
4. Give name definition to HARRY_VACANT_LIST, make this as dynamic so as to avoid blanks.
5. Use this new name definition in Data Validation for Seat_No
Limitations:1. Can't add new columns in any sheet. (Have seen earlier example on this forum at http://bit.ly/vZa6mI, didnt really like the idea of having a new column C called Validation_KEY)2. Can't change order of column in any sheet - I am already using VLOOKUP at many places
3. Will prefer a NON-MACRO approach.
Sample File Attached:
paragb_seat_sample.xls

I am having a hard time trying to figure how to get the second column (data validation formula) working.

I am attaching a file so you can see what i'm trying to do. The validation fields are working perfect on the first tab. However, when I try and recreate it on a new sheet, it generates an error.

I'm trying to put this formula in the validation list (source) field..

	VB:
	
=OFFSET(INDIRECT(SUBSTITUTE($A2," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($A2," ","")&"Col")),1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If anyone has a few moments to help me figure it out, it would be greatly appreciated.

DataValDynamic.xls

Attached is an example that I pulled from Contextures website (Grovier). There a re 3 sheets. The first sheet if the original file and all works as it should, In which the combobox attaches itself to the active cell containing data validation using one click. The lists for the data validation are contained on the same sheet.

Sheet Two: is the same thing modified such that the data validation lists (moved to sheet List) are using the named Ranges of the lists offsheet. Most of the functionality of the combobox no longer works. Namely the font change , number of items the list dispplays on click, autocomplete dont work any more.

any thoughts?

I'm trying to create the following functionality in reference to the following:

LongList ShortList dog chicken dog dog pony donkey pig horse donkey ostrich horse pig ostrich pony chicken chicken pony pony
1. ShortList is a sorted list of the unique elements in LongList. 2. ShortList also serves as data validation list, meaning user can add to LongList by selecting ShortList items from drop-down menu.
3. ShortList is dynamic, in the sense that if user adds new item to LongList, it shows up on ShortList, and is thus available from the drop-down menu.
4. User can add overwrite an item anywhere on LongList with a new item that is not on ShortList, and ShortList will update automatically.

I'm having difficulty with #4, which seems a kind of Catch-22 situation. Because LongList is using the Data Validation feature (to get the dropdown menu), it seems it's not possible for the user to be able to add an item to the middle of the list that is not on ShortList, i.e., the data validation list.

I understand that I could require the user to add only to the bottom of LongList (thus allowing the range of data validation to grow dynamically), but that's not what I want.

I've looked over the various range.validation properties hoping to be able to turn something off to allow user to enter a value in the middle of ShortList, but nothing jumps out at me.

Any ideas?

I have on a sheet:
4 data validation lists that contain the exact same values.

would like to have:
One validation list box that is dependant on all 4 vd lists.

I posted a thread on dv and came to the conclusion that it is not possible to use INDIRECT() as it only allows for one cell reference and not four.

Is there another way to do this?

Glove Man, I tried your suggestion but couldn't get it work. Any other suggestions?

Thanks

Debs

Is it possible to use a 'Dynamic Named Range' in the Data Validation
list ?
When I tried, the Data Validation List did NOT show any entries... ?!
(it remained empty, enven though the Dynamic List was working right!

Details on 'Dynamic Named Ranges' can be found he
http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm

If this is not possible, is there any ways/workarounds to create a Data
Validation List, based on a Dynamic Named Range ?

TIA !

R.

Hi,

I have placed about 21 comboboxes from the "Control Toolbox" on a
standard worksheet (let's call it the "Selection Sheet"). It shows a
3-column list from which the user can select an entry and the bound
column is stored in the cell underneath the combobox.

The workbook has multiple sheets. Sometimes, when working on the other
sheets, for no apparent reason (I can't reproduce it), contents &
formatting of the filled comboboxes from the Selection Sheet are copied
onto the sheet I am working on. This is highly frustrating and surely
not by design. I have seen it happen on several computers, so it is not
a specific problem for my PC configuration.

Has any of you some idea why Combobox data is accidentally copied on
different sheets?

Your help is appreciated!

Bennie Douma
Excel 2002

*** Sent via Developersdex http://www.developersdex.com ***

Hi,
been breaking my head over dynamic ranges and data lists. trouble is that i am not able to remove blanks inspite of using OFFSET and COUNTA can some help me out here. I have a coloumn which is populated when users click on checkboxes, these populated rows are used in a data validation list. trouble is i either get all data with blanks or none.
appreciate ur responses.

cheers
reghu

(Mac Excel 2008) I want to populate a data validation list depending on the value in cell G11. G11 can have 1 of (for this example) 6 values: "ABC", "DEF", "GHI", etc. (In practice there are 35 values.) The six cell ranges containing the list contents in each case are at various locations within column CB.

I'm using nested formulae, each of which is a nested IF statement. The top level IF statement is the Source (Data -> Validation... -> Source text field) which first references the named formulae and then, on failure, checks its own false clause (ABC and DEF here). If nothing matches then cell CC1 is used.

For example:

This is the source statement for the drop-down cell:
=IF(NESTED_IF_0,NESTED_IF_0,IF(G11="ABC",CB1:CB10,IF(G11="DEF",CB11:CB20,CC1:CC1)))

These are the named formulae:
NESTED_IF_0:
=IF(NESTED_IF_1,NESTED_IF_1,IF(G11="GHI",CB21:CB30,IF(G11="JKL",CB31:CB40,FALSE)))

NESTED_IF_1:
=IF(G11="MNO",CB41:CB:50,IF(G11="PQR",CB51:CB60,FALSE))

(Actually I have 6 formulae to an IF depth of 6 for 35 different values in G11.)

The problem is: NESTED_IF_0 always returns false, as though it can't be seen, regardless of what's in G11. It's definitely defined and the syntax is correct. If I plug any one of these formulae directly into a cell it seems to work as it should, but within the data validation list source they don't seem to be seen.

Thanks for reading this far, and thanks in advance for any insight you can provide as to why it might not be seeing the named formula.

- Dan

Is it possible to use a 'Dynamic Named Range' in the Data Validation
list ?
When I tried, the Data Validation List did NOT show any entries... ?!
(it remained empty, enven though the Dynamic List was working right!

Details on 'Dynamic Named Ranges' can be found here:
http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm

If this is not possible, is there any ways/workarounds to create a Data
Validation List, based on a Dynamic Named Range ?

TIA !

R.

hi folks,

i am probably pushing the envelope a little too far here. i have a situation as follows:

clmA clmB
a 1
b 3
b 2
b 4
c 4
c 2
d 1

for certain reasons (i can go into details if asked), i would like to do the following.

the above values in clmA are available as a data validation list elsewhere within the same spreadsheet (different tab, though). when user select a value, say 'c', from the picklist in a particular cell, in the adjacent cell i would like the data validation list to present the two values 4 and 2 (VLOOKUP returns only the first value it finds in the array, 4). i tried to use INDEX, but got stumped because i could not figure out how to use CTRL+SHFT+ENTER within the Data Validation > List > Source.

much obliged if i can get some help on this.

kind regards.

Hi. I'm trying to use the Data Validation List function to access a named range. When the range resides within the workbook, this works fine. This also continues to work if I move the range into another workbook using the "Move selected sheets" function. My problem comes when I try to reference the named range from a new workbook - the error "You may not use references to other worksheets or workbooks for Data Validation criteria".

Ideally, I want to construct a Setup workbook with common data lists and ranges defined. I then want to be able to access this Setup workbook from a number of other workbooks where local data will be held since they will also utilise the common data (from the Setup workbook) in some of their tables.

Is there a way to achieve these 'global' lists? Any advice appreciated. Thx

This seems like a difficult one, way above me.

What I want is a data validation list in a cell on one worksheet. The list should include all the unique values from a range in another worksheet (There will be more that one similar value in the range and but I don't want them to repeat in my list).

Is this possible!!??

Someone helped me come up with the macro below to allow me to print 100 timesheets from a Validation list by using a macro. This macro works well but I forgot to mention that the source of the validation list is on another sheet and I do not know how to get the code to reference the information on the other sheet.

Can someone help me make this work?

Sub PrintAll()
For Each cell In Range("V47:V132")
Range("F2") = cell
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next cell
End Sub

Hi,

Is there any way of populating the data validation list of a cell
based on the selection in another list driven data validated cell?

For example:

Using the table below,

Col_A Col_B
1 Football
1 Running
2 Baseball
2 Swimming

I want Cell A2 to have the list options 1 and 2.
Then if the user selects 1 in Cell A2, I want the user to have the
options Football and Running in Cell B2.

Regards,
Ciarán

Hi, I wonder if anybody can help
In cell A1 there are multiple options eg London, Birmingham, Manchester, I
twould like a data validation list to appear in cell B! based on the value in
cell B1 so if it is London I have a drop down list of Waterloo, Victoria,
Kings Cross etc, but if it is Birmingham then I have a drop down list of New
Street, International Airport etc

Hope this make sense.

Thanks in advance for any help

Hi - Can anyone identify & rectify the error
I gave this formula at Data->validation->List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data->validation->list range name "Biz")
c4 - is having a list (thro' data->validation->,list range name "Region")
D5- is having a list (thro' data->validation->list range = formula as above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing 3 units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT shows 7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.

Thanks in advance, some one help me.

Basically, I want to populate a Data Validation based on values in another cell on another worksheet--but, I want to populate it with the values stored in the cell right next to the cell.

http://i32.photobucket.com/albums/d3...n/untitled.jpg

would be an example.

What I want my Data Validation List to do is to look at column B and wherever it sees a certain color--for example, red, I want it to put the corresponding value in column A in the list.

Any ideas? I'm fairly new to excel, but not that new. I was thinking of using a VLOOKUP function but had no idea how to go about it in a list.

I have data validation list entered somewhere in the last column. Now, when I print pdf the data validation list appears on the pdf in the last page. Is there a way to avoid this list in pdf? Or I have to create a new sheet in the workbook to enter the list?

Thanks

Hi
I am familiar with the normal "data validation - list" I have several named lists. I want the correct list to appear as drop down in the cell D3 depending of the value of cell C3

Example if you choose: drink in cell D3 you get the drop down list ;water;coffee....
But if you choose meal in D3 you get burger, hot-dog....

Have a long list and want to restrict the values depending on the first choice.

Hi - Can anyone identify & rectify the error
I gave this formula at Data->validation->List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data->validation->list range name "Biz")
c4 - is having a list (thro' data->validation->,list range name "Region")
D5- is having a list (thro' data->validation->list range = formula as above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing 3 units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT shows 7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.

Thanks in advance, some one help me.

Is it possible to create a Dynamic Data Validation list based on a formula that looks up information based on another value?

Example:

Lookup
1 Smith
2 Jones

Data
1 Smith Dan
1 Smith Jane
1 Smith Bob
2 Jones Larry
2 Jones Frank

User looks at the data validation cell list and sees the 3 Smiths or the 2 Jones depending on the lookup value.

So when I paste a new list into my spreadsheet I have a specific cell with the Data Validation looking at a formula that will populate based on the lookup.

I would like to avoid use VBA if possible.

TIA


No luck finding an answer? You could always try Google.