formula based on three dependent drop down lists

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

formula based on three dependent drop down lists

Post by DVM »

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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

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.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: formula based on three dependent drop down lists

Post by DVM »

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

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

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: formula based on three dependent drop down lists

Post by DVM »

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

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]],"")
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: formula based on three dependent drop down lists

Post by DVM »

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.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

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]],"")
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

And to prevent formulas being removed, in the Worksheet_Change event of Masterlist:

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
'           ----------------------------------------------------------------------------------------------------------------
The rest of the code can remain the same.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: formula based on three dependent drop down lists

Post by DVM »

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

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.
V1_R2_NR.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: formula based on three dependent drop down lists

Post by DVM »

Yes , it works and thank you. But the macros are not running! Please give me an advise.
Best regards

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

Make sure that macros aren't blocked when you open the workbook.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: formula based on three dependent drop down lists

Post by DVM »

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.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

Which macros don't work?
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: formula based on three dependent drop down lists

Post by DVM »

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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

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

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: formula based on three dependent drop down lists

Post by DVM »

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula based on three dependent drop down lists

Post by HansV »

Which attachment?
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: formula based on three dependent drop down lists

Post by DVM »

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.