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

 

[FDibbins]

Your "text" formula uses B2 as the reference. Is it B2 or D2?
This worked fine for me./..

	H​	I​
1​	10/13/2017​	10-13-17

I1=TEXT(H1,"M-D-Y")

 

[bmpreston]

Your "text" formula uses B2 as the reference. Is it B2 or D2?
This worked fine for me./..

	H​	I​
1​	10/13/2017​	10-13-17

<tbody>
</tbody>

I1=TEXT(H1,"M-D-Y")

You are right for questioning. The formulas from above in this topic were using B2, and I have since changed to D2. It was a copy and paste thing. Regardless, I have changed them, see for yourself in this screenshot. Nevermind all the other REF failures, this is just me trying to get ONE formula to work, so I'm using them as formula test cells.

From my understanding, I'm asking D2 (currently a date, formatted as a date) to be converted to text, in the Month, Date, Year, or really YEar format. It just gives a #NAME .

If I get this working, maybe the indirect will work...

Thanks

 

[bmpreston]

To help, I can tell you if I evaluate that formula, it chokes on the Month part.

=TEXT(43010,#NAME?,D,Y)

 

[FDibbins]

You need to wrap the m-d-yy in ""
=TEXT(H1,"M-D-Y")
not =TEXT(H1,M-D-Y)

 

[bmpreston]

Ok.

This works flawlessly..

=IF(D$2="","",(INDIRECT("'C:\Users\bprest1\Backup\Documents\Inventory\Master Region Stale\2017\[Region 3 Master Inventory Stale Report "&TEXT(D$2,"M-D-Y")&".xlsx]Burris, Michael'!$E1")))

Pulls the Date from D2 (that is live so I can drag it across the sheet), and grabs from that other file, on the Burris, Michael sheet, value in E1.

PERFECT!

However...

As soon as I close the open sheet, it instantly crashes the formula to a #REF . Can I get this to stay alive after closing the sheet? Otherwise, week after week I'd have to have EVERY file open to save the values in the fields until I can get it opened, saved, and exported.

Thanks

 

[FDibbins]

Not with using INDIRECT, no, it only works on open workbooks

Another option might be to look at the Morefunc add-in
http://www.ashishmathur.com/tag/morefunc/
This allows referencing closed wokbooks

 

[bmpreston]

As a function of closing out this thread.... All is said and done. It's awesome.

Thanks