• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenate to link cells and create a link across workbooks on a network drive

S

Shoebird

Member
Having a bugger of time with this one.


Creating a short cut for users so all they have to do is enter a date.

The date is broken down into year and month in seperate cells.

I am then trying to link cells and text to create a pathway to draw data out of specific workbooks.


Example. A1=Feb and A2=2013 and A3=x:Data File

=indirect(A3&"PDU Data"&A1&" "&A2&"[PDU Trend "&A1&" &A2&.xls]PDU Trend "&A1&" "&A2&"'!"B3


I know it is lengthy but thanks for help in advance.
 
Shoebird


Firstly, Welcome to the Chandoo.org forums


Your formula is missing an & near the end and a ' near the start

If you change it to

=INDIRECT("'"&A3&"PDU Data"&A1&" "&A2&"[PDU Trend "&A1&" &A2&.xls]PDU Trend "&A1&" "&A2&"'!"&"B3")


It evaluates to

=Indirect("'x:dataPDU DataFeb 2013[PDU Trend Feb 2013.xls]PDU Trend Feb 2013'!B3")
 
Thank you very much for the invite Hui.

You are absolutely right. This was just a mistake made when I was entering it into this forum. But for some reason, I am still having issues with the formula. Any other thoughts?
 
I am able to get this string to work on my C:/ but not on the corporate X:/ IT changed some settings but it still seems to be an issue.


One additional question for you Hui since I have seen your posts all over and you obviously have a lot more exceo experience than myself. Indirect seems to need to have the workbook open. Can I use Index instead of Indirect? And what does the + symbol infront of Index do that is different than without?


Thanks again!
 
You must log in or register to reply here.
Back
Top