Referencing other sheets - updates

I need the sheet to reference another file and update accordingly. I have
entered the destination of the file required and cells to be referenced etc.
This works fine if both files are open at the same time.

However, when I open the main file, it asks me if I want to update, and I
have to choose yes as it is referencing daily reports over a month, so I need
it to update automatically. When I choose yes, the fields error and don't
populate correctly, even though the destination in the formula is still
correct. What am I doing wrong?

Thanks in advance

There are some functions that don't work with closed workbooks:

=indirect(), =sumif(), =countif()

are a few

shakey1181 wrote:
>
> I need the sheet to reference another file and update accordingly. I have
> entered the destination of the file required and cells to be referenced etc.
> This works fine if both files are open at the same time.
>
> However, when I open the main file, it asks me if I want to update, and I
> have to choose yes as it is referencing daily reports over a month, so I need
> it to update automatically. When I choose yes, the fields error and don't
> populate correctly, even though the destination in the formula is still
> correct. What am I doing wrong?
>
> Thanks in advance

--

Dave Peterson

is there anyway around this? [i am using =countif()]

"Dave Peterson" wrote:

> There are some functions that don't work with closed workbooks:
>
> =indirect(), =sumif(), =countif()
>
> are a few
>
> shakey1181 wrote:
> >
> > I need the sheet to reference another file and update accordingly. I have
> > entered the destination of the file required and cells to be referenced etc.
> > This works fine if both files are open at the same time.
> >
> > However, when I open the main file, it asks me if I want to update, and I
> > have to choose yes as it is referencing daily reports over a month, so I need
> > it to update automatically. When I choose yes, the fields error and don't
> > populate correctly, even though the destination in the formula is still
> > correct. What am I doing wrong?
> >
> > Thanks in advance
>
> --
>
> Dave Peterson
>

You can replace your formula with =sumproduct().

=SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99=A3))

Adjust the range to match--but you can't use the whole column.

If you create the formula with the book1.xls workbook open, you may find it
easier. Excel will adjust the formula when you close that workbook.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

shakey1181 wrote:
>
> is there anyway around this? [i am using =countif()]
>
> "Dave Peterson" wrote:
>
> > There are some functions that don't work with closed workbooks:
> >
> > =indirect(), =sumif(), =countif()
> >
> > are a few
> >
> > shakey1181 wrote:
> > >
> > > I need the sheet to reference another file and update accordingly. I have
> > > entered the destination of the file required and cells to be referenced etc.
> > > This works fine if both files are open at the same time.
> > >
> > > However, when I open the main file, it asks me if I want to update, and I
> > > have to choose yes as it is referencing daily reports over a month, so I need
> > > it to update automatically. When I choose yes, the fields error and don't
> > > populate correctly, even though the destination in the formula is still
> > > correct. What am I doing wrong?
> > >
> > > Thanks in advance
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Still returning #VALUE!.

The COUNTIF formula, that returned the correct data was as follows:

=COUNTIF('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10)

this showed the correct data [count of B10 entires] if either both sheets
were open or you didn't update. using SUMPRODUCT I have written the below:

=SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10))

this still returns an error,

any thoughts?





"Dave Peterson" wrote:

> You can replace your formula with =sumproduct().
>
> =SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99=A3))
>
> Adjust the range to match--but you can't use the whole column.
>
> If you create the formula with the book1.xls workbook open, you may find it
> easier. Excel will adjust the formula when you close that workbook.
>
> =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
>
> shakey1181 wrote:
> >
> > is there anyway around this? [i am using =countif()]
> >
> > "Dave Peterson" wrote:
> >
> > > There are some functions that don't work with closed workbooks:
> > >
> > > =indirect(), =sumif(), =countif()
> > >
> > > are a few
> > >
> > > shakey1181 wrote:
> > > >
> > > > I need the sheet to reference another file and update accordingly. I have
> > > > entered the destination of the file required and cells to be referenced etc.
> > > > This works fine if both files are open at the same time.
> > > >
> > > > However, when I open the main file, it asks me if I want to update, and I
> > > > have to choose yes as it is referencing daily reports over a month, so I need
> > > > it to update automatically. When I choose yes, the fields error and don't
> > > > populate correctly, even though the destination in the formula is still
> > > > correct. What am I doing wrong?
> > > >
> > > > Thanks in advance
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

=SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27=$B10))

Notice that the last comma is now an equal sign.



shakey1181 wrote:
>
> Still returning #VALUE!.
>
> The COUNTIF formula, that returned the correct data was as follows:
>
> =COUNTIF('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10)
>
> this showed the correct data [count of B10 entires] if either both sheets
> were open or you didn't update. using SUMPRODUCT I have written the below:
>
> =SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10))
>
> this still returns an error,
>
> any thoughts?
>
> "Dave Peterson" wrote:
>
> > You can replace your formula with =sumproduct().
> >
> > =SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99=A3))
> >
> > Adjust the range to match--but you can't use the whole column.
> >
> > If you create the formula with the book1.xls workbook open, you may find it
> > easier. Excel will adjust the formula when you close that workbook.
> >
> > =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
> > to 1's and 0's.
> >
> > Bob Phillips explains =sumproduct() in much more detail here:
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > And J.E. McGimpsey has some notes at:
> > http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > shakey1181 wrote:
> > >
> > > is there anyway around this? [i am using =countif()]
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > There are some functions that don't work with closed workbooks:
> > > >
> > > > =indirect(), =sumif(), =countif()
> > > >
> > > > are a few
> > > >
> > > > shakey1181 wrote:
> > > > >
> > > > > I need the sheet to reference another file and update accordingly. I have
> > > > > entered the destination of the file required and cells to be referenced etc.
> > > > > This works fine if both files are open at the same time.
> > > > >
> > > > > However, when I open the main file, it asks me if I want to update, and I
> > > > > have to choose yes as it is referencing daily reports over a month, so I need
> > > > > it to update automatically. When I choose yes, the fields error and don't
> > > > > populate correctly, even though the destination in the formula is still
> > > > > correct. What am I doing wrong?
> > > > >
> > > > > Thanks in advance
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Hi Dave,

Well the formula did help, however it is giving me reference error for some dates.

I do have the file, when i open it, the value come up.

But as per your explaination for sum product its not working.

Please help.

The formula i am using is given below :

=SUMPRODUCT(--('C:\csat\July''06\[CSAT_Daily_1.xls]CSAT_Daily'!$V$1:$V$1000="VS"))[/SIZE]

Also one more question, is there a way to dynamically pull up the file name ie. the reference file. In this case C:\csat\July"06\[CSAT_Daily_1.xls]
then the worksheet name n then the range.

Do help me if possible.

Eagerly waiting for reply.

Originally Posted by Dave Peterson

=SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27=$B10))

Notice that the last comma is now an equal sign.



shakey1181 wrote:
>
> Still returning #VALUE!.
>
> The COUNTIF formula, that returned the correct data was as follows:
>
> =COUNTIF('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10)
>
> this showed the correct data [count of B10 entires] if either both sheets
> were open or you didn't update. using SUMPRODUCT I have written the below:
>
> =SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10))
>
> this still returns an error,
>
> any thoughts?
>
> "Dave Peterson" wrote:
>
> > You can replace your formula with =sumproduct().
> >
> > =SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99=A3))
> >
> > Adjust the range to match--but you can't use the whole column.
> >
> > If you create the formula with the book1.xls workbook open, you may find it
> > easier. Excel will adjust the formula when you close that workbook.
> >
> > =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
> > to 1's and 0's.
> >
> > Bob Phillips explains =sumproduct() in much more detail here:
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > And J.E. McGimpsey has some notes at:
> > http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > shakey1181 wrote:
> > >
> > > is there anyway around this? [i am using =countif()]
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > There are some functions that don't work with closed workbooks:
> > > >
> > > > =indirect(), =sumif(), =countif()
> > > >
> > > > are a few
> > > >
> > > > shakey1181 wrote:
> > > > >
> > > > > I need the sheet to reference another file and update accordingly. I have
> > > > > entered the destination of the file required and cells to be referenced etc.
> > > > > This works fine if both files are open at the same time.
> > > > >
> > > > > However, when I open the main file, it asks me if I want to update, and I
> > > > > have to choose yes as it is referencing daily reports over a month, so I need
> > > > > it to update automatically. When I choose yes, the fields error and don't
> > > > > populate correctly, even though the destination in the formula is still
> > > > > correct. What am I doing wrong?
> > > > >
> > > > > Thanks in advance
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

