What is the best way to associate two Excel workbooks where Spreadsheet A can reference selected lines or fields shown on Spreadsheet B.

If you were to modify the sentence that says "shown on line 57" to instead reference the actual row the line is on like so:
="shown on line " & ROW('Spreadsheet B'!A57)

No matter where the item on Spreadsheet B was moved to - even if a different column - it would return the correct result.

-Glenn

If you name your cell or range, instead of using the cell address or reference, it should maintain the correct link when rows are added or removed.

See screenshot for example where you would type to name a cell.


then when you reference the cell you use the name that you gave it instead of the address reference.
namedCell.jpg

Glenn Ray: Thank you for your comment. Many of the lines contain comments.  We would like the comment on that Spreadsheet B to be repeated in a particular location on Spreadsheet A.

Example.   D12 on Spreadsheet B says:    "Used by the Washington DC Boy Scouts Group 16M12"

We would like T7 on Spreadsheet A to automatically say the same: "Used by the Washington DC Boy Scouts Group 16M12"

There are areas where the reference in Spreadsheet B for example: $12,307.  It would be good for the Spreadsheet A to pickup the amount and place it in the correct area.

***************************************************************************************************************

Ken Butters: Thank you for your comment.  There are about nine tabs on the Spreadsheet B and each tab contains information from Columns A through N and lines 1 through 87.  Spreadsheet A has about six tabs.  If we rename many columns and areas with reference data, I think that it would be too involved.  If we only had five or ten special areas, it would be ok to name them and I agree that the reference would be great.  Is there a quick way to rename the columns and rows?

 *****************************************

If this is very involved, please let me know.  I am still learning Excel and may not have the knowledge to make many reorganizations.  Thanks for any suggestions that you have.

sherman6789, We cross-posted here! :-)

In your specific example, the formula in cell T7 on "Spreadsheet A" would be
='Spreadsheet B'!D12

That's it.  You can actually create this automatically by just entering an equal sign (=) in cell T7, then navigating over to "Spreadsheet B" and clicking on cell T7 then pressing [Enter].  Excel will fill in the sheet and cell reference automatically.  This is true whether you're dealing with text or a number:  an absolute copy of the value will be shown in the cell.

sherman6789, We cross-posted here! :-)

 In your specific example, the formula in cell T7 on "Spreadsheet A" would be
 ='Spreadsheet B'!D12

 That's it.  You can actually create this automatically by just entering an equal sign (=) in cell T7, then navigating over to "Spreadsheet B" and clicking on cell T7 then pressing [Enter].  Excel will fill in the sheet and cell reference automatically.  This is true whether you're dealing with text or a number:  an absolute copy of the value will be shown in the cell.

 I've modified my example workbook to show your examples.  Note that I changed my original spreadsheet name from "Spreadsheet B" to "Spreadsheet X" and the original "sentence formula" automatically renamed to adjust.
EE-cross-sheet-referencing.xlsx

Glenn Ray:  This looks great.  This is working on two tabs in the same spreadsheet.  Does it work the same way with two different spreadsheets?  We have two spreadsheets being worked on by to sets of people.  Both spreadsheets are on a common directory in the office.

Thanks again, Glen.  You are right.  I was typing while you were posting your update.  I must leave the office now and will not be able to answer until Monday.

I think that we are on the right track.

Remember that in addition to copying the amounts, in some areas, we have to copy the multi-word comments.

Example, The sentence or phrase: "Used by the Washington DC Boy Scouts Group 16M12" may need to be copied on the second spreadsheet.

I will practice what you have given me and update my information this weekend if I get on the Internet or on Monday.

Thanks again.

Sorry Glen,  I just saw that you answered my second question about copying the phrase and sentence.  I did not see the example that you gave with the yellow highlights until  I was about to close the file.   Thanks.

Hope this helps you with your workbooks.  If any of my answers are satisfactory, please click the "Accept this solution" link above the appropriate answer.  This will help with future users and searches and properly closes the question.

Have a good weekend.

-Glenn

Attn:  GLEN

Thank you for your assistance.  

It is correct that there are two separate spreadsheet workbooks.  We have decided to make sure that both spreadsheets are in the same directory on the shared system.  That cuts out the need to specity the directory and location.  As a result, only the spreadsheet name, the TAB and the line column/row need to be placed in the formula.

Everything is now working with the exception of getting the Row and Column number (A6) to print on Spreadsheet A in the reference spot.

Example:  "As shown on line A57".

This is what I wrote:
="As shown on Line " & ROW([Spreadsheet B.xlsx]'NTPD-TRT'!A6)

Spreadsheet Name:   Spreadsheet B.xlsx

Tab Name:   NTPD-TRT

Row:   A6

I would like the statement "As shown on Line A6" to automatically be placed in the reference field on Spreadsheet A.

Otherwise, we are very happy with the results and the time savings that your suggestions have caused.

Thanks again.

Hello again Glen.

Each time I try to make the changes, when I hit return, the entire formula appears in the cell.
I gave you the name "Spreadsheet A" to make it easier for us to reference.  After you send your suggestion to me I have to change it to the real name. That might be a mistake.  This time I am giving you the real name of the SpreadSheet and the real name of the TAB.  

