Reading of filename from cell

[Vinceaam]

Hi Everyone and thanks for any help in advance.

I have an excel spreadsheet in which I am linking to another spreadsheet. From this worksheet I have to return the SUM of an OFFSET. Because of the limitations of the OFFSET function it is ineffective if you change the linked workbook and the workbooks are closed.

I am therefore trying to use the INDIRECT function to retrieve the linked workbook file name which I have output to cell A1 in the relevant worksheet. the problem is that when I use this INDIRECT function it reads the "\" in the full file name as "|". I am not sure why this is occuring.

e.g.

Cell A1 = C:\Users\Vince\desktop\[test.xlsx]

but when i run the evaluate formula on the INDIRECT funtion it reads it as

INDIRECT("C:|Users|Vince|dektop|[test.xlsx])



Any assistance would be much appreciated.


Regards


Vince

 

[Firefly2012]

You do realise INDIRECT won't work with closed workbooks either?

 

[Vinceaam]

Yes.

That is why I am outputting the filename of the current Linked workbook to cell A1. So that this can be read via the INDIRECT function.

That is I am trying to get round the fact that the file name will not automatically change with these functions but making it reference the correct filempath name which is in Cell A1.

I think if I can just get it to read the text as it should all would be fine.

 

[Firefly2012]

I'm just not understanding that if INDIRECT won't work with a closed workbook, why are you using INDIRECT? Or do you mean it to only work when the workbook is open? In which case why are you bothering with the full path?

 

[CharlesChuckieCharles]

Further to Fireflys reply

INDIRECT will only work for books opened in the same instance of Excel also.

you'll continue to get #REF! while the content of the referenced cell does not point to a valid item (cell range book etc)

 

[Vinceaam]

Sorry.

OFFSET does not work with closed workbooks, therefore I am trying to make use of the INDIRECT function as I know the column I want in the closed workbook and can make use of the MATCH function to specify the range. So the INDIRECT Function as it basically just creates a reference can work with closed work book.

Let me copy the formula below, which may help you understand;

Note Cell A1 = Filename of linked workbook = C:\Users\Vince\desktop\[test.xlsx]

This is the formula i'm working with;

=SUM(INDIRECT("'"&A1&"NAV (Pool)'!$X"&MATCH(B64,'C:\Users\Vince\desktop]NAV (Pool)'!$J:$J,0)&":X"&(MATCH(B65,'C:\Users\Vince\desktop]NAV (Pool)'!$J:$J,0)-1)))



Because when i am using the match function I do not need to alter the range it automatically changes the filename when i change the linked workbook.

When I run the evaluate formula I get to the following;


-- removed inline image ---

as you can see there are verticle lines as oppose to "\" in the full file path.

when I click evaluate once more I get a reference error.



Thank you for your help.

 

[Vinceaam]

Ok.

So i've just tried to eliminate the INDIRECT function altogether and now have the following formula. However, the same issue appears to occur if I read the file name from a cell.

=SUM(H1&MATCH(B64,'C:\Users\Vince\desktop\[test.xlsx]NAV (Pool)'!$J:$J,0)&":X"&(MATCH(B65,'C:\Users\Vince\desktop\[test.xlsx]NAV (Pool)'!$J:$J,0)-1))

 

[Vinceaam]

Anyone?

 

[Firefly2012]

What is the formula you wish to end up with (I mean if you wrote it statically in a cell, what would it look like)?

You won't be able to build your workbook filename & path dynamically (as that requires INDIRECT which doesn't work with closed workbooks as already discussed) but you may have other options to construct a formula. Depends on what the formula needs to do though.

 

[Vinceaam]

The formula I wish to end up with is bascially;

=SUM('Test.xls'!X10:X15)

Where the range has been specified using the match function which work perfectly fine.

Obviously I want this to work with a closed workbook so the filename would have the full path.

Thanks