Need help with linking sheets.

Hi all,

I have a problem I was hoping someone could help me with. What I'm doing is
using a =sumif() to pull some numbers from different files. On the files
that I'm pulling the information from I have two ranges named Code and
Current and we'll call the actual file names Table.xls, Table1.xls,
Table2.xls, etc. On the file that I'm trying to pull the information into I
have =sumif('Table.xls'!Code,A5,'Table.xls'!Current) in C5.
This pulls the information fine but I have 300 rows I have this formula in.
I want to be able to just put the file name like Table.xls in C4 and have it
update all the rows without having to change the formula and copy it down
each time I change the file name. That way I could just change the file name
from Table.xls to Table1.xls in C4 and have everything update automatically.
I tried =sumif($C$4!Code,A5,$C$4!Current) but that wasn't working work. Does
anybody know of a way to do this? Thanks for any help you can offer.

ww

Have you tried using the Edit | Links options off the menu?

You should be able to tell Excel to change all the links from one book to
another



"ww" wrote:

> Hi all,
>
> I have a problem I was hoping someone could help me with. What I'm doing is
> using a =sumif() to pull some numbers from different files. On the files
> that I'm pulling the information from I have two ranges named Code and
> Current and we'll call the actual file names Table.xls, Table1.xls,
> Table2.xls, etc. On the file that I'm trying to pull the information into I
> have =sumif('Table.xls'!Code,A5,'Table.xls'!Current) in C5.
> This pulls the information fine but I have 300 rows I have this formula in.
> I want to be able to just put the file name like Table.xls in C4 and have it
> update all the rows without having to change the formula and copy it down
> each time I change the file name. That way I could just change the file name
> from Table.xls to Table1.xls in C4 and have everything update automatically.
> I tried =sumif($C$4!Code,A5,$C$4!Current) but that wasn't working work. Does
> anybody know of a way to do this? Thanks for any help you can offer.
>
> ww

I haven't tried that because the file I'm pulling everything into is a
summary page so it will generally have links to all the different Table.xls
files that I may use. I might only have Table1.xls through Table6.xls. On my
summary page. Others might use the summary page and just want Table1,
Table8, and Table10. So they'd have to change the formulas for all the rows
to get Table8 and Table10 since they weren't used beforehand. I thought if
they could just enter the a new file name in one cell instead of copying it
to all cells it would be easier. I am thinking of this right or am I missing
something. I'm not real familiar with links so. Thanks again.

"Duke Carey" wrote:

> Have you tried using the Edit | Links options off the menu?
>
> You should be able to tell Excel to change all the links from one book to
> another
>
>
>
> "ww" wrote:
>
> > Hi all,
> >
> > I have a problem I was hoping someone could help me with. What I'm doing is
> > using a =sumif() to pull some numbers from different files. On the files
> > that I'm pulling the information from I have two ranges named Code and
> > Current and we'll call the actual file names Table.xls, Table1.xls,
> > Table2.xls, etc. On the file that I'm trying to pull the information into I
> > have =sumif('Table.xls'!Code,A5,'Table.xls'!Current) in C5.
> > This pulls the information fine but I have 300 rows I have this formula in.
> > I want to be able to just put the file name like Table.xls in C4 and have it
> > update all the rows without having to change the formula and copy it down
> > each time I change the file name. That way I could just change the file name
> > from Table.xls to Table1.xls in C4 and have everything update automatically.
> > I tried =sumif($C$4!Code,A5,$C$4!Current) but that wasn't working work. Does
> > anybody know of a way to do this? Thanks for any help you can offer.
> >
> > ww

Well, you can use the Indirect() function to pull the workbook name(s) into a
formula, but there are a couple of drawbacks.

First is you'll have to recreate all your formulas to build up the link name
& cell address as a text string

