Change of Excel Formula Dynamic Variable

[bmpreston]

Hello

I'm looking to make this:

='C:\Users\bprest1\Backup\Documents\Inventory\Master Region Stale\2017\[Master Region Stale Report 9-4-17.xlsx]Burris, Michael'!$E1

Use a variable cell reference for the DATE.

='C:\Users\bprest1\Backup\Documents\Inventory\Master Region Stale\2017\[Master Region Stale Report B2.xlsx]Burris, Michael'!$E1

B2 is a date.

I've tried this:

=INDIRECT("C:\Users\bprest1\Backup\Documents\Inventory\Master Region Stale\2017\[Master Region Stale Report " & B2 &".xlsx]Burris, Michael'!$E1")

No dice

Thanks

 

[mart37]

=INDIRECT("'C:\Users\bprest1\Backup\Documents\Inventory\Master Region Stale\2017\[Master Region Stale Report "&TEXT(B2,"d-m-yy")&".xlsx]Burris, Michael'!$E1")

 

[AlanY]

excel store the dates as numbers, e.g. 9-4-17 stored as 42982.

try replace B2 in your formula to TEXT(B2,"d-m-yy"), swap the d & m around if you're using the m-d-yyyy format in B2

 

[bmpreston]

Seems that none of this works. Everything gives a #REF .

I tried just to remove the variable and see if that works with the INDIRECT function, and same thing, just a #REF .

Both =INDIRECT("'C:\Users\bprest1\Backup\Documents\Inventory\Master Region Stale\2017\[Master Region Stale Report "&TEXT(B2,"m-d-yyyy")&".xlsx]Burris, Michael'!$E1")

(B2 is "9-4-17, and 9-4-2017 respectively)

and

=INDIRECT("'C:\Users\bprest1\Backup\Documents\Inventory\Master Region Stale\2017\[Master Region Stale Report 9-4-17.xlsx]Burris, Michael'!$E1")

Interestingly, removing the INDIRECT, the parenthesis, the outer quotes, and the value does populate, meaning the file is valid and structure is accurate. I've read that file names with spaces are an issue with INDIRECT, is this true?

Thanks

 

[FDibbins]

Note that INDIRECT only works on open workbooks

 

[bmpreston]

I was not aware of the open workbook requirement at first, but I can say its open now while testing, and I repaste the formula into a new clean cell and same thing.

If I paste in the un'formulized' cell equals it asks for the file location. Just pasting the =INDIRECT cell call, it will ask for the location. I think that's telling.

Any help is appreciated.

 

[Marcelo Branco]

With the file opened see if this works
EDIT
=INDIRECT("'[Master Region Stale Report "&TEXT(B2,"d-m-yy")&".xlsx]Burris, Michael'!$E1")



M.

 

[bmpreston]

So after a delay I'm back working on this. It's really aggravating I can't get it. If I call the sheet, workbook, cell accordingly, it works without a hitch. When I ask it to concatenate the formula by using the date in a cell, as part of the sheet name to reference I get #REF . Workbook open or not.

Interestingly a few points. If I run just a TEXT(CELL WITH DATE "D2","m-d-yy") I get a #Name

This formula without the Text call in it, works great:

=SUM('C:\Users\bprest1\Backup\Documents\Inventory\Master Region Stale\2017\[Master Region Stale Report 10-2-17.xlsx]Burris, Michael'!$E$1)

This does not, because the TEXT function seems to fail

=INDIRECT("'[Master Region Stale Report "&TEXT(B2,"m-d-yy")&".xlsx]Burris, Michael'!$E1")

Doesn't matter if the sheet is open or not....

Thanks in advance

 

[FDibbins]

Just for clarity...
=TEXT(B2,"m-d-yy")
this returns an error?
what is in B2? Are you sure its a real date?
test with =isnumber(B2)

 

[bmpreston]

Just for clarity...
=TEXT(B2,"m-d-yy")
this returns an error?
what is in B2? Are you sure its a real date?
test with =isnumber(B2)

Correct. =Text does give a #NAME error, since I'm trying to evaluate why the bigger formula doesn't work.

I have just confirmed. (I'm actually using D2) =ISNUMBER(D2) Provide TRUE

D2 is DATE, formatted at M-D-YYYY

Thanks for responding.