| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy/Paste Question | Excel Discussion (Misc queries) | |||
| Copy/Paste question | Excel Worksheet Functions | |||
| Question about copy/paste functions | Excel Worksheet Functions | |||
| Question about Copy/Paste functions | Excel Discussion (Misc queries) | |||
| Copy Paste Question | Excel Discussion (Misc queries) | |||
Linear Mode
