Links using Indirect function

[RANJEETBHAGAT]

Hi,

I have created a link to another workbook using indirect function. When such source data file is closed it shows #Ref error. I used manual calculation option but it fails when I refresh the file.
Basically, purpose of creating of link using indirect is only that I want to change sheet reference in formula. Is any option available so that I can make sheet reference dynamic in formula.

Thanks in advance.

 

[shrivallabha]

INDIRECT can't be used with closed workbooks, it won't work. Moreover, if you open another workbook with the same name then it will try to get data from there which could lead to unexpected results.

You can use INDEX which creates a link to the workbook or post your formula here for suggestions.

 

[RANJEETBHAGAT]

Thanks, but I have to change sheet reference every month so I am looking for a solution for that.

 

[shrivallabha]

Any chance that you could post your formula?

 

[RANJEETBHAGAT]

Part of formula
='C:\Users\ranjeet\Desktop\New folder\[sales.xlsx]A'!D6
In the above link
Now next month I've to change sheet reference from A to B and so on.

 

[shrivallabha]

I don't see INDIRECT used in it. Probably I am missing something.

 

[Luke M]

I don't see INDIRECT used in it. Probably I am missing something.

I think OP is wanting a way to automate the change. Initial thought was using INDIRECT to build the formula.

@OP
Is using macros/VB acceptable?

 

[RANJEETBHAGAT]

I have uploaded a sample file.

 

[RANJEETBHAGAT]

It's difficult for me to use VBA

 

[shrivallabha]

There are some suggestions in the link below but they all involve VBA one way or the other.
http://www.contextures.com/xlFunctions05.html

 

[RANJEETBHAGAT]

can I have some short vba code for this.

 

[Luke M]

Debra links to the VB code you can use at the end of this section:
http://www.contextures.com/xlFunctions05.html#RefWkbk