Disable sorting

[PetarTen]

Hi Folks,
I have a File here Filebin | 2jezz8wdpo1sm01j that needs a bit of a tweak.
It needs to have Sorting disabled and was wondering "How to do it"?

It's a xlsm file and I think I found the right Code here:
Redirecting
```vba
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect userInterfaceOnly:=True, AllowFiltering:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=False
```
Unfortunately, I don't know where (and how) to add it.
Any help is much appreciated.
PS The problem is that, when the user chooses the 10 Cylinder (from A2 cell and then B2) there's no data coming up underneath A2 (BP oil)
All the rest (4, 6 and 8 Cylinder Engines) work like a charm.
I found that when change the figure to 9 (and not 10 Cylinder), the Table gives me all the requested info.
Obviously it messes with Sorting.

 

[PetarTen]

Select the entire worksheet using ctrl+A.
In format cell-->protection uncheck locked.
Select the range not be sorted.
In format cell-->protection check locked.
Now protect the sheet with password.
Now the selected range cannot edited and sorted.
No need of VBA code.

Hi mate, I understood what you mean...the Range in this case is F column (on Sheet1)
But when I tried to Protect it - the Sorting was already unchecked.
On top of that, I cannot go back in Format Cell to "check locked", because the whole worksheet is dormant.

I still think VBA is the quickest way of sorting this issue. Please help

 

[Alex Blakenburg]

Does changing the formulas in Column E by adding the ",0" or ",FALSE" fix your issue ?
(From approximate match to exact match)

=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0),"")

See below.

Table.xlsm
	A	B	C	D	E	F
1	OIL	ENGINE
2	BP	10 Cylinder	0
3	Valvoline properties	5W-40		5W-40	Valvoline properties	4 Cylinder
4		5W-50		5W-50		6 Cylinder
5	Shell's properties exceed…	5W-60		5W-60	Shell's properties exceed…	8 Cylinder
6	Esso	5W-70		5W-70	Esso	10 Cylinder
Sheet1

Cell Formulas
Range	Formula
A3:A6	A3	=IFNA(INDEX(Sheet1!$E$3:$E$8,Sheet1!$E$2+ROWS(Sheet1!$E$3:E3),COLUMNS(Sheet1!$E$6:E7)),"")
B3:B6	B3	=IFNA(INDEX(Sheet1!$D$3:$D$8,Sheet1!$D$2+ROWS(Sheet1!$D$3:D3),COLUMNS(Sheet1!$D$6:D7)),"")
D3	D3	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,2),"")
E3	E3	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0),"")
F3	F3	=IFERROR(INDEX(Sheet2!$A$7,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
D4	D4	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,3),"")
E4	E4	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,3,0),"")
F4	F4	=IFERROR(INDEX(Sheet2!$A$8,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
D5	D5	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,4),"")
E5	E5	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,4,0),"")
F5	F5	=IFERROR(INDEX(Sheet2!$A$9,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
D6	D6	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,5),"")
E6	E6	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,5,0),"")
F6	F6	=IFERROR(INDEX(Sheet2!$A$10,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")

Named Ranges
Name	Refers To	Cells
Sheet2!_FilterDatabase	=Sheet2!$A$3:$E$5	D3:D6, F3:F6

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Sheet2!#REF!

 

[PetarTen]

Does changing the formulas in Column E by adding the ",0" or ",FALSE" fix your issue ?
(From approximate match to exact match)

=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0),"")

See below.

Table.xlsm
	A	B	C	D	E	F
1	OIL	ENGINE
2	BP	10 Cylinder	0
3	Valvoline properties	5W-40		5W-40	Valvoline properties	4 Cylinder
4		5W-50		5W-50		6 Cylinder
5	Shell's properties exceed…	5W-60		5W-60	Shell's properties exceed…	8 Cylinder
6	Esso	5W-70		5W-70	Esso	10 Cylinder
Sheet1

Cell Formulas
Range	Formula
A3:A6	A3	=IFNA(INDEX(Sheet1!$E$3:$E$8,Sheet1!$E$2+ROWS(Sheet1!$E$3:E3),COLUMNS(Sheet1!$E$6:E7)),"")
B3:B6	B3	=IFNA(INDEX(Sheet1!$D$3:$D$8,Sheet1!$D$2+ROWS(Sheet1!$D$3:D3),COLUMNS(Sheet1!$D$6:D7)),"")
D3	D3	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,2),"")
E3	E3	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0),"")
F3	F3	=IFERROR(INDEX(Sheet2!$A$7,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
D4	D4	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,3),"")
E4	E4	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,3,0),"")
F4	F4	=IFERROR(INDEX(Sheet2!$A$8,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
D5	D5	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,4),"")
E5	E5	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,4,0),"")
F5	F5	=IFERROR(INDEX(Sheet2!$A$9,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
D6	D6	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,5),"")
E6	E6	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,5,0),"")
F6	F6	=IFERROR(INDEX(Sheet2!$A$10,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")

Named Ranges
Name	Refers To	Cells
Sheet2!_FilterDatabase	=Sheet2!$A$3:$E$5	D3:D6, F3:F6

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Sheet2!#REF!

I’ll try it tonight cuz I’m busy working today

 

[PetarTen]

Does changing the formulas in Column E by adding the ",0" or ",FALSE" fix your issue ?
(From approximate match to exact match)

=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0),"")

See below.

Table.xlsm
	A	B	C	D	E	F
1	OIL	ENGINE
2	BP	10 Cylinder	0
3	Valvoline properties	5W-40		5W-40	Valvoline properties	4 Cylinder
4		5W-50		5W-50		6 Cylinder
5	Shell's properties exceed…	5W-60		5W-60	Shell's properties exceed…	8 Cylinder
6	Esso	5W-70		5W-70	Esso	10 Cylinder
Sheet1

Cell Formulas
Range	Formula
A3:A6	A3	=IFNA(INDEX(Sheet1!$E$3:$E$8,Sheet1!$E$2+ROWS(Sheet1!$E$3:E3),COLUMNS(Sheet1!$E$6:E7)),"")
B3:B6	B3	=IFNA(INDEX(Sheet1!$D$3:$D$8,Sheet1!$D$2+ROWS(Sheet1!$D$3:D3),COLUMNS(Sheet1!$D$6:D7)),"")
D3	D3	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,2),"")
E3	E3	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0),"")
F3	F3	=IFERROR(INDEX(Sheet2!$A$7,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
D4	D4	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,3),"")
E4	E4	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,3,0),"")
F4	F4	=IFERROR(INDEX(Sheet2!$A$8,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
D5	D5	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,4),"")
E5	E5	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,4,0),"")
F5	F5	=IFERROR(INDEX(Sheet2!$A$9,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
D6	D6	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,5),"")
E6	E6	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,5,0),"")
F6	F6	=IFERROR(INDEX(Sheet2!$A$10,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")

Named Ranges
Name	Refers To	Cells
Sheet2!_FilterDatabase	=Sheet2!$A$3:$E$5	D3:D6, F3:F6

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Sheet2!#REF!

Thank you, thank you, thank you mate!
God bless you!!!
It works like a charm

Can I ask you another question btw?

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.
If your other question is related, sure. Otherwise, just start a new thread.
Starting a new thread with a targetted Subject line, will help get someone with the appropriate interests / skills to look at your question.

 

[PetarTen]

Thanks for letting us know. Glad we could help.
If your other question is related, sure. Otherwise, just start a new thread.
Starting a new thread with a targetted Subject line, will help get someone with the appropriate interests / skills to look at your question.

Yes, I think it's related...
It's about the Sorting in Dropdown Table, Column A, Sheet1....which corresponds to everything that's written in Column C, Sheet1 ...which is linked to the Second Table on Sheet2, A7:F11.
IF there are Empty cells in that Second Table A7:F11 (like the missing Properties in 4 cylinder - D7, D8 and D9 ....and like 12 cylinder - D11 and E11), but then on F11 there IS a written Property - when you choose 12 cylinder in the BP dropdown list, you CANNOT see those Properties but you have to slide down with the slider!
Is this GLITCH caused by the Alphabetic sorting or something else must be done on Sheet1, Column C ?
Those missing rows are so visible there...and they're (obviously) transferred in the Dropdown Table.
What do you think, could this be avoided somehow?
Cheers!
PS You can find the updated File here:
Filebin | orb2g46p7o2uf4v9

 

[Alex Blakenburg]

I'm afraid I am the wrong person to ask about forms.
See if this helps at all, I have changed the VLookup in column E to return "" and not 0 (which you are hiding with your custom format)

=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0)&"","")

and changed column A to a filter function.

Table 20241114 v03.xlsm
	A	B	C	D	E	F
1	OIL	ENGINE
2	BP	10 Cylinder	0
3	Valvoline properties	5W-40		5W-40	Valvoline properties	4 Cylinder
4	Shell's properties exceed…	5W-50		5W-50		6 Cylinder
5	Esso	5W-60		5W-60	Shell's properties exceed…	8 Cylinder
6		5W-70		5W-70	Esso	10 Cylinder
7						12 Cylinder
Sheet1

