Hi Pete

Got It Myself!!!!!!!

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!"&ADDRESS(111,(INT(COLUMN()/1))))

"Pete_UK" wrote:

Well, you have to convert that COLUMN() into a letter (and you also
have some quotes there that you don't need). If the formula is in
column C and you want to get the data from column C then COLUMN() as
you have written it is fine (it will return 3), but you might like to
make it COLUMN(C1) just to make sure.

To convert it to a letter you can use:

CHAR(COLUMN(C1)+64)

This is fine as long as you will only copy the formula out to column Z
- beyond that it is easier to use R1C1 notation.

So, try this:

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A
$1))-Â*25)&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour
Managers Spreadsheet'!"&CHAR(COLUMN(C1)+64)&ROW(A111))

or this:

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A
$1))-Â*25)&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour
Managers Spreadsheet'!R"&ROW(A111)&"C"&COLUMN(C1))

You might also like to have a look at the ADDRESS function.

Hope this helps.

Pete

On Nov 9, 2:00 pm, stew wrote:
Hi Pete

Its Actually The Column I want to change as i drag along so could i make it

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A

$1))-Â*25)&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour
Managers Spreadsheet'!"&Column()"&ROW(A111))

the formula is in C

hmm Just tried that ,Did Not Work

Onwards and Upwards

Stew



"Pete_UK" wrote:
Stew,

if you want to copy it down and have the C111 effectively become C112,
C113 etc, then you can do this:

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A
$1))-Â*25)&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour
Managers Spreadsheet'"&column()&ROW(A111))

ROW(A111) returns 111 which is then added to the string - the way you
had it the C111 was within the string and thus did not change, but
this way the row parameter changes when you copy it down.

Hope this helps.

Pete

On Nov 9, 12:16 pm, stew wrote:
Hia All

In the following formula when you Copy and drag over a series of cells why
does the C111 NOT CHANGE

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-Â*Â*25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")

thanks for looking

Stewart- Hide quoted text -

- Show quoted text -