Spread Sheet A real name:   RichTest
Real TAB Name:                    2012 Program Plan
row/column:                           N52

Spread Sheet B real Name:   OVC2014Central
TAB Name:                            NTPD-TRT
row/column:                           A6


This is what I changed it to and put in the "RichTest" Spreadsheet (Spreadsheet A):

="Line "& ADDRESS(ROW('[(OVC2014Central.xlsx)NTPD-TRT]Sheet1'!A6),COLUMN('[(OVC2014Central.xlsx)NTPD-TRT]Sheet1'!A6),4,1)

Is Sheet1 the same as the Tab Name?

Thanks

Yes, "Sheet1" is what you call the tab name.

Again, just to refresh terminology:
RichTest.xlsx, OVC2014Central.xlsx - workbooks
Sheet1, NTPD-TRT - sheets/worksheets

Thanks Glen Ray for your assistance.  Everything works as expected.  After you explained that "Sheet1" is the same as the name of a "Tab" I made a change to one of your suggestions.  (SEE BELOW)

="As shown in cell "& ADDRESS(ROW('[Spreadsheet B.xlsx]Sheet1'!A6),COLUMN('[Spreadsheet B.xlsx]Sheet1'!A6),4,1)

After I changed the two areas that listed "Sheet1" to "NTPD-TRT" it began to work as expected.  However, the "as shown on" results are:
"Line A6" rather than "Line 6".  However, we can live with that.  

What does ",4,1" at the end of the line mean?

Below is the way the line looks now:
="Line "& ADDRESS(ROW('[(OVC2014Central.xlsx)]NTPD-TRT'!A6),COLUMN('[(OVC2014Central.xlsx)]NTPD-TRT'!A6),4,1)

I will end this thread after I get your last reply.

THIS IS GREAT.  I made changes to the fields.  The other spreadsheet updated itself immediately.  I added three lines above and the information on line 6 moved down to line 9.  I then made some changes and the other spreadsheet immediately updated itself as expected.

The change that you made to the reference line also worked.  It now says "Line 6" instead of "Line 6A.  However, when I added the three line the reference continued to say "Line 6" instead of "Line 9". Can that be updated automatically?

This is the last update needed.  Thanks.

Oops...those inside parantheses shouldn't have been in there.  Try this instead:
="Line "&ROW('[OVC2014Central.xlsx]NTPD-TRT'!A6)

I copied the line without the inside parantheses but the results remained "Line 6" even after I added several lines which pushed the subject line to "Line 12".  The reference still remained "Line 6".  Should there be one or more "$" added somewhere.  I think that I remember reading shomewhere that the "$" reserves a location or something like that. ($A$6)

I saved both spreadsheets.  When I opened them I did notice the change that you told me about yesterday came true.  The "Directory" and "Sub-directory" references have been added to the formula.

I feel that I am now taking up too much of your time and you need to be helping others, like me.  If you can think of any other change that I should make, please let me know.  If not, I will close this thread with my greatest appreciation.

I tried the above formula with both relative (A6) and absolute ($A$6) addressing and each of them worked when I moved the value originally in OVC2014Central.xlsx, sheet "NTPD-TRT", cell A6.  I inserted rows, inserted columns, cut and pasted the cell to a new row and it worked.

It is important for both workbooks to be open, otherwise if changes are made to OVC2014Central, then RichTest won't pick up the new cell references.

If you have no further questions, can you select one (or more) of my answers by clicking the "Accept as solution" or "Accept multiple solutions" link on top of each answer?  That will properly close the question.

Thanks,
Glenn

THANK YOU GLENN!

I am in the process of closing this thread.  I want to mention that last week everything worked except the field that should change to indicate that the line has been moved from "Line 6" to another row.  However, something must have mysteriously and automatically changed over the weekend; because, when I came in this morning, I noticed that it is now working completely like your sample.  Last week, I tried rebooting and turning off the PC and the "Line ?" would not update, no matter how I modified the associated line numbers.  Well, this morning everything works completely.

It will be difficult for me to select the expert comments that are important to this project.  I will pick what I think added most to solving the problem and suggest that anyone who reads this link should pay close attention to ALL of the expert's comments.  Each one of them greatly help me to solve the problem.

Glen, you should be a teacher and I hope that is part of your training and that you continue to help those of us who need assistance.  I plan to send a special note to the Experts-Exchange organization to thank them for providing the connections to Experts like you, to let them know how your patience and completeness has paid off and that my project now works greatly.

Sincerely,

sherman6789

Thank you for the kind comments.  This was a good lesson for both of us, as I had to learn to "listen" to what you were actually needing to resolve your issue.  It was challenging since I could not actually see your environment, but I hope that, in the end, we both learned something from the experience.

Regards,
-Glenn

The solution to this problem was handled excellently by Glenn Ray.  I have chosen to give 100 points each to five of the expert responses from GR (500 points total).  Each of these responses played a very important part in figuring out the solution to the problem.  I recommend that anyone having simular problems should read this entire tread   Your degree of knowledge of Excel may allow you to skip some of the questions and their responses.  This has been an extremely educational experience for me and my co-workers.  Without a doubt, this will come in handy for future projects and will save us many-many processing hours and increase accuracy.  

Thanks to Glen Ray and Experts-Exchange.

-Sherman6789