Second (and I'm not positive about this) I think the Indirect function will
pull data only from OPEN workbooks, whereas a direct link can pull data from
a closed workbook.

Respond if you want to try it and need help

"ww" wrote:

> I haven't tried that because the file I'm pulling everything into is a
> summary page so it will generally have links to all the different Table.xls
> files that I may use. I might only have Table1.xls through Table6.xls. On my
> summary page. Others might use the summary page and just want Table1,
> Table8, and Table10. So they'd have to change the formulas for all the rows
> to get Table8 and Table10 since they weren't used beforehand. I thought if
> they could just enter the a new file name in one cell instead of copying it
> to all cells it would be easier. I am thinking of this right or am I missing
> something. I'm not real familiar with links so. Thanks again.
>
> "Duke Carey" wrote:
>
> > Have you tried using the Edit | Links options off the menu?
> >
> > You should be able to tell Excel to change all the links from one book to
> > another
> >
> >
> >
> > "ww" wrote:
> >
> > > Hi all,
> > >
> > > I have a problem I was hoping someone could help me with. What I'm doing is
> > > using a =sumif() to pull some numbers from different files. On the files
> > > that I'm pulling the information from I have two ranges named Code and
> > > Current and we'll call the actual file names Table.xls, Table1.xls,
> > > Table2.xls, etc. On the file that I'm trying to pull the information into I
> > > have =sumif('Table.xls'!Code,A5,'Table.xls'!Current) in C5.
> > > This pulls the information fine but I have 300 rows I have this formula in.
> > > I want to be able to just put the file name like Table.xls in C4 and have it
> > > update all the rows without having to change the formula and copy it down
> > > each time I change the file name. That way I could just change the file name
> > > from Table.xls to Table1.xls in C4 and have everything update automatically.
> > > I tried =sumif($C$4!Code,A5,$C$4!Current) but that wasn't working work. Does
> > > anybody know of a way to do this? Thanks for any help you can offer.
> > >
> > > ww

Okay. Well I don't think I'll worry about the indirect() function right now.
I just learned that sumif() only pulls information from open workbooks as
well so I'll have to find a way around that to maybe I can use Vlookup().

"Duke Carey" wrote:

> Well, you can use the Indirect() function to pull the workbook name(s) into a
> formula, but there are a couple of drawbacks.
>
> First is you'll have to recreate all your formulas to build up the link name
> & cell address as a text string
>
> Second (and I'm not positive about this) I think the Indirect function will
> pull data only from OPEN workbooks, whereas a direct link can pull data from
> a closed workbook.
>
> Respond if you want to try it and need help
>
> "ww" wrote:
>
> > I haven't tried that because the file I'm pulling everything into is a
> > summary page so it will generally have links to all the different Table.xls
> > files that I may use. I might only have Table1.xls through Table6.xls. On my
> > summary page. Others might use the summary page and just want Table1,
> > Table8, and Table10. So they'd have to change the formulas for all the rows
> > to get Table8 and Table10 since they weren't used beforehand. I thought if
> > they could just enter the a new file name in one cell instead of copying it
> > to all cells it would be easier. I am thinking of this right or am I missing
> > something. I'm not real familiar with links so. Thanks again.
> >
> > "Duke Carey" wrote:
> >
> > > Have you tried using the Edit | Links options off the menu?
> > >
> > > You should be able to tell Excel to change all the links from one book to
> > > another
> > >
> > >
> > >
> > > "ww" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a problem I was hoping someone could help me with. What I'm doing is
> > > > using a =sumif() to pull some numbers from different files. On the files
> > > > that I'm pulling the information from I have two ranges named Code and
> > > > Current and we'll call the actual file names Table.xls, Table1.xls,
> > > > Table2.xls, etc. On the file that I'm trying to pull the information into I
> > > > have =sumif('Table.xls'!Code,A5,'Table.xls'!Current) in C5.
> > > > This pulls the information fine but I have 300 rows I have this formula in.
> > > > I want to be able to just put the file name like Table.xls in C4 and have it
> > > > update all the rows without having to change the formula and copy it down
> > > > each time I change the file name. That way I could just change the file name
> > > > from Table.xls to Table1.xls in C4 and have everything update automatically.
> > > > I tried =sumif($C$4!Code,A5,$C$4!Current) but that wasn't working work. Does
> > > > anybody know of a way to do this? Thanks for any help you can offer.
> > > >
> > > > ww