Issue with generating dynamic external cell references via concatenating

[MrSquiffy]

Hi everyone

I'm trying to write some code to automate a search we carry out at my company over 64 spreadsheets, with 6 sub-sheets apiece. Due to the typical large-company 'left hand, right hand' issues and due to strict policy, merging the spreadsheets is not an option. As such I'm looking at one spreadsheet which searches for a given business name across all spreadsheets, however while creating a direct link to each individual spreadsheet and sub-sheet works, I'm trying to generate the sub-sheets dynamically.
Code linking to the spreadsheet directly:

Using SumProduct:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

=SUMPRODUCT(--('\\samnedfsn1\common\Exception - Frequent Access Spreadsheets\Customer Services\Irregularities spreadsheets\2017-18 irregularities spreadsheets\[YH Irregularities Spreadsheet 2017-18.xlsx]Irregularities'!$A:$A=J5))

</code>Using VLookup:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

=(IFERROR(IF(VLOOKUP(J5,'\\samnedfsn1\common\Exception - Frequent Access Spreadsheets\Customer Services\Irregularities spreadsheets\2017-18 irregularities spreadsheets\[YH Irregularities Spreadsheet 2017-18.xlsx]Irregularities'!$A:$A,1,FALSE)=J5,"Yes","No"),"No"))

</code>In this case 'J5' refers to the cell being used for the search (in this case 'Megginson '), and the search runs fine.

Direct links - code working

However when trying to generate the links dynamically I've been trying to concatenate the address so I can generate the references to the sub-sheets dynamically.
This is the code I'm using for SumProduct trying to generate it:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

=SUMPRODUCT(--("'\\samnedfsn1\common\Exception - Frequent Access Spreadsheets\Customer Services\Irregularities spreadsheets\2017-18 irregularities spreadsheets\[YH Irregularities Spreadsheet 2017-18.xlsx]" & Lists!A2:A7 & "!$A:$A=" & $J$5  ))

</code>(note: For any further screenshots 'Lists!A2:A7' will be 'Lists!:A2' to improve readability)

However this code generates a #value error. Now, according to the formula evaluation box the code is generating the links to the sheets as an array, so that's working fine. This is what the evaluation box shows before it generates the #value error:

sumproduct not working - 1

Now I noticed that the "megginson " part at the end appears to be losing its quotation marks, which I thought could be the cause, so I amended the end of the SumProduct code to this:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

"!$A:$A=" & CHAR(34) & $J$5 & CHAR(34)

</code>But this is now what the evaluation is showing before generating the error

sumproduct not working - 2

Where now it appears that there's too many quotation marks at the end. I've tried varying combinations of adding quotation marks around the J5 reference, but none of them solve the issue.

I've also attempted to use VLookup instead, using this code for dynamically generating the links:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

=VLOOKUP(J5,"\\samnedfsn1\common\Exception - Frequent Access Spreadsheets\Customer Services\Irregularities spreadsheets\2017-18 irregularities spreadsheets\[YH Irregularities Spreadsheet 2017-18.xlsx]" & "Irregularities!" & "$A:$A",1,FALSE)

</code>which also generates the same #value error.

This is the formula evaluation for VLookup before it fails

not working - VLookup

This one's really confusing me, as I can't functionally see any difference between the output between the code that directly links to the irregularities spreadsheet and to the one that attempts to generate the link dynamically, aside from the presence of the quotation marks (which I assume disappear when the formula evalutates?).

Direct Link (formula evaluated to step before result returned):

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

=VLOOKUP("Megginson ",'\\samnedfsn1\common\Exception - Frequent Access Spreadsheets\Customer Services\Irregularities spreadsheets\2017-18 irregularities spreadsheets\[YH Irregularities Spreadsheet 2017-18.xlsx]Irregularities'!$A:$A,1,FALSE)

</code>Dynamic Link (formula evaluated to step before failure):

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

=VLOOKUP("Megginson ", "'\\samnedfsn1\common\Exception - Frequent Access Spreadsheets\Customer Services\Irregularities spreadsheets\2017-18 irregularities spreadsheets\[YH Irregularities Spreadsheet 2017-18.xlsx]Irregularities'!$A:$A",1,FALSE)

</code>Now I figure that the issue is with trying to generate the external sheet reference via concatenation, however I have absolutely no idea how to amend the code so the links generate correctly (assuming that is what's going wrong). If someone can help me by telling me what's going wrong I would be incredibly grateful.

Cheers!

 

[RoryA]

Welcome to the forum.

You can't use text as a file reference unless you put it inside an INDIRECT function, and that won't work unless the source file is open.

You keep mentioning code, but I presume you are just talking about formulas since you haven't posted any code at all?

 

[MrSquiffy]

Welcome to the forum.

You can't use text as a file reference unless you put it inside an INDIRECT function, and that won't work unless the source file is open.

You keep mentioning code, but I presume you are just talking about formulas since you haven't posted any code at all?

Yes sorry, I meant formulas. I'm not a programmer at all, just an entry-level office monkey looking to make his workflow faster, so forgive me if I butcher the jargon. I figured that I can use the INDIRECT function to get the reference, but the whole 'having to have the spreadsheet open' is the issue I'm trying to bypass. I've got 64 spreadsheets to go through, if I have to open them all it defeats the point of making the search in the first place.

I was wondering if it was possible to feed the formula a text string seeing how the VLookup and SumProduct formulas I made to directly link to each sub-sheet does work correctly.

 

[RoryA]

I was wondering if it was possible to feed the formula a text string seeing how the VLookup and SumProduct formulas I made to directly link to each sub-sheet does work correctly.

No, not without INDIRECT. You would need code instead, either to search the workbooks or to amend the formula in-place.

 

[MrSquiffy]

So that would be VBA, right? Where would be a good place to start looking at learning that?

 

[RoryA]

Correct.

It depends on how you learn best really. There are plenty of online sites, or you can get a VBA for Dummies book and go from there.

As a starting point, you might be able to use Application.ExecuteExcel4Macro like this:

application.ExecuteExcel4Macro("VLOOKUP(""Megginson "",'C:\some path\[book1.xls]Sheet1'!C1,1,FALSE)")

Note that you must use R1C1 style references, not A1.

 

[MrSquiffy]

Okay wonderful, thank you very much!