How do I link to worksheet in another book?

[JPaterson]

I have two Excel workbooks that I use on a regular basis. One is an
inventory collection of my stuff, like music CDs, video games, books,
DVDs, computer software, etc.. Another one is music information, such
as songs with lyrics, etc..

What I want to do is create a linked worksheet in my music workbook
that points to the Music CD collection sheet of my inventory book. I
don't want to link to the entire second workbook, just the one sheet of
that workbook.

This is my inventory workbook:

http://www.jpatworld.com/excel1.jpg

This is my music info book (some of it's business, so I blocked out the
tabs):

http://www.jpatworld.com/excel2.jpg

In the inventory book, you'll see a tab called "Music CDs". What I
want is to create a worksheet in the "Music Info book" that links
directly to the "Music CD" sheet of the other workbook. So, whenever I
update the "Music CD" sheet, and I click on that sheet in the -other-
book, I see the updated sheet.

Hopefully you guys know what I'm trying to say. I want to create a
sheet that links to a sheet in another workbook, basically.

 

[BruceP]

If you're only going to be posting in one direction (from Book1 t
Book2, for instance), you can make a page in Book2 with this formula i
A1 - then copied to the entire range you need:

=IF(ISBLANK([Book1.xls]Sheet1!A2),"",[Book1.xls]Sheet1!A2)

That solution might be a bit clunky, but it will automatically updat
Book2 whenever you update the corresponding cells in Book1. I'm sure
macro (or event-driven VBA code) would also work.

Hope this helps

 

[JPaterson]

It helps a little, thanks.

Your formula worked, but is there I way I can use that forumla to lin
to the entire sheet, without specifying a range of cells? I want th
entire sheet to be updated in the other book, so I'd prefer not t
specify cells. That way, if I create new rows or columns, I won't hav
to update the formula

 

[don]

It helps a little, thanks.

Your formula worked, but is there I way I can use that forumla to link
to the entire sheet, without specifying a range of cells? I want the
entire sheet to be updated in the other book, so I'd prefer not to
specify cells. That way, if I create new rows or columns, I won't have
to update the formula.

Its not a very technical way of doing it but I just did what you are
asking for with a macro.

Try selecting sheet and then use paste link data into new sheet. Start
Macro recorder and select first cell containing paste link, drag to
outside of paste area and drag down also. Effectively re copying your
paste data. Select A1 to deselect area and stop macro recorder. Assign
macro to a button on toolbar and away you go.

Each time your source changes layout run your macro on your target and
any new column row should be added.

Don