I have this formula that works but I can seem to convert it to a VBA code. When I run the code, there is no error, but no value appears. Appreciate your help.
I am basically trying to extract a value from another workbook. There is a master field selection in the activesheet that requires me to Portfolio in B2 and Field in D3. It then runs an index match based on the portfolio and field in another workbook (Balanced Portfolio AA Summary Table). The list of portfolios are from D5:CR5 while the list of fields are from B78:B298. The fields have duplicated values and hence I need to extract the correct one but altering the column range using large.
My formula:
INDEX('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:CR298,AGGREGATE(14,6,(ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:D298)-ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78)+1)/(('[Balanced Portfolio AA_280219.xlsx]Summary Table'!B78:B298 =D3)*(INDEX('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:CR298,0,MATCH(B2,'[Balanced Portfolio AA_280219.xlsx]Summary Table'!D5:CR5,0))<>"")),1),MATCH(B2,'[Balanced Portfolio AA_280219.xlsx]Summary Table'!D5:CR5,0))
My code:
I defined sh1 as '[Balanced Portfolio AA_280219.xlsx]Summary Table' from another workbook
and comboSh1 as the sheet in the active workbook.
I am trying to find obtain a value that has duplicated variables and hence I need this function.
I think there is something wrong when i tried to convert
AGGREGATE(14,6,(ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:D298)-ROW('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78)+1)/(('[Balanced Portfolio AA_280219.xlsx]Summary Table'!B78:B298 =D3)
into
not sure how one range can be = to another range.
Bookmarks