|
One cell to many cells (two different work book/sheets)
Greetings...
I would like to know if there is a way to link a single cell from one work sheet to several cells from another worksheet (from a different workbook). Kind of like a one-to-many relationship; however, on the many side, only one of the several cells in this second workbook would have data at a time and it would be this data that would migrate to my main workbook on the "One" side of the relationship. Hope that mades sense... v/r, Steve |
One cell to many cells (two different work book/sheets)
Something like this, for example?:
=IF('full_path[filename2.xls]Sheet1'! A1<"",'full_path[filename2.xls]Sheet1'! A1,IF('full_path[filename2.xls]Sheet2'! A1<"",'full_path[filename2.xls]Sheet2'! A1,IF('full_path[filename2.xls]Sheet3'! A1<"",'full_path[filename2.xls]Sheet3'! A1,'full_path[filename2.xls]Sheet4'!A1))) Put this in your "one" cell, and it will bring data from A1 of either Sheet1, Sheet2, Sheet3 or Sheet4 of filename2.xls, whichever is not blank. Hope this helps. Pete On Aug 28, 3:38*pm, Shek5150 wrote: Greetings... I would like to know if there is a way to link a single cell from one work sheet to several cells from another worksheet (from a different workbook).. * Kind of like a one-to-many relationship; however, on the many side, only one of the several cells in this second workbook would have data at a time *and it would be this data that would migrate to my main workbook on the "One" side of the relationship. Hope that mades sense... v/r, Steve |
One cell to many cells (two different work book/sheets)
Thank you Pete_UK,
Final question, will this (or a variation of it) also work if the referenced cells (from the second workbook) are all located on the same sheet in the second work book. Thanks again and sorry for the bother... v/r, Steve "Pete_UK" wrote: Something like this, for example?: =IF('full_path[filename2.xls]Sheet1'! A1<"",'full_path[filename2.xls]Sheet1'! A1,IF('full_path[filename2.xls]Sheet2'! A1<"",'full_path[filename2.xls]Sheet2'! A1,IF('full_path[filename2.xls]Sheet3'! A1<"",'full_path[filename2.xls]Sheet3'! A1,'full_path[filename2.xls]Sheet4'!A1))) Put this in your "one" cell, and it will bring data from A1 of either Sheet1, Sheet2, Sheet3 or Sheet4 of filename2.xls, whichever is not blank. Hope this helps. Pete On Aug 28, 3:38 pm, Shek5150 wrote: Greetings... I would like to know if there is a way to link a single cell from one work sheet to several cells from another worksheet (from a different workbook).. Kind of like a one-to-many relationship; however, on the many side, only one of the several cells in this second workbook would have data at a time and it would be this data that would migrate to my main workbook on the "One" side of the relationship. Hope that mades sense... v/r, Steve |
One cell to many cells (two different work book/sheets)
No bother - we like answering questions !!
Yes, you do not need to have different sheets as I have shown - just make them all Sheet1 (or your actual name), but obviously the cell references will have to change. There is a restriction on the formula if you are using Excel 2003 or earlier - there is a limit of 7 nested functions (which is what I am using), so if you want to extend the formula beyond 8 different source cells then it will need a slight modification - post back if this is the case. Hope this helps. Pete On Aug 28, 5:35*pm, Shek5150 wrote: Thank you Pete_UK, Final question, will this (or a variation of it) also work if the referenced cells (from the second workbook) are all located on the same sheet in the second work book. Thanks again and sorry for the bother... v/r, Steve "Pete_UK" wrote: Something like this, for example?: =IF('full_path[filename2.xls]Sheet1'! A1<"",'full_path[filename2.xls]Sheet1'! A1,IF('full_path[filename2.xls]Sheet2'! A1<"",'full_path[filename2.xls]Sheet2'! A1,IF('full_path[filename2.xls]Sheet3'! A1<"",'full_path[filename2.xls]Sheet3'! A1,'full_path[filename2.xls]Sheet4'!A1))) Put this in your "one" cell, and it will bring data from A1 of either Sheet1, Sheet2, Sheet3 or Sheet4 of filename2.xls, whichever is not blank. Hope this helps. Pete On Aug 28, 3:38 pm, Shek5150 wrote: Greetings... I would like to know if there is a way to link a single cell from one work sheet to several cells from another worksheet (from a different workbook).. * Kind of like a one-to-many relationship; however, on the many side, only one of the several cells in this second workbook would have data at a time *and it would be this data that would migrate to my main workbook on the "One" side of the relationship. Hope that mades sense... v/r, Steve- Hide quoted text - - Show quoted text - |
One cell to many cells (two different work book/sheets)
Hello again Pete_UK...
Just wanted to say thanks for your help...this did the trick and the following is what I ended up with... =IF([Step_1.xls]Scores!$B5<"",[Step_1.xls]Scores!$B$5,IF([Step_1.xls]Scores!$D$5<"",[Step_1.xls]Scores!$D$5,IF([Step_1.xls]Scores!$F$5<"",[Step_1.xls]Scores!$F$5,IF([Step_1.xls]Scores!$H$5<"",[Step_1.xls]Scores!$H$5,IF([Step_1.xls]Scores!$J$5<"",[Step_1.xls]Scores!$J$5,[Step_1.xls]Scores!$L$5))))) For some reason, Excel wouldn't recognize the "Full_Path_Name"; however, once I dropped them ... it worked wonderfully ... so thanks for the help... Steve "Pete_UK" wrote: No bother - we like answering questions !! Yes, you do not need to have different sheets as I have shown - just make them all Sheet1 (or your actual name), but obviously the cell references will have to change. There is a restriction on the formula if you are using Excel 2003 or earlier - there is a limit of 7 nested functions (which is what I am using), so if you want to extend the formula beyond 8 different source cells then it will need a slight modification - post back if this is the case. Hope this helps. Pete On Aug 28, 5:35 pm, Shek5150 wrote: Thank you Pete_UK, Final question, will this (or a variation of it) also work if the referenced cells (from the second workbook) are all located on the same sheet in the second work book. Thanks again and sorry for the bother... v/r, Steve "Pete_UK" wrote: Something like this, for example?: =IF('full_path[filename2.xls]Sheet1'! A1<"",'full_path[filename2.xls]Sheet1'! A1,IF('full_path[filename2.xls]Sheet2'! A1<"",'full_path[filename2.xls]Sheet2'! A1,IF('full_path[filename2.xls]Sheet3'! A1<"",'full_path[filename2.xls]Sheet3'! A1,'full_path[filename2.xls]Sheet4'!A1))) Put this in your "one" cell, and it will bring data from A1 of either Sheet1, Sheet2, Sheet3 or Sheet4 of filename2.xls, whichever is not blank. Hope this helps. Pete On Aug 28, 3:38 pm, Shek5150 wrote: Greetings... I would like to know if there is a way to link a single cell from one work sheet to several cells from another worksheet (from a different workbook).. Kind of like a one-to-many relationship; however, on the many side, only one of the several cells in this second workbook would have data at a time and it would be this data that would migrate to my main workbook on the "One" side of the relationship. Hope that mades sense... v/r, Steve- Hide quoted text - - Show quoted text - |
One cell to many cells (two different work book/sheets)
Hi, thanks for feeding back, and glad you got it to work.
If you have the Step_1.xls file open at the same time as the other file then you don't need to put in the full path, even though Excel will remember it. See what happens to the formula when you close the Step_1.xls window. Pete On Aug 28, 7:58*pm, Shek5150 wrote: Hello again Pete_UK... Just wanted to say thanks for your help...this did the trick and the following is what I ended up with... =IF([Step_1.xls]Scores!$B5<"",[Step_1.xls]Scores!$B$5,IF([Step_1.xls]Score*s!$D$5<"",[Step_1.xls]Scores!$D$5,IF([Step_1.xls]Scores!$F$5<"",[Step_1.x*ls]Scores!$F$5,IF([Step_1.xls]Scores!$H$5<"",[Step_1.xls]Scores!$H$5,IF([S*tep_1.xls]Scores!$J$5<"",[Step_1.xls]Scores!$J$5,[Step_1.xls]Scores!$L$5))*))) For some reason, Excel wouldn't recognize the "Full_Path_Name"; however, once I dropped them ... it worked wonderfully ... so thanks for the help.... Steve "Pete_UK" wrote: No bother - we like answering questions !! Yes, you do not need to have different sheets as I have shown - just make them all Sheet1 (or your actual name), but obviously the cell references will have to change. There is a restriction on the formula if you are using Excel 2003 or earlier - there is a limit of 7 nested functions (which is what I am using), so if you want to extend the formula beyond 8 different source cells then it will need a slight modification - post back if this is the case. Hope this helps. Pete On Aug 28, 5:35 pm, Shek5150 wrote: Thank you Pete_UK, Final question, will this (or a variation of it) also work if the referenced cells (from the second workbook) are all located on the same sheet in the second work book. Thanks again and sorry for the bother... v/r, Steve "Pete_UK" wrote: Something like this, for example?: =IF('full_path[filename2.xls]Sheet1'! A1<"",'full_path[filename2.xls]Sheet1'! A1,IF('full_path[filename2.xls]Sheet2'! A1<"",'full_path[filename2.xls]Sheet2'! A1,IF('full_path[filename2.xls]Sheet3'! A1<"",'full_path[filename2.xls]Sheet3'! A1,'full_path[filename2.xls]Sheet4'!A1))) Put this in your "one" cell, and it will bring data from A1 of either Sheet1, Sheet2, Sheet3 or Sheet4 of filename2.xls, whichever is not blank. Hope this helps. Pete On Aug 28, 3:38 pm, Shek5150 wrote: Greetings... I would like to know if there is a way to link a single cell from one work sheet to several cells from another worksheet (from a different workbook).. * Kind of like a one-to-many relationship; however, on the many side, only one of the several cells in this second workbook would have data at a time *and it would be this data that would migrate to my main workbook on the "One" side of the relationship. Hope that mades sense... v/r, Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
One cell to many cells (two different work book/sheets)
Hey,
That was pretty cool...when I closed the Step_1.xls window...Excel automatically entered the full path number... Thanks again.. Steve "Pete_UK" wrote: Hi, thanks for feeding back, and glad you got it to work. If you have the Step_1.xls file open at the same time as the other file then you don't need to put in the full path, even though Excel will remember it. See what happens to the formula when you close the Step_1.xls window. Pete On Aug 28, 7:58 pm, Shek5150 wrote: Hello again Pete_UK... Just wanted to say thanks for your help...this did the trick and the following is what I ended up with... =IF([Step_1.xls]Scores!$B5<"",[Step_1.xls]Scores!$B$5,IF([Step_1.xls]ScoreÂ*s!$D$5<"",[Step_1.xls]Scores!$D$5,IF([Step_1.xls]Scores!$F$5<"",[Step_1.xÂ*ls]Scores!$F$5,IF([Step_1.xls]Scores!$H$5<"",[Step_1.xls]Scores!$H$5,IF([SÂ*tep_1.xls]Scores!$J$5<"",[Step_1.xls]Scores!$J$5,[Step_1.xls]Scores!$L$5))Â*))) For some reason, Excel wouldn't recognize the "Full_Path_Name"; however, once I dropped them ... it worked wonderfully ... so thanks for the help.... Steve "Pete_UK" wrote: No bother - we like answering questions !! Yes, you do not need to have different sheets as I have shown - just make them all Sheet1 (or your actual name), but obviously the cell references will have to change. There is a restriction on the formula if you are using Excel 2003 or earlier - there is a limit of 7 nested functions (which is what I am using), so if you want to extend the formula beyond 8 different source cells then it will need a slight modification - post back if this is the case. Hope this helps. Pete On Aug 28, 5:35 pm, Shek5150 wrote: Thank you Pete_UK, Final question, will this (or a variation of it) also work if the referenced cells (from the second workbook) are all located on the same sheet in the second work book. Thanks again and sorry for the bother... v/r, Steve "Pete_UK" wrote: Something like this, for example?: =IF('full_path[filename2.xls]Sheet1'! A1<"",'full_path[filename2.xls]Sheet1'! A1,IF('full_path[filename2.xls]Sheet2'! A1<"",'full_path[filename2.xls]Sheet2'! A1,IF('full_path[filename2.xls]Sheet3'! A1<"",'full_path[filename2.xls]Sheet3'! A1,'full_path[filename2.xls]Sheet4'!A1))) Put this in your "one" cell, and it will bring data from A1 of either Sheet1, Sheet2, Sheet3 or Sheet4 of filename2.xls, whichever is not blank. Hope this helps. Pete On Aug 28, 3:38 pm, Shek5150 wrote: Greetings... I would like to know if there is a way to link a single cell from one work sheet to several cells from another worksheet (from a different workbook).. Kind of like a one-to-many relationship; however, on the many side, only one of the several cells in this second workbook would have data at a time and it would be this data that would migrate to my main workbook on the "One" side of the relationship. Hope that mades sense... v/r, Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
One cell to many cells (two different work book/sheets)
You're welcome, Steve - thanks for feeding back.
Pete On Aug 29, 12:47*pm, Shek5150 wrote: Hey, That was pretty cool...when I closed the Step_1.xls window...Excel automatically entered the full path number... Thanks again.. Steve |
| All times are GMT +1. The time now is 10:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com