Dynamic Data Validation list without dispersed blanks

[julhs]

I am wondering if I have completely misunderstood the use of “Offset,Count” for my purposes but should have gone with using ActiveX combobox instead?

I have 2 validation lists on my source sheet, one for “Supplier” ColB (which has loads of dispersed blank rows) and the other in ColC for “Chemical Name”.
My ultimate goal will be to make the “Chemical Name” list dependent on selection made in the “Supplier” list, but at the moment that is not my issue.

I’m using this in the DV list source in ColB (Supplier)

=OFFSET($B$6,0,0,COUNTA($B:$B),1)

and this as the source in ColC (Chemical Name)

=OFFSET($C$6,0,0,COUNTA($C:$C),1)

Firstly, neither of the 2 validation lists are complete (seems they are getting chopped off part way down)
Secondly (and only referring to) “Supplier” list, it still contains all the blank rows

 

[julhs]

Green filled cells are where Validation drop downs are
The Red filled cells in ColB & C are where each Validation List is being cut off

aa	asa
asa	asa
Supplier	Chemical Name
Supplier	Chemical Name
HUTCHINGSONS	Envision
	Envision
	Stacto
	Glyco phosp + wetter
	Grazon 90
	Pastor
	New Shot
	Headland Relay
	Mircam Plus
	Cassoron G
	Dictabenal(Cassaron equiv)
	Panacide M
	E 11 (wetting agent)
	Iron
	Round up Pro Active
	Round up Pro Active(Gold)
	Pistol (residual)
	Espom Salt
	Depitox
	Finalsan Plus (Total)
	Activator 90
	Mentor (RoundUp equiv)
Sherriff Amenity	Katoun Gold (Total)
	Praxys
	Flatline
	Pistol (residual)..
	Katana (residual)
	Valdor Flex (residual)
	Activator 90..
	Ferromex
	Grazon 90 Pro..
Glenside
(Richard Salmon)	Sea Green Liqiud Iron
BIOtechnica
(Croptec Ltd -	Algaflex
Martin Noble)
ViveGreen	MossOff
ALS
(Amenity Land	Pistol (residual)…
Solutions)	Round up Pro Active..
	Soluble Iron
Avoncrop	Blazon ( Blue die)
	Vitax Tank Cleaner
RBT 24/7	Moss & Algae killer
Oakland -	Intek Opal Green
Amenity
T H Whites	Rose Clear
	Armallatox
	Grazon 90,,
Patio Majic	Patio Majic
Wyevale	Dithane ( pack of 6)
	Rose Clear (£ 8.96 for 150ml)
Tack shop	Epsom Salts
Rigby Taylor	Mascot Soluable Iron

 

[awoohaw]

My intial thought on this is that COUNTA counts non blank cells. So if you have 26 non blank cells you only get the first 26 items returned with the OFFSET function.

 

[julhs]

Well I sort of follow that to a point because in Col B there are 22 blank rows between first & second entry in the column with 2 blank rows below 2nd entry, but not 26 in total.
But using an adjusted Offset/Count formula for Col C validation list, there are only 12 blank rows before the list is cut off?

 

[awoohaw]

the 3rd argument of OFFSET is the height amount. If you want to find the LAST cell in the column that has a value in it, COUNTA will not work because there are only 22 (or 24, or 26... it makes no difference) populated cells.

