Issue with generating dynamic external cell references via concatenating

MrSquiffy

New Member
Joined
Aug 30, 2018
Messages
4
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;">
Code:
=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;">
Code:
=(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;">
Code:
=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;">
Code:
"!$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;">
Code:
=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;">
Code:
=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;">
Code:
=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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:

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

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

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top