Copy and Paste Question

[stew]

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

 

[Mike H]

Try this

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

Mike

 

[stew]

Hi Mike

The Formula then concludes that C111 is in the work sheet that the Formula
is on. C111 refered to is in the [tour managers spreadsheet.xls]

Any Suggestions gratfully received

Thanks

Stew

Try this

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

Mike

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

 

[Pete_UK]

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'!C"&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

 

[stew]

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

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

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

 

[Pete_UK]

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

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

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

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 -

 

[stew]

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))))

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

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

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.

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 -

 

[Pete_UK]

Glad to see that, Stew.

Not sure why you have an INT and a divide by 1 in there, but if it
works for you ...

Pete

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))))

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:

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

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
:
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
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 -- Hide quoted text -

- Show quoted text -

 

[ShaneDevenshire]

Hi,

I not sure anyone actually answered your question, if I am mistaken, no
offense. Why doesn't the C111 changes? It's inside of quotes, therefore
it's no longer dynamic, it's text.

 

[stew]

you know , i was just thinking that. So thats why

thanks

stew

Hi,

I not sure anyone actually answered your question, if I am mistaken, no
offense. Why doesn't the C111 changes? It's inside of quotes, therefore
it's no longer dynamic, it's text.

--
Thanks,
Shane Devenshire

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

 

[Pete_UK]

I posted:

" ...
- the way you had it the C111 was within the string and thus did not
change,
...."

which I thought answered the question !!

Pete

Hi,

I not sure anyone actually answered your question, if I am mistaken, no
offense.  Why doesn't the C111 changes?  It's inside of quotes, therefore
it's no longer dynamic, it's text.

--
Thanks,
Shane Devenshire

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 -

 

[stew]

Dear Pete

This is so typical of me in that I do not read everything carefully, I go
straight to a formula

You are perfectly correct. You did answer the question on your first post

Apoligies

Best

Stewart

I posted:

" ...
- the way you had it the C111 was within the string and thus did not
change,
...."

which I thought answered the question !!

Pete

Hi,

I not sure anyone actually answered your question, if I am mistaken, no
offense. Why doesn't the C111 changes? It's inside of quotes, therefore
it's no longer dynamic, it's text.

--
Thanks,
Shane Devenshire

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 -

 

[Pete_UK]

That's alright, Stewart.

Pete