?
Solved

Data Validate Source

Posted on 2012-08-22
9
Medium Priority
?
386 Views
Last Modified: 2012-08-23
Hello,

A data validation that happens off the list of data in sheet “sourceMySource”.
The sheet SourceMySource will have data changing but I need the data validation to pick up the driver options from the [ALL ] range and the indirects of the [All] values correctly?
I used these:
=OFFSET(INDIRECT(C4),0,0,COUNTA(INDIRECT(ADDRESS(1,COLUMN(INDIRECT(C4)))&":"&ADDRESS(65536,COLUMN(INDIRECT(J1))))),1)
 =OFFSET(INDIRECT(VLOOKUP(C4,LookupList,2,0)),1,0,COUNTA(INDIRECT(VLOOKUP(C4,LookupList,2,0)))-1,1)

But none are helping…
How to do that?
EEQs22.xlsx
0
Comment
Question by:Rayne
  • 6
  • 2
9 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 38322565
Why don't you use just indirect(c4), but define myRng1 through 4 dynamically as

=OFFSET(SourceMySource!$C$2,1,0,COUNTA(SourceMySource!$C:$C)-1,1)
(example for myRng1)

Thomas
0
 

Author Comment

by:Rayne
ID: 38322568
The source for the data validation will be growing or changing so hose needs to be dynamic
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38322591
Which is what I suggested, see attached file.

Thomas
Copy-of-EEQs22-1.xlsx
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Rayne
ID: 38322739
Thomas,
Thank you for your suggestion….In your file, when I click on select choices (C5), it’s not showing the options for the dropdown….
0
 

Author Comment

by:Rayne
ID: 38322747
0
 

Author Comment

by:Rayne
ID: 38322757
I tried to reassign the C5 data validation as indirect (C4) >> then the data validation says—this evaluates to an error, do you want to continue?
0
 

Author Comment

by:Rayne
ID: 38322783
The moment I changed the source range are changed from dynamic to static, the data validation works
then how can data validation be made from dynamic source??
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 38323891
Try this which uses EVALUATE instead of indirect. Note it has to be saved as a macro-enabled format.
Copy-of-EEQs22-1.xlsm
0
 

Author Comment

by:Rayne
ID: 38325459
Hello Royra,
Awesome…You rock. In my previous interactions as well, with you over EE, I have always seen that you have tried to solve the problem in the most  quick and efficient way possible. Kudos to you.
Rayne
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
I came across an unsolved Outlook issue and here is my solution.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question