Getting a range from an array formula contained in a cell

[kayard]

Hi to everyone.

Thanks to the help of many I have written a macro that takes a range of cells (input range) and execute some code using the values contained in each cell. I have a cell (let's call it Sheet2!C1) which contain text and the text is the exact name of the input range (for example Sheet1!A1:A100). I process the text in the macro and set a range object to Sheet1!A1:A100 and then I use the newly created object. Now the problem is that whenever I change something in Sheet1 that changes the position of my input range (say that I add a collum to the left of collumn A) I must remember to change the text inside cell Sheet2!C1.

So I would like to put inside Sheet2!C1 an array that references the input range ( I would do that writing = the selecting the range and then CTRL + ENTER for the array formula) This way the reference is updated if I make any change in Sheet1.

So the question is: how do I create a range object making use of cell C1 that contains an array formula that actually is something like this {=Sheet1:A1:A100} ?

Thanks in advance for your help

[shg]

Create a named range that contains the data, and put the range name in Sheet2!C1. Then reference the range using INDIRECT(Sheet2!C1).

[kayard]

isn't there a way without using a named range ?