How to refenence ranges on a closed workbook

[GjArt]

I would like to know how I can reference a range of cells in a closed
workbook, A, that may be updated daily, to cells in an open workbook, B, that
would be opened weekly. Would the weekly workbook, B, be updated daily
(while closed) from the daily workbook, A, or would (can) it update only when
opened each week?
I am referencing a yearly calander of vacation days, A, to a weekly work
schedule, B.

[Dave Peterson]

Workbook B has links to Workbook A.

Workbook B has to be opened for the links to workbook A to be refreshed.

If you have workbook C that points at workbook B that depends on the links
getting refreshed, you'll either have to change the links in C to point at
Workbook A (or just open workbook b, refresh, save, and close).



GjArt wrote:
>
> I would like to know how I can reference a range of cells in a closed
> workbook, A, that may be updated daily, to cells in an open workbook, B, that
> would be opened weekly. Would the weekly workbook, B, be updated daily
> (while closed) from the daily workbook, A, or would (can) it update only when
> opened each week?
> I am referencing a yearly calander of vacation days, A, to a weekly work
> schedule, B.

--

Dave Peterson

[GjArt]

Thanks Dave.
I understand that B links to A and would have to be opened to be refreshed
with new data. I'm not really sure how C fits in. Also, I've followed Help
instructions to try to make links, but when I choose the edit menu, "links"
are grayed out-not available. How can I get started linking A & B.
GjArt


"Dave Peterson" wrote:

> Workbook B has links to Workbook A.
>
> Workbook B has to be opened for the links to workbook A to be refreshed.
>
> If you have workbook C that points at workbook B that depends on the links
> getting refreshed, you'll either have to change the links in C to point at
> Workbook A (or just open workbook b, refresh, save, and close).
>
>
>
> GjArt wrote:
> >
> > I would like to know how I can reference a range of cells in a closed
> > workbook, A, that may be updated daily, to cells in an open workbook, B, that
> > would be opened weekly. Would the weekly workbook, B, be updated daily
> > (while closed) from the daily workbook, A, or would (can) it update only when
> > opened each week?
> > I am referencing a yearly calander of vacation days, A, to a weekly work
> > schedule, B.
>
> --
>
> Dave Peterson
>

[cvolkert]

Dave, it is pretty easy to link files. The Link item under edit is really just to be used to maintain links that are already set up. All you have to do to create links is open both files A and B at the same time. In file B, go to the cell you want to create the link in, Type and equals sign then click over to file A (under Windows menu if you're not familiar) and click on the cell in file A you want to link to and hit enter - it should take you back to file B and the cell should now have a formula listing the file name, sheet name and cell address in file A that you linked to. Let us know if you need more help, Chad

[Dave Peterson]

And one more way to create formulas that link to other worksheets.

Open both workbooks
select a cell on Sheet1 of Book1.xls
edit|copy
select a cell on sheet2 of Book2.xls
edit|paste special|click the paste link button.

This will result in a formula that looks like:
=[book1.xls]Sheet1!$A$1
(excel will adjust the formula when book1.xls closes)

If book1.xls, sheet1!a1 is empty, you'll see a 0.

If you want to avoid this, you can modify the formula:

=if([book1.xls]Sheet1!$A$1="","",[book1.xls]Sheet1!$A$1)



And you can also the technique that cvolkert gave to make a formula that looks
like:

=if(a1=17,b1,[book1.xls]Sheet1!$A$1)

It doesn't just have to be a simple link that returns the value:

=vlookup(a1,[book1.xls]Sheet1!$A:$B,2,false)




GjArt wrote:
>
> Thanks Dave.
> I understand that B links to A and would have to be opened to be refreshed
> with new data. I'm not really sure how C fits in. Also, I've followed Help
> instructions to try to make links, but when I choose the edit menu, "links"
> are grayed out-not available. How can I get started linking A & B.
> GjArt
>
> "Dave Peterson" wrote:
>
> > Workbook B has links to Workbook A.
> >
> > Workbook B has to be opened for the links to workbook A to be refreshed.
> >
> > If you have workbook C that points at workbook B that depends on the links
> > getting refreshed, you'll either have to change the links in C to point at
> > Workbook A (or just open workbook b, refresh, save, and close).
> >
> >
> >
> > GjArt wrote:
> > >
> > > I would like to know how I can reference a range of cells in a closed
> > > workbook, A, that may be updated daily, to cells in an open workbook, B, that
> > > would be opened weekly. Would the weekly workbook, B, be updated daily
> > > (while closed) from the daily workbook, A, or would (can) it update only when
> > > opened each week?
> > > I am referencing a yearly calander of vacation days, A, to a weekly work
> > > schedule, B.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson