ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Copy and Paste Question (https://www.excelbanter.com/excel-discussion-misc-queries/209593-copy-paste-question.html)

SteW
Copy and Paste Question
 
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
Copy and Paste Question
 
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" 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

SteW
Copy and Paste Question
 
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

"Mike H" wrote:

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

Pete_UK
Copy and Paste Question
 
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


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

SteW
Copy and Paste Question
 
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

Pete_UK
Copy and Paste Question
 
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 -

SteW
Copy and Paste Question
 
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 -

Pete_UK
Copy and Paste Question
 
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

On Nov 9, 5:27*pm, stew wrote:
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 -- Hide quoted text -

- Show quoted text -

ShaneDevenshire
Copy and Paste Question
 
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


"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

SteW
Copy and Paste Question
 
you know , i was just thinking that. So thats why

thanks

stew

"ShaneDevenshire" wrote:

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


"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

Pete_UK
Copy and Paste Question
 
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

On Nov 9, 5:45*pm, ShaneDevenshire
wrote:
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



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

SteW
Copy and Paste Question
 
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

"Pete_UK" wrote:

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

On Nov 9, 5:45 pm, ShaneDevenshire
wrote:
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



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

Pete_UK
Copy and Paste Question
 
That's alright, Stewart.

Pete

On Nov 9, 7:14*pm, stew wrote:
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


All times are GMT +1. The time now is 08:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com