One cell to many cells (two different work book/sheets)

[Shek5150]

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

 

[Pete_UK]

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

 

[Shek5150]

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

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

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

 

[Pete_UK]

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

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

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.

- Show quoted text -

 

[Shek5150]

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

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

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

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.

On Aug 28, 3:38 pm, Shek5150 <[email protected]>
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...

Steve- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

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

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

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

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 inthe
second work book.
Thanks again and sorry for the bother...
v/r,
Steve
:
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 <[email protected]>
wrote:
Greetings...
I would like to know if there is a way to link a single cell fromone 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 atime  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 -

 

[Shek5150]

Hey,

That was pretty cool...when I closed the Step_1.xls window...Excel
automatically entered the full path number...

Thanks again..

Steve

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

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

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.

On Aug 28, 5:35 pm, Shek5150 <[email protected]>
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

:
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.

On Aug 28, 3:38 pm, Shek5150 <[email protected]>
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...

Steve- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

You're welcome, Steve - thanks for feeding back.

Pete