I have a formula that needs to reference another worksheet selected by the user. The name of this worksheet will always be different, so I need an adaptable solution.
The good news is that by using VBA I will know what the name of the file is. I've tried writing that name to a cell in my workbook and altered the formula to concatenate the file name into it. But that throws and error.
This is my attempt at concatenating the name:
This is the formula that works when I manually enter the name of the other workbook:
How can I do this please?
The good news is that by using VBA I will know what the name of the file is. I've tried writing that name to a cell in my workbook and altered the formula to concatenate the file name into it. But that throws and error.
This is my attempt at concatenating the name:
Code:
=IF(B2="","",IF(SUM(COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000]"["&G1&"]"&[/COLOR]Vortex!$L:$L,B2),COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000]"["&G1&"]"&[/COLOR]Vortex!$N:$N,B2),COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000]"["&G1&"]"&[/COLOR]Vortex!$P:$P,B2),COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000]"["&G1&"]"&[/COLOR]Vortex!$R:$R,B2),COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000]"["&G1&"]"&[/COLOR]Vortex!$T:$T,B2),COUNTIF[COLOR=#ff0000]("["&G1&"]"&[/COLOR]Vortex!$V:$V,B2))>0,"P","O"))
This is the formula that works when I manually enter the name of the other workbook:
Code:
=IF(B2="","",IF(SUM(COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000][Feb3_Mar2.xlsx][/COLOR]Vortex!$L:$L,B2),COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000][Feb3_Mar2.xlsx][/COLOR]Vortex!$N:$N,B2),COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000][Feb3_Mar2.xlsx][/COLOR]Vortex!$P:$P,B2),COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000][Feb3_Mar2.xlsx][/COLOR]Vortex!$R:$R,B2),COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000][Feb3_Mar2.xlsx][/COLOR]Vortex!$T:$T,B2),COUNTIF[COLOR=#000000]([/COLOR][COLOR=#ff0000][Feb3_Mar2.xlsx][/COLOR]Vortex!$V:$V,B2))>0,"P","O"))
How can I do this please?