Building closed workbook file path within an Index Function

[William C Gates]

Hi All,

I have searched for an option to allow me to build a path of a closed workbook yet allow the index formula to reference that 'built' path; an 'Add-in' was the main result, but the websites with the add-in no longer works...so I'm asking you experts!

Here is my sample Data ('...' is used to reduce path string):

Row Column A Column B
1 Folder 2014 This Cell will change yearly
2 Folder 2014-04 This Cell will change monthly
3 File Date 04.30.14 This Cell will change monthly
4 Sheet Name Notes Payable Roll

Formula entered in cell A7

="...\Audit Workpapers\"&A1&"\"&A2&"\Note Payable\[Loan Roll Forward - "&A3&".xlsx]"&A4&"'!V80")

(A): Results from above function

...\Audit Workpapers\2014\2014-04\Note Payable\[Loan Roll Forward - 04.30.14.xlsx] Notes Payable Roll'!V80


This Index Formula works fine when the path is manually entered:
=INDEX('...\Audit Workpapers\2014\2014-04\Note Payable\[Loan Roll Forward - 04.30.14.xlsx] Notes Payable Roll'!V80,1,1)

How can I get the Index Function to work when I want to reference '(A)' above (Cell A7 Formula)?

When using the Index Function to build, I either get the entire path of the closed worked book in the cell or a '#VALUE!' error.

My Thoughts that didn't work:

=Index("...\Audit Workpapers\"&A1&"\"&A2&"\Note Payable\[Loan Roll Forward - "&A3&".xlsx]"&A4&"'!V80,1,1)
OR
=Index(A7,1,1)


I know =Indirect will only work on an open workbook; but that is the only formula I can think of that will allow me to build a file path that will return a result.

Any help on this would be greatly appreciated.

Thank you

William

 

[Willie G]

I know this was posted late Friday, but hope to push this to the top to get some help with this today - Thank you.

 

[GS]

I know this was posted late Friday, but hope to push this to the top

to get some help with this today - Thank you.

Take at look at how the INDIRECT() function might serve your purpose.
It uses a text string that returns a ref which will allow the INDEX()
function to work.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Willie G]

Take at look at how the INDIRECT() function might serve your purpose.

It uses a text string that returns a ref which will allow the INDEX()

function to work.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

I tried the below formula. It works, but only when the file is open.

=INDEX(INDIRECT("'...\Audit Workpapers\"&A1&"\"&A2&"\Note Payable\[Loan Roll Forward - "&A3&".xlsx] Notes Payable Roll'!V80"),1)

When I open the file that has this formula it asks if I want to update links, and I click no, I still get the #REF! value. That value goes away once the 'Loan Roll...' file is opened.

Thanks,

 

[GS]

Take at look at how the INDIRECT() function might serve your
purpose.

It uses a text string that returns a ref which will allow the
INDEX()

function to work.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

I tried the below formula. It works, but only when the file is open.

=INDEX(INDIRECT("'...\Audit Workpapers\"&A1&"\"&A2&"\Note
Payable\[Loan Roll Forward - "&A3&".xlsx] Notes Payable
Roll'!V80"),1)

When I open the file that has this formula it asks if I want to
update links, and I click no, I still get the #REF! value. That value
goes away once the 'Loan Roll...' file is opened.

Thanks,

Normally, I read info in closed workbooks differently via loading the
data into a recordset, an array, or open the file in an automated
instance to access its data. This is all done via VBA when my projects
require access to external data. I recall using a standard query once
for a client, where they wanted to refresh a hidden sheet with changes
to another file on there network server. All refs were to the query
sheet so any sheets using formulas that pull data from there would
update automatically after 'Refresh' was done. Again, though, the
update/refresh was handled via VBA.

Hopefully someone with some experience with what you're trying to do
will step in to help...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Willie G]

Normally, I read info in closed workbooks differently via loading the

data into a recordset, an array, or open the file in an automated

instance to access its data. This is all done via VBA when my projects

require access to external data. I recall using a standard query once

for a client, where they wanted to refresh a hidden sheet with changes

to another file on there network server. All refs were to the query

sheet so any sheets using formulas that pull data from there would

update automatically after 'Refresh' was done. Again, though, the

update/refresh was handled via VBA.



Hopefully someone with some experience with what you're trying to do

will step in to help...



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Thanks Garry...I have the reference cell pointing directly to the workbook, no fancy formulas. It works when the workbooks are closed, but want to try and get something set up so I don't have to do the find/replace option each month.

I'll keep an eye on this post for new suggestions.

 

[GS]

Normally, I read info in closed workbooks differently via loading

Thanks Garry...I have the reference cell pointing directly to the
workbook, no fancy formulas. It works when the workbooks are closed,
but want to try and get something set up so I don't have to do the
find/replace option each month.

I'll keep an eye on this post for new suggestions.

Would a macro suffice to 'automate' the find/replace task? If so,
record your steps with the macro recorder and run the resulting macro
when needed!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion