Defining a range in a cell so it can be referenced in a formula

[SandyUK]

Can anyone help?

I want to define a range in a cell that refers to a worksheet in a workbook on a network drive so that i can reference the cell in a formula e.g.

A1 = 'G:\information\weekly figures\[south Jan.xls]'!$1:$65536

The theory is that i can let users update A1 which would mean that the VLOOKUP formulas contained in the spreadsheet e.g.

=IF(A10="","0",(VLOOKUP(A10,A1,11,FALSE)))*(VLOOKUP(A10,SALES,11,FALSE))

SALES is a defined range

Any help would be appreciated

Regards

Adrian

[mangesh_yadav]

Try

INDIRECT(A1) instead of A1 in your formula.

- Mangesh

[SandyUK]

Thanks for the help

I have tried INDIRECT but still cant get it to work, though learning about INDIRECT has been really useful in itself. Not sure how to get around the problem as it apears that what i need is something along the lines of

=IF(A10="","0",(VLOOKUP(A10,=INDIRECT(A1),11,FALSE)))*(VLOOKUP(A10,SALES,11 ,FALSE))

but the = in =INDIRECT(A1) is causing an error and just having INDIRECT(A1) is not working

Regards

Adrian