You need to build a "helper" range that has no blanks. (Office 365 Insiders (Current Version) actually filters duplicates out of DV ranges so youi don't need helper columns.)

This is how I would construct the DV list in a helper column:

(additionally, if you're going to have lots of complex formulas, using a column reference like $B:$B will slow your worksheet down, epecially if OFFSET is a volatile function).

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1		aa	asa
2		asa	asa
3		Supplier	Chemical Name
4		Supplier	Chemical Name
5
6		HUTCHINGSONS	Envision		HUTCHINGSONS
7			Envision		Sherriff Amenity
8			Stacto		Glenside
9			Glyco phosp + wetter		(Richard Salmon)
10			Grazon 90		BIOtechnica
11			Pastor		(Croptec Ltd -
12			New Shot		Martin Noble)
13			Headland Relay		ViveGreen
14			Mircam Plus		ALS
15			Cassoron G		(Amenity Land
16			Dictabenal(Cassaron equiv)		Solutions)
17			Panacide M		Avoncrop
18			E 11 (wetting agent)		RBT 24/7
19			Iron		Oakland -
20			Round up Pro Active		Amenity
21			Round up Pro Active(Gold)		T H Whites
22			Pistol (residual)		Patio Majic
23			Espom Salt		Wyevale
24			Depitox		Tack shop
25			Finalsan Plus (Total)
26			Activator 90
27			Mentor (RoundUp equiv)
28
29		Sherriff Amenity	Katoun Gold (Total)
30			Praxys
31			Flatline
32			Pistol (residual)..
33			Katana (residual)
34			Valdor Flex (residual)
35			Activator 90..
36			Ferromex
37			Grazon 90 Pro..
38
39		Glenside
40		(Richard Salmon)	Sea Green Liqiud Iron
41
42		BIOtechnica
43		(Croptec Ltd -	Algaflex
44		Martin Noble)
45
46		ViveGreen	MossOff
47
48		ALS
49		(Amenity Land	Pistol (residual)…
50		Solutions)	Round up Pro Active..
51			Soluble Iron
52
53		Avoncrop	Blazon ( Blue die)
54			Vitax Tank Cleaner
55
56		RBT 24/7	Moss & Algae killer
57
58		Oakland -	Intek Opal Green
59		Amenity
60
61		T H Whites	Rose Clear
62			Armallatox
63			Grazon 90,,
64
65		Patio Majic	Patio Majic
66
67		Wyevale	Dithane ( pack of 6)
68			Rose Clear (£ 8.96 for 150ml)
69
70		Tack shop	Epsom Salts
71
72		Rigby Taylor	Mascot Soluable Iron
73
Sheet5

Cell Formulas
Range	Formula
E6:E43	E6	=IF(ROWS($B$6:$B6)>COUNTA($B$1:$B200)-5,"",INDEX($B$1:$B200,1/AGGREGATE(14,,IFERROR(1/((LEN($B$6:$B$200)>0)*(ROW($B$6:$B$200))),0),ROW(E6)-5)))

 

[julhs]

Thank you.
Yet again I find that IF I updated my Excel 2010 I would find life a lot easier!!!
What you have said just shows again my lack of in-depth knowledge.

I had inserted a manually populated column of suppliers (so no blanks) to reference but just thought I was completely missing something basic regarding a straight Offset/CountA VD list.
What you have given me (un-test in my real time) would do away with that manually populated column of suppliers. Adapted; the same goes for “Chemical Name’s” in col:C, but as you can see there is only a one line blank between what product one supplier provides and the start of what the next supplier provides.
Any blanks that exist in col:C will not be relevant when I get the right way to make Col C VDlist dependent on col:B VDlist selection.

I know this is NOT part of the initial question!!
But just as a closing question (and ONLY meant as a tip); do you think that using individually named ranges of chemicals that each supplier provides the way to go when creating the dependent drop down list for col:C (ie col:B VB list selection should only display relevant products in col:C Vb list)?
Or should I be looking at an other way?

 

[awoohaw]

I don't really understand your question. My initial thought is that you need to have a way to identify all the components in C that match with B. Since B has spaces, how are you associating the C records with their "parent" records? My suggestion would be to fill the blanks in Column B with repeated values.

But I think you want a data validation list that is dynamicly filtered and based on adjacent cells, is that correct?
There are a few youtube channels that have all posted videos on Dynamic Validation. But, I do not know how effective they are in older versions of Excel. Although 2010 has much that is transferable between users these days (365 and 2021 may not be very ubiquitous).

ExcelIsFun, Mr Excel, Leila Gharani, and MyOnlineTrainingHub all have great video tutorials.

Just search "MS-Excel, Dynamic Data Valadation"

Here is one video that probably works with 2010.

 

[julhs]

Sorry if my supplementary question was not clear.
But with what you have provided me I will attempt to link Col B selections to Named ranges for associated “Chemical Names” in Col C.
If I’ts any help, for instance
Hutchingsons supply the chemicals listed in B6:B27
Sherriff Amenity B29:B37

Many thanks for your help, you have enabled me to move forward from the log jam I was at.