CountIf Function Question

[Josh in Indy]

I have a summary spreadsheet, "April Daily Revenue Impact.xls" that has a tab
for each day of the month. Also, for each day of the month there is a
seperate spreadsheet that contains the orders for each day. The tabs on the
summary spreadsheet should look at the daily order log and summarize the
quantity of orders and the revenue impact of the orders by region. The
Sumproduct function works great for the revenues. However, the Countif
function will only function if the corresponding Daily Order Log is open when
the values are updated, otherwise it returns "#VALUE".

The function is entered as: "=COUNTIF([04042006.xls]Master
Worksheet'!$C$3:$C$83,A4)" where Row "C" is the Region on the 04042006.xls
Spreadsheet and Row "A" is the same regions on the summary spreadsheet. When
the corresponding Daily Order Log is open, the function returns the correct
values.

Any help would be greatly appreciated.

[deflated]

try clicking on edit, then go to "links". then select the 04042006.xls
spreasheet and click "update values"

"Josh in Indy" wrote:

> I have a summary spreadsheet, "April Daily Revenue Impact.xls" that has a tab
> for each day of the month. Also, for each day of the month there is a
> seperate spreadsheet that contains the orders for each day. The tabs on the
> summary spreadsheet should look at the daily order log and summarize the
> quantity of orders and the revenue impact of the orders by region. The
> Sumproduct function works great for the revenues. However, the Countif
> function will only function if the corresponding Daily Order Log is open when
> the values are updated, otherwise it returns "#VALUE".
>
> The function is entered as: "=COUNTIF([04042006.xls]Master
> Worksheet'!$C$3:$C$83,A4)" where Row "C" is the Region on the 04042006.xls
> Spreadsheet and Row "A" is the same regions on the summary spreadsheet. When
> the corresponding Daily Order Log is open, the function returns the correct
> values.
>
> Any help would be greatly appreciated.
>

[Josh in Indy]

That will update all of the values except the quantity of orders, which will
return the "#VALUE" until I open the 04042006.xls spreadsheet. When that
sheet is opened, the quantity of orders updates and shows the correct value.

"deflated" wrote:

> try clicking on edit, then go to "links". then select the 04042006.xls
> spreasheet and click "update values"
>
> "Josh in Indy" wrote:
>
> > I have a summary spreadsheet, "April Daily Revenue Impact.xls" that has a tab
> > for each day of the month. Also, for each day of the month there is a
> > seperate spreadsheet that contains the orders for each day. The tabs on the
> > summary spreadsheet should look at the daily order log and summarize the
> > quantity of orders and the revenue impact of the orders by region. The
> > Sumproduct function works great for the revenues. However, the Countif
> > function will only function if the corresponding Daily Order Log is open when
> > the values are updated, otherwise it returns "#VALUE".
> >
> > The function is entered as: "=COUNTIF([04042006.xls]Master
> > Worksheet'!$C$3:$C$83,A4)" where Row "C" is the Region on the 04042006.xls
> > Spreadsheet and Row "A" is the same regions on the summary spreadsheet. When
> > the corresponding Daily Order Log is open, the function returns the correct
> > values.
> >
> > Any help would be greatly appreciated.
> >

[deflated]

i tried this on my spreadsheet and your right, it doesn't work. but it does
work if you do a straight link to the other sheet. iow do the countif on the
other sheet and then do =(cell #) on the sheet you have open.

"Josh in Indy" wrote:

> That will update all of the values except the quantity of orders, which will
> return the "#VALUE" until I open the 04042006.xls spreadsheet. When that
> sheet is opened, the quantity of orders updates and shows the correct value.
>
> "deflated" wrote:
>
> > try clicking on edit, then go to "links". then select the 04042006.xls
> > spreasheet and click "update values"
> >
> > "Josh in Indy" wrote:
> >
> > > I have a summary spreadsheet, "April Daily Revenue Impact.xls" that has a tab
> > > for each day of the month. Also, for each day of the month there is a
> > > seperate spreadsheet that contains the orders for each day. The tabs on the
> > > summary spreadsheet should look at the daily order log and summarize the
> > > quantity of orders and the revenue impact of the orders by region. The
> > > Sumproduct function works great for the revenues. However, the Countif
> > > function will only function if the corresponding Daily Order Log is open when
> > > the values are updated, otherwise it returns "#VALUE".
> > >
> > > The function is entered as: "=COUNTIF([04042006.xls]Master
> > > Worksheet'!$C$3:$C$83,A4)" where Row "C" is the Region on the 04042006.xls
> > > Spreadsheet and Row "A" is the same regions on the summary spreadsheet. When
> > > the corresponding Daily Order Log is open, the function returns the correct
> > > values.
> > >
> > > Any help would be greatly appreciated.
> > >

[Josh in Indy]

That does work, thanks for the help.

If anyone knows how to make this work without having to add this function to
the 04042006.xls spreadsheet I would really rather not have that function on
that sheet.

Thanks

"deflated" wrote:

> i tried this on my spreadsheet and your right, it doesn't work. but it does
> work if you do a straight link to the other sheet. iow do the countif on the
> other sheet and then do =(cell #) on the sheet you have open.
>
> "Josh in Indy" wrote:
>
> > That will update all of the values except the quantity of orders, which will
> > return the "#VALUE" until I open the 04042006.xls spreadsheet. When that
> > sheet is opened, the quantity of orders updates and shows the correct value.
> >
> > "deflated" wrote:
> >
> > > try clicking on edit, then go to "links". then select the 04042006.xls
> > > spreasheet and click "update values"
> > >
> > > "Josh in Indy" wrote:
> > >
> > > > I have a summary spreadsheet, "April Daily Revenue Impact.xls" that has a tab
> > > > for each day of the month. Also, for each day of the month there is a
> > > > seperate spreadsheet that contains the orders for each day. The tabs on the
> > > > summary spreadsheet should look at the daily order log and summarize the
> > > > quantity of orders and the revenue impact of the orders by region. The
> > > > Sumproduct function works great for the revenues. However, the Countif
> > > > function will only function if the corresponding Daily Order Log is open when
> > > > the values are updated, otherwise it returns "#VALUE".
> > > >
> > > > The function is entered as: "=COUNTIF([04042006.xls]Master
> > > > Worksheet'!$C$3:$C$83,A4)" where Row "C" is the Region on the 04042006.xls
> > > > Spreadsheet and Row "A" is the same regions on the summary spreadsheet. When
> > > > the corresponding Daily Order Log is open, the function returns the correct
> > > > values.
> > > >
> > > > Any help would be greatly appreciated.
> > > >