Hi Guys,
i have tried to use:
where ID is column name and t are tables.
Please Login or Register to view this content.
Also tried to do:
but still not working.Please Login or Register to view this content.
How can i solve the problem?
It is possible to union columns from different tables into validation list?
Best,
Jacek
Just trying to understand exactly what you are trying to do.
If table1 has 15 rows and table2 has 10 rows, does data validation list contain 25 items?
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
It would be wise idea to post sample file (just few rows each table)
Best Regards,
Kaper
Exactly! kev_If table1 has 15 rows and table2 has 10 rows, does data validation list contain 25 items?
One way
Use VBA to combine the 2 columns into one list
- test method in attached workbook
- open workbook
- enable macros
- select cell J1
- combined list appears in column G (named range "ValidRange")
- data validation in J1 includes combined list based on "ValidRange"
- add some names to table A and\or Table B
- select cell J1
- combined list is updated correctly
- data validation in J1 includes combined list based on "ValidRange"
Please Login or Register to view this content.
thank you Kev_,
but it is not quite good idea i think for me (i should set up it once only).
There is no way to do it using simple validation list?
Jacek
So you want join two columns (lists) in-the-fly inside DV without creating single column in the sheet?
Yes sandy666, exactly.
Eventually how to merge two columns from two different worksheets into one column?
Jacek
you can use PowerQuery and append them then load to the sheet and name it (without header) then use in DV
Two lists in DV you can try with IF, means via third list (two headers only) if namesa use 1st list, if namesb use 2nd list but both should contain these headers inside the two lists (twisted, I know)
everytime you should have step back to the basic list (two headers)
Hi sandy !
thank you,
i can not use powerQuery for now - because my clients do not have it.
It is confusing for me- can you please help givining an exaple?Two lists in DV you can try with IF, means via third list (two headers only) if namesa use 1st list, if namesb use 2nd list but both should contain these headers inside the two lists (twisted, I know )
Best,
Jacek
Maybe it is possible to input 2 columns into one array ?
Jacek
Data validation (DV) does not allow lists to be joined
Merge your lists before using the range in DV
for example see attached workbook
By formula in M2 copied down until you get a blank cell
- but you need to keep extending this manually as your tables grow (or copy down further to allow for extra items)
Formula:
Please Login or Register to view this content.
Create named range "Unique" with RefersTo formula
Formula:Please Login or Register to view this content.
Data validation is in cell N1, with formula
Formula:Please Login or Register to view this content.
Last edited by kev_; 02-09-2018 at 03:36 AM.
I have read your all your comments
The simplest route for you (at the moment) would be to use VBA to update a single range whenever either table is updated
(which is a minor modification to code in post#5)
Last edited by kev_; 02-09-2018 at 03:38 AM.
Re: creating validation list from 2 different dynamic tables
you can play two columns into one
=IFERROR(INDEX($A$2:$A$6, ROWS(B1:$B$1)), IFERROR(INDEX($B$2:$B$8, ROWS(B1:$B$1)-ROWS($A$2:$A$6)), "")) but you need to find something better, sorry
EDIT: refreshed - I think kev's formula to join columns is better
[EDIT2:] you can create common list from many different sub-list from different sheets: simply PivotTable
and file is with DV (created on-the-fly
Last edited by sandy666; 02-09-2018 at 06:01 AM. Reason: see edit
Hi Guys,
thank you!
kev_
i have formula:
Przechwytywanie.PNG
I do not exactly know how lookup function is working here.
Why did you use value "2"? I used value "1" and nothing change.
Jacek
Re: creating validation list from 2 different dynamic tables
as I said above you can join many columns into one, single list then create name which will be used in DV
Hope, client is able to use PivotTable
Thank you Sandy!
How did you do that?
Created pivot from multible tables?
Jacek
old, known from Ex97 shortcut: Alt + D + P
but one thing: this is not dynamic
if you change any list you'll need recreate whole PT
Thank you
I added 2 ranges like here:
2ranges.PNG
But i have an error:
onerow.PNG
I should give here 2 rows table not only one...
Can you please give me a tip?
Jacek
You are correctWhy did you use value "2"? I used value "1" and nothing change.
- I think all numbers > 0 give the same result
Hm, should I say: MAGIC ?Originally Posted by jaryszek
select range: A1:B5 not A1:A5 and so on
on the end you'll use only row field in row area, nothing more
Hi Guys,
thank you!
what does value means here? What does "2", "1" means here?Please Login or Register to view this content.
thank you Sandy666 i understand it now
Jacek
You are welcome,
so
If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.
Hi Sandy,
i have to understand still how vlookup working in Kev_ formula, and i will close the topic.
Best wishes,
Jacek
Explanation of formula
to return array of zero valuesPlease Login or Register to view this content.
Formula:Please Login or Register to view this content.
to replace zero values with TRUE
Formula:Please Login or Register to view this content.
convert TRUE to 1
Formula:Please Login or Register to view this content.
To find the last value in array with LOOKUP use a value LARGER than any value in the array
- LOOKUP does approximate match
(similar to VLOOKUP with 4th argument set to TRUE : VLOOKUP(x,array,column,TRUE))
- the largest value in the array is 1
use LOOKUP to find the value 2 with
Formula:Please Login or Register to view this content.
- LOOKUP cannot find 2
- so looks for first value smaller than 2 starting from the last value in the array
- which is why the name order is reversed by the formula
used in this way
LOOKUP(value, array1, array2)
LOOKUP finds the value in array1 and returns the corresponding value in array2
Last edited by kev_; 02-11-2018 at 05:18 AM.
Wow !
Thank you kev_!
Fully understand it now!
MY code to update one column dynamic table (merge data from different dynamic tables).
However, I think that VBA is the best option here.
Code is very fast - better solution the copying ranges.Please Login or Register to view this content.
Jacek
Here is another way too without using any additional arrays, dictionaries, copying or pasting
Test in attached file
- old values in sheet "Combo" cleared
- one column each from 3 tables is consolidated, de-duped and sorted
- updated list appears in sheet combo
- data validation in cell A1 Source is dynamic named range "ComboNames"
- ComboNames formula is =OFFSET(Combo!$A$2,0,0,COUNTA(Combo!$A:$A)-1,1)
Please Login or Register to view this content.
Thank you kev_
Dictionary and array are the fastest way of coding in VBA, but your method is also good!
Jacek
Your codes reads everything into an array/dictionary and then writes everything back to the workbook (2 steps)
Code in post#28 directly attributes values to the workbook (1 step)
You code contains loops which slow things down
Code in post#28 contain no loops
Which code is faster?Have you timed both solutions?
Could you time them with your live data and let me know?
Last edited by kev_; 02-11-2018 at 06:10 PM.
Hi kev_
I do not have time to test it but i suppose that your code will be quicker here
It is very nice approach!
I wrote this wrongly: looping throuh arrays and dictionary is faster than looping in range cells
Thank you once again!
Jacek
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks