formula based on three dependent drop down lists
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
formula based on three dependent drop down lists
Dear sir,
Please see this attachment and refer to sheets MasterList! and DV!
In sheet MasterList!, I have three dependent drop downs and the ranges for these drop downs is in sheet DV! In Col D sheet DC!, there is a code corresponding to every selection that is made in cols D, E anf F in masterlist!
In DV! I also have two ranges H3:H40 and I3:I40
There is a drop down list in MasterList! col B corresponding to range H3:H40 in DV!
I need a formula in G5 of MasterList! which will give me an AND of the following:
1. ISA Code ( look up in Col D of wsh DV! against dependent drop downs in Col D E and F of masterlist!)
2. VLOOK of Range H3:H40 in range I3:I40 in wsh DV!
Please help
DVM
Please see this attachment and refer to sheets MasterList! and DV!
In sheet MasterList!, I have three dependent drop downs and the ranges for these drop downs is in sheet DV! In Col D sheet DC!, there is a code corresponding to every selection that is made in cols D, E anf F in masterlist!
In DV! I also have two ranges H3:H40 and I3:I40
There is a drop down list in MasterList! col B corresponding to range H3:H40 in DV!
I need a formula in G5 of MasterList! which will give me an AND of the following:
1. ISA Code ( look up in Col D of wsh DV! against dependent drop downs in Col D E and F of masterlist!)
2. VLOOK of Range H3:H40 in range I3:I40 in wsh DV!
Please help
DVM
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula based on three dependent drop down lists
1) Do you mean that you want a formula in H5 instead of G5?
2) What do you want to look up in H3:H40 of the DV sheet? Cell B5?
3) There is code that wipes out any formula I place in H5.
2) What do you want to look up in H3:H40 of the DV sheet? Cell B5?
3) There is code that wipes out any formula I place in H5.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: formula based on three dependent drop down lists
Dear Hans,
Please erase the existing formula in H5 and please have your new formula in H5 and not G5
for the value of B5 in masterList, I want to look up the corresponding value in col I in sheet DV!
Regards
DVM
Please erase the existing formula in H5 and please have your new formula in H5 and not G5
for the value of B5 in masterList, I want to look up the corresponding value in col I in sheet DV!
Regards
DVM
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula based on three dependent drop down lists
Any formula that I enter in H5 is immediately replaced with its result when I press Enter. So it's virtually impossible to test.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: formula based on three dependent drop down lists
Here is the password for the macros vdesai! but i do not think there is a macro for the formula in H5. In any case you may also try the formula in G5 if not in H5
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula based on three dependent drop down lists
I don't understand what you're doing, but does this do what you want?
=IF(AND(B5<>"",E5<>""),$F$1&"-"&VLOOKUP([@[Service Area]],DV!$H$3:$I$40,2,FALSE)&[@[ISA_Code]],"")
=IF(AND(B5<>"",E5<>""),$F$1&"-"&VLOOKUP([@[Service Area]],DV!$H$3:$I$40,2,FALSE)&[@[ISA_Code]],"")
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: formula based on three dependent drop down lists
I think I am not doing it correct.
Is it possible to clear any existing formulas in G and H of Masterlist?
If yes, the let me have these two formulas separately
In cell Mastersheet! G5, formula is the vlookup for the D3 cell value in the cols A, B and C in worksheet DV!
In cell Master sheet! H5, formula is the vlookup for value of I3 in h3 in DV!
Please advise me if I am thinking wrong.
Is it possible to clear any existing formulas in G and H of Masterlist?
If yes, the let me have these two formulas separately
In cell Mastersheet! G5, formula is the vlookup for the D3 cell value in the cols A, B and C in worksheet DV!
In cell Master sheet! H5, formula is the vlookup for value of I3 in h3 in DV!
Please advise me if I am thinking wrong.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula based on three dependent drop down lists
In G5:
=IFERROR(OFFSET(INDEX(INDIRECT([@[Device Function]]),MATCH([@Measurement],INDIRECT([@[Device Function]]),0)),0,1),"")
In H5:
=IF(AND(B5<>"",E5<>""),VLOOKUP([@[Service Area]],DV!$H$3:$I$40,2,FALSE)&"-"&[@[ISA_Code]],"")
=IFERROR(OFFSET(INDEX(INDIRECT([@[Device Function]]),MATCH([@Measurement],INDIRECT([@[Device Function]]),0)),0,1),"")
In H5:
=IF(AND(B5<>"",E5<>""),VLOOKUP([@[Service Area]],DV!$H$3:$I$40,2,FALSE)&"-"&[@[ISA_Code]],"")
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula based on three dependent drop down lists
And to prevent formulas being removed, in the Worksheet_Change event of Masterlist:
The rest of the code can remain the same.
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Const NUMBEROFCOLUMNS = 33
Dim vPrevValue As Variant, vCurValue As Variant, varOld As Variant, varNew As Variant
Dim lngIndex As Long, lngRowCount As Long, lngCount As Long, RowNum As Long, r As Long
Dim strSource As String, strDestination As String, strList As String, GenericValue As String
Dim wshSource As Worksheet, wshDestination As Worksheet, wshList As Worksheet, wsh As Worksheet
Dim rngSource As Range, rngDestination As Range
Dim firstpass As Boolean
Dim user_response As Integer
Dim strFormula As String
' ----------------------------------------------------------------------------------------------------------------
If mbNoEvent Then Exit Sub
If Target.Row <= ActiveSheet.ListObjects(1).HeaderRowRange.Row Then Exit Sub
If ((Target.Address = ActiveSheet.ListObjects(1).HeaderRowRange.Offset(ActiveSheet.ListObjects(1).ListRows.Count).Address) And Target.Rows.Count = 1) Then Exit Sub
If (Target.Address = Target.EntireRow.Address) Then Exit Sub
' ----------------------------------------------------------------------------------------------------------------
Application.EnableEvents = False
varNew = Target.Value
strFormula = Target.Formula
Application.Undo
varOld = Target.Value
Target.Formula = strFormula
Application.EnableEvents = True
' ----------------------------------------------------------------------------------------------------------------
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: formula based on three dependent drop down lists
Dear Hans,
I see an error in the formula.
Please refer to the below 2 formulas
=IFERROR(INDEX(OFFSET(INDIRECT(Table3[[#This Row],[Instrument_Main]]),0,1),MATCH(Table3[[#This Row],[Instrument_Type]],INDIRECT(Table3[[#This Row],[Instrument_Main]]),0)),"")
The above formula worked perfectly well for two dropdown lists, for which the headers was Col D: Instrument_Main and Col E: Instrument_Type
"=IFERROR(OFFSET(INDEX(INDIRECT([@[Device Function]]),MATCH([@Measurement],INDIRECT([@[Device Function]]),0)),0,1),"""")
"
The error in this new formula for 3 drop downs - Col D: Device, ColE: Device Function and Col F: Measurement is shown in Yellow highlight above.
I cant be sure but is it because :
1. Col D should be Device and not Device Function as seen in above formuls. I tried correcting it but did not pass the test.
2. Are names such as Device Function with a space between the two words not a correct way?
Please advise.
Best regards
DVM
I see an error in the formula.
Please refer to the below 2 formulas
=IFERROR(INDEX(OFFSET(INDIRECT(Table3[[#This Row],[Instrument_Main]]),0,1),MATCH(Table3[[#This Row],[Instrument_Type]],INDIRECT(Table3[[#This Row],[Instrument_Main]]),0)),"")
The above formula worked perfectly well for two dropdown lists, for which the headers was Col D: Instrument_Main and Col E: Instrument_Type
"=IFERROR(OFFSET(INDEX(INDIRECT([@[Device Function]]),MATCH([@Measurement],INDIRECT([@[Device Function]]),0)),0,1),"""")
"
The error in this new formula for 3 drop downs - Col D: Device, ColE: Device Function and Col F: Measurement is shown in Yellow highlight above.
I cant be sure but is it because :
1. Col D should be Device and not Device Function as seen in above formuls. I tried correcting it but did not pass the test.
2. Are names such as Device Function with a space between the two words not a correct way?
Please advise.
Best regards
DVM
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula based on three dependent drop down lists
The formula does not look at columns D and E, but at columns E and F. It works for me. Here is the workbook with the formulas.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: formula based on three dependent drop down lists
Yes , it works and thank you. But the macros are not running! Please give me an advise.
Best regards
Best regards
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula based on three dependent drop down lists
Make sure that macros aren't blocked when you open the workbook.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: formula based on three dependent drop down lists
Yes, I have done this to my best knowledge. The file is saved as .xlsm, and configured for 'enable all macros " from excel options Please see the same file you sent me. I am surely missing something that I cant figure out. Your help to resolve this is highly appreciated.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: formula based on three dependent drop down lists
The change event macro in the sheet masterlist! When I change the selection in cell D, Cell E border changes its color to red and when I make the selection in cell E the red border disappears.
I also tested tree other files, but I observed in all three cases that when the formula in cell G is entered and executed, the macro stops working. Please find the attached file without the formula.
I also tested tree other files, but I observed in all three cases that when the formula in cell G is entered and executed, the macro stops working. Please find the attached file without the formula.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula based on three dependent drop down lists
The reason is that the workbooks are incomplete. There should be a sheet corresponding to each value in the Devices list DV!A3:A14. There are no sheets named Elements, Read_Outs, Test_Points etc. This causes the code to throw an error when it tries to refer to such a nonexistent sheet.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: formula based on three dependent drop down lists
Dear Hans,
I have corrected all that you have mentioned and also tried different options but was not able to resolve this issue. I really need your further assistance to resolve it. Please see the attachment Please help.
Regards
DVM
I have corrected all that you have mentioned and also tried different options but was not able to resolve this issue. I really need your further assistance to resolve it. Please see the attachment Please help.
Regards
DVM
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: formula based on three dependent drop down lists
Apologies that the attachment was not transmitted, because it is oversized 307 KB. I earlier reduced the file size by deleting some work sheets and that is why I did not recollect I had to redo the worksheets again. Please let me know if I could send you the file on your personal ID. I cannot reduce the file size any more to make sure I convey you all that is minimum required.