Please check my formula for external cell reference

[Mr ZN]

Hi everyone,

Sorry to ask for help on my first post, hopefully I can get my skills up and start spreading some love around.

Just wanted to check how I could modify the following formula to include a cell reference containing the current date which would thereby allow the formula to find the current dates workbook. Hope that makes sense, I will try to elaborate more below...

This is the formula:
='Z:\Admin\Afternoon Reports\July\260713\[tradefiledata260713.xlsx]Iress Trades - All'!$A5

The red text is where I want to put a cell reference (I1) in, for example...

='Z:\Admin\Afternoon Reports\July\I1\[tradefiledataI1.xlsx]Iress Trades - All'!$A5

Would anyone be able to point me in the right direction? I have already created the cell in the spreadsheet that contains the date in the correct format.

Thank you in advance for any help!

Justin

[XOR LX]

Hi,

Assuming the cell in which your desired variable lies is A1:

=INDIRECT("'Z:\Admin\Afternoon Reports\July\"&$A$1&"\[tradefiledata"&$A$1&".xlsx]Iress Trades - All'!$A5")

Regards

[Jonmo1]

Indirect won't work on a closed workbook.

You'll need the Morefunc Addin which has it's own indirect function that does work on closed books.

[Mr ZN]

Thank you XOR LX & Jonmo1 for your timely help, it is very much appreciated!

Would you be able to explain why Indirect will not work on a closed work book? I'm just finding it hard to wrap my head around as the formula works without the INDIRECT function as long as I do not have a cell reference in the file name...

[Jonmo1]

Would you be able to explain why Indirect will not work on a closed work book?

Sorry, I don't know "Why" it doesn't work on closed workbooks.
I just know it's a given limation of the Indirect Function.

[Mr ZN]

Hi,

Assuming the cell in which your desired variable lies is A1:

=INDIRECT("'Z:\Admin\Afternoon Reports\July\"&$A$1&"\[tradefiledata"&$A$1&".xlsx]Iress Trades - All'!$A5")

Regards

Hi XOR LX,

Nearly there. This morning I got the addin set up and it is working a treat.

I have one problem though, whenever I drag the formula the cell references remain the same, e.g.

When i drag =INDIRECT("'Z:\Admin\Afternoon Reports\July\"&$A$1&"\[tradefiledata"&$A$1&".xlsx]Iress Trades - All'!A5")

The A5 remains the same irrespective of whether I drag it vertically or horizontally.

I have gathered that I will need to insert cell references within the formula to make this happen but cannot seem to get the right combo.

Would you happen to know the structure of the formula I will need to use, i.e. Where the cell reference goes and how to format it?

EDIT: Would you use an array formula for this?

Thanks so much for your help.

[XOR LX]

Hi,

Try:

=INDIRECT("'Z:\Admin\Afternoon Reports\July\"&$A$1&"\[tradefiledata"&$A$1&".xlsx]Iress Trades - All'!"&CELL("address",A5))

Regards

[Mr ZN]

Thanks for getting back to me.

Would it be possible to enter this formula as an array? something like:

{=INDIRECT("'Z:\Admin\Afternoon Reports\July\"&$A$1&"\[tradefiledata"&$A$1&".xlsx]Iress Trades - All'!"&CELL("address",A5:G300))}

Just wanna try pulling all the data at the same time. Currently it is calculating the cells one by one and it take aaaaaages.

Thx for your help again!