If you're getting a reference error (sometimes), then I would think that you
made a typing mistake in your formula.

I'd check the names (folder, file and sheet) once more.

And the function you'd want to use is =indirect(), but that only works when the
sending workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

(I've never used it, though.)

aalokjain wrote:
>
> Hi Dave,
>
> Well the formula did help, however it is giving me reference error for
> some dates.
>
> I do have the file, when i open it, the value come up.
>
> But as per your explaination for sum product its not working.
>
> Please help.
>
> The formula i am using is given below :
>
> =sumproduct(--('c:\csat\july''06\[csat_daily_1.xls]csat_daily'!$v$1:$v$1000=\"vs\"))[/SIZE]
>
> Also one more question, is there a way to dynamically pull up the file
> name ie. the reference file. In this case
> C:\csat\July"06\[CSAT_Daily_1.xls]
> then the worksheet name n then the range.
>
> Do help me if possible.
>
> Eagerly waiting for reply.
>
> Dave Peterson Wrote:
> > =SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27=$B10))
> >
> > Notice that the last comma is now an equal sign.
> >
> >
> >
> > shakey1181 wrote:
> > >
> > > Still returning #VALUE!.
> > >
> > > The COUNTIF formula, that returned the correct data was as follows:
> > >
> > > =COUNTIF('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10)
> > >
> > > this showed the correct data [count of B10 entires] if either both
> > sheets
> > > were open or you didn't update. using SUMPRODUCT I have written the
> > below:
> > >
> > > =SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10))
> > >
> > > this still returns an error,
> > >
> > > any thoughts?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You can replace your formula with =sumproduct().
> > > >
> > > > =SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99=A3))
> > > >
> > > > Adjust the range to match--but you can't use the whole column.
> > > >
> > > > If you create the formula with the book1.xls workbook open, you may
> > find it
> > > > easier. Excel will adjust the formula when you close that
> > workbook.
> > > >
> > > > =sumproduct() likes to work with numbers. The -- stuff changes
> > trues and falses
> > > > to 1's and 0's.
> > > >
> > > > Bob Phillips explains =sumproduct() in much more detail here:
> > > > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> > > >
> > > > And J.E. McGimpsey has some notes at:
> > > > http://mcgimpsey.com/excel/formulae/doubleneg.html
> > > >
> > > > shakey1181 wrote:
> > > > >
> > > > > is there anyway around this? [i am using =countif()]
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > There are some functions that don't work with closed
> > workbooks:
> > > > > >
> > > > > > =indirect(), =sumif(), =countif()
> > > > > >
> > > > > > are a few
> > > > > >
> > > > > > shakey1181 wrote:
> > > > > > >
> > > > > > > I need the sheet to reference another file and update
> > accordingly. I have
> > > > > > > entered the destination of the file required and cells to be
> > referenced etc.
> > > > > > > This works fine if both files are open at the same time.
> > > > > > >
> > > > > > > However, when I open the main file, it asks me if I want to
> > update, and I
> > > > > > > have to choose yes as it is referencing daily reports over a
> > month, so I need
> > > > > > > it to update automatically. When I choose yes, the fields
> > error and don't
> > > > > > > populate correctly, even though the destination in the
> > formula is still
> > > > > > > correct. What am I doing wrong?
> > > > > > >
> > > > > > > Thanks in advance
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
>
> --
> aalokjain
> ------------------------------------------------------------------------
> aalokjain's Profile: http://www.excelforum.com/member.php...o&userid=36671
> View this thread: http://www.excelforum.com/showthread...hreadid=542408

--

Dave Peterson