concatenated formula help

[bmpreston]

I'm trying to make a formula to put data in from an external closed sheet.

This is what I have so far, but it doesn't work:

='"https:\teams.foobar.com\sites\GSATGS\secserv\ops\REGION 3\Inventory Sheets" & B$1 & "\[" & B$1 & " Inventory Sheet " & TEXT(D$1, "M-D-YYYY")& ".xlsx]EQUIPMENT'!E3

(B1 is Smith, John)
(D1 is 2/1/17)

If you just type this in:

='https://teams.foobar.com/sites/GSATGS/secserv/ops/REGION 3/Inventory Sheets/Smith, John/[Smith, John Inventory Sheet 2-1-2017.xlsx]EQUIPMENT'!E3

That works

And when I break it down into smaller sections it all seems to work:

=TEXT(D1, "M-D-YYYY")

Gives 2-1-2017 - Checks out

This:

="Inventory Sheets/" & B$1 & "/[" & B$1 & " Inventory Sheet " & TEXT(D$1, "M-D-YYYY")& ".xlsx"

Gives Inventory Sheets/Smith, John/Smith, John Inventory Sheet 2-1-2017.xlsx - Checks out.

Any help- Super appreciated!


To be clear, in this forum post, there may be small grammatical errors, if you see something, speak up, I did a lot of copying and pasting, but sometimes quotes, brackets, and parenthesis gets moved all over...

Thanks

 

[davesexcel]

You may need to use indirect, kind of hard to test out though.
For example if I had John Smith in A1 and a sheet Named Smith John and wanted to reference E3 in Smith John sheet.

=INDIRECT("'"&A1&"'!e3")

Also having a sheet name with commas could cause problems.

 

[bmpreston]

The name in B1 is in fact LASTNAME, FIRSTNAME which is the formatting of the first folder naming, and the name of the file.

I will try using INDIRECT.

Thanks

 

[bmpreston]

So I built this and it works, EXCEPT

=INDIRECT("AF2")&INDIRECT("AF4")&"/["&INDIRECT("AF4")&INDIRECT("AF5")& TEXT(D1, "M-D-YYYY")&INDIRECT("AF7")

Gives me

https://teams.foobar.com/sites/GSATGS/secserv/ops/REGION 3/Inventory Sheets/Smith, Bob/[Smith, Bob Inventory Sheet 2-1-2017.xlsx]EQUIPMENT'!E3

<tbody>
</tbody>

However, now that this exists as the result in the cell, how do I add an equals sign and a quote to actually use it as a formula to pull from the sheet?

Thanks

 

[Jonmo1]

Indirect won't work on a closed workbook.

There is an addin called Morefunc that has a similar function that will.
You can find it in the Recommended addins thread.

 

[bmpreston]

Ok, so the sheet is open. However the resultant in the cell is still the actual equals and single quote. It doesn't actually process the ='FILENAME HERE' etc...

Any help

Thanks