Cell Formulas
Range	Formula
A3:A5	A3	=FILTER(E3:E8,E3:E8<>"")
E3	E3	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0)&"","")
F3	F3	=IFERROR(INDEX(Sheet2!$A$7,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
E4	E4	=IFNA(VLOOKUP($B$2,Sheet2!A8:F12,3,0)&"","")
F4	F4	=IFERROR(INDEX(Sheet2!$A$8,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
E5	E5	=IFNA(VLOOKUP($B$2,Sheet2!A9:F13,4,0)&"","")
F5	F5	=IFERROR(INDEX(Sheet2!$A$9,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
E6	E6	=IFNA(VLOOKUP($B$2,Sheet2!A10:F14,5,0)&"","")
F6	F6	=IFERROR(INDEX(Sheet2!$A$10,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
E7	E7	=IFNA(VLOOKUP($B$2,Sheet2!A11:F15,6,0)&"","")
F7	F7	=IFERROR(INDEX(Sheet2!$A$11,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
B3:B6	B3	=IFNA(INDEX(Sheet1!$D$3:$D$8,Sheet1!$D$2+ROWS(Sheet1!$D$3:D3),COLUMNS(Sheet1!$D$6:D7)),"")
D3	D3	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,2),"")
D4	D4	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,3),"")
D5	D5	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,4),"")
D6	D6	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,5),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Sheet2!_FilterDatabase	=Sheet2!$A$3:$E$5	D3:D6, F3:F7

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Sheet2!#REF!

 

[PetarTen]

I'm afraid I am the wrong person to ask about forms.
See if this helps at all, I have changed the VLookup in column E to return "" and not 0 (which you are hiding with your custom format)

=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0)&"","")

and changed column A to a filter function.

Table 20241114 v03.xlsm
	A	B	C	D	E	F
1	OIL	ENGINE
2	BP	10 Cylinder	0
3	Valvoline properties	5W-40		5W-40	Valvoline properties	4 Cylinder
4	Shell's properties exceed…	5W-50		5W-50		6 Cylinder
5	Esso	5W-60		5W-60	Shell's properties exceed…	8 Cylinder
6		5W-70		5W-70	Esso	10 Cylinder
7						12 Cylinder
Sheet1

Cell Formulas
Range	Formula
A3:A5	A3	=FILTER(E3:E8,E3:E8<>"")
E3	E3	=IFNA(VLOOKUP($B$2,Sheet2!A7:F11,2,0)&"","")
F3	F3	=IFERROR(INDEX(Sheet2!$A$7,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
E4	E4	=IFNA(VLOOKUP($B$2,Sheet2!A8:F12,3,0)&"","")
F4	F4	=IFERROR(INDEX(Sheet2!$A$8,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
E5	E5	=IFNA(VLOOKUP($B$2,Sheet2!A9:F13,4,0)&"","")
F5	F5	=IFERROR(INDEX(Sheet2!$A$9,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
E6	E6	=IFNA(VLOOKUP($B$2,Sheet2!A10:F14,5,0)&"","")
F6	F6	=IFERROR(INDEX(Sheet2!$A$10,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
E7	E7	=IFNA(VLOOKUP($B$2,Sheet2!A11:F15,6,0)&"","")
F7	F7	=IFERROR(INDEX(Sheet2!$A$11,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")
B3:B6	B3	=IFNA(INDEX(Sheet1!$D$3:$D$8,Sheet1!$D$2+ROWS(Sheet1!$D$3:D3),COLUMNS(Sheet1!$D$6:D7)),"")
D3	D3	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,2),"")
D4	D4	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,3),"")
D5	D5	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,4),"")
D6	D6	=IFNA(VLOOKUP($A$2,Sheet2!A1:E4,5),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Sheet2!_FilterDatabase	=Sheet2!$A$3:$E$5	D3:D6, F3:F7

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Sheet2!#REF!

Hi mate,
Is it me, or it's simply the Office pack version?
I don't think it has FILTER function

I.e. I cannot add a Filter to Column A

 

[Alex Blakenburg]

That formula is only available if you have Office 2021+ or MS 365. Your profile indicated you have MS 365.
Are you definitely using 365 on the machine that you are using this spreadsheet ?

 

[PetarTen]

That formula is only available if you have Office 2021+ or MS 365. Your profile indicated you have MS 365.
Are you definitely using 365 on the machine that you are using this spreadsheet ?

Noup! I'm using Office 2016