#NA in remote reference formula

[BACinTX]

I have a budget summary report that pulls multiple names from several
different pipeline detail reports. I have 4 cells that return #N/A when
cells are updated i nthe budget summary report. If I open the actual
pipeline detail report file though, then the correct name is populated on the
budget status report. I have to do this each time though. There are a
multitude of other names that are updated from the various pipeline details
files with no problems nor without having to open the respective files. I
have checked the cell formatting in both spreadsheets, copied the formula,
deleted and re-entered the formula, etc. Any ideas what's wrong with just
these 4 cells out of many other that are working properly?

 

[Dave Peterson]

You may want to share the formula that's failing.

I have a budget summary report that pulls multiple names from several
different pipeline detail reports. I have 4 cells that return #N/A when
cells are updated i nthe budget summary report. If I open the actual
pipeline detail report file though, then the correct name is populated on the
budget status report. I have to do this each time though. There are a
multitude of other names that are updated from the various pipeline details
files with no problems nor without having to open the respective files. I
have checked the cell formatting in both spreadsheets, copied the formula,
deleted and re-entered the formula, etc. Any ideas what's wrong with just
these 4 cells out of many other that are working properly?

 

[BACinTX]

The failing formula is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]SteWhi'!C$5

The formula above this one is working though and it is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]RobGun'!C$5

I have checked the cell formatting in the target file, even copied the
RobGun cell over to the SteWhi cell. I have 4 formulas failing like this;
but, 52 working in this budget report.

 

[Dave Peterson]

The only difference I see is in the worksheet name. And if that worksheet
didn't exist, then opening the sending workbook wouldn't make a difference.

So I only have a silly guess...

Maybe it's a network problem.

If you put this file on your C: drive (just temporarily to test a few times),
does the problem disappear?

If the problem goes away, I think I'd talk with my network admins.

Another possible workaround.
Edit|Links|click Update values

or

Select the offending range of formulas
edit|replace
what: =
with: =
replace all

Maybe one of these would work.

The failing formula is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]SteWhi'!C$5

The formula above this one is working though and it is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]RobGun'!C$5

I have checked the cell formatting in the target file, even copied the
RobGun cell over to the SteWhi cell. I have 4 formulas failing like this;
but, 52 working in this budget report.

You may want to share the formula that's failing.

 

[BACinTX]

Hi, Dave: I tried all 3 options....none worked. I did however, copy the
RobGun file and turned it into the SteWhi file and that worked; but, did not
work when I tried that process on another failing formula file. I've just
hard coded the names in there now since this duty is being passed onto
someone else. I really wanted to get it fixed properly first before doing
so. Let me know if you can think of any other ideas.

Thanks!

The only difference I see is in the worksheet name. And if that worksheet
didn't exist, then opening the sending workbook wouldn't make a difference.

So I only have a silly guess...

Maybe it's a network problem.

If you put this file on your C: drive (just temporarily to test a few times),
does the problem disappear?

If the problem goes away, I think I'd talk with my network admins.

Another possible workaround.
Edit|Links|click Update values

or

Select the offending range of formulas
edit|replace
what: =
with: =
replace all

Maybe one of these would work.

The failing formula is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]SteWhi'!C$5

The formula above this one is working though and it is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]RobGun'!C$5

I have checked the cell formatting in the target file, even copied the
RobGun cell over to the SteWhi cell. I have 4 formulas failing like this;
but, 52 working in this budget report.

You may want to share the formula that's failing.

BACinTX wrote:

I have a budget summary report that pulls multiple names from several
different pipeline detail reports. I have 4 cells that return #N/A when
cells are updated in the budget summary report. If I open the actual
pipeline detail report file though, then the correct name is populated on the
budget status report. I have to do this each time though. There are a
multitude of other names that are updated from the various pipeline details
files with no problems nor without having to open the respective files. I
have checked the cell formatting in both spreadsheets, copied the formula,
deleted and re-entered the formula, etc. Any ideas what's wrong with just
these 4 cells out of many other that are working properly?

 

[Dave Peterson]

You write that you turned the RobGun file into the SteWhi file. But in your
formula, those weren't files--they were worksheets in the same file.

If that wasn't a typo in the message, then you may want to explain more.

But if you really meant worksheets, then I don't have any other insights to try.

Hi, Dave: I tried all 3 options....none worked. I did however, copy the
RobGun file and turned it into the SteWhi file and that worked; but, did not
work when I tried that process on another failing formula file. I've just
hard coded the names in there now since this duty is being passed onto
someone else. I really wanted to get it fixed properly first before doing
so. Let me know if you can think of any other ideas.

Thanks!

The only difference I see is in the worksheet name. And if that worksheet
didn't exist, then opening the sending workbook wouldn't make a difference.

So I only have a silly guess...

Maybe it's a network problem.

If you put this file on your C: drive (just temporarily to test a few times),
does the problem disappear?

If the problem goes away, I think I'd talk with my network admins.

Another possible workaround.
Edit|Links|click Update values

or

Select the offending range of formulas
edit|replace
what: =
with: =
replace all

Maybe one of these would work.

The failing formula is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]SteWhi'!C$5

The formula above this one is working though and it is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]RobGun'!C$5

I have checked the cell formatting in the target file, even copied the
RobGun cell over to the SteWhi cell. I have 4 formulas failing like this;
but, 52 working in this budget report.

:

You may want to share the formula that's failing.

BACinTX wrote:

I have a budget summary report that pulls multiple names from several
different pipeline detail reports. I have 4 cells that return #N/A when
cells are updated in the budget summary report. If I open the actual
pipeline detail report file though, then the correct name is populated on the
budget status report. I have to do this each time though. There are a
multitude of other names that are updated from the various pipeline details
files with no problems nor without having to open the respective files. I
have checked the cell formatting in both spreadsheets, copied the formula,
deleted and re-entered the formula, etc. Any ideas what's wrong with just
these 4 cells out of many other that are working properly?

 

[BACinTX]

They are worksheets within the same file....thanks!

You write that you turned the RobGun file into the SteWhi file. But in your
formula, those weren't files--they were worksheets in the same file.

If that wasn't a typo in the message, then you may want to explain more.

But if you really meant worksheets, then I don't have any other insights to try.

Hi, Dave: I tried all 3 options....none worked. I did however, copy the
RobGun file and turned it into the SteWhi file and that worked; but, did not
work when I tried that process on another failing formula file. I've just
hard coded the names in there now since this duty is being passed onto
someone else. I really wanted to get it fixed properly first before doing
so. Let me know if you can think of any other ideas.

Thanks!

The only difference I see is in the worksheet name. And if that worksheet
didn't exist, then opening the sending workbook wouldn't make a difference.

So I only have a silly guess...

Maybe it's a network problem.

If you put this file on your C: drive (just temporarily to test a few times),
does the problem disappear?

If the problem goes away, I think I'd talk with my network admins.

Another possible workaround.
Edit|Links|click Update values

or

Select the offending range of formulas
edit|replace
what: =
with: =
replace all

Maybe one of these would work.

BACinTX wrote:

The failing formula is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]SteWhi'!C$5

The formula above this one is working though and it is:

='\\Anb-tpd\commercial\2008 Pipeline\CRLG\[CRLG - ANB Pipeline -
2008.xls]RobGun'!C$5

I have checked the cell formatting in the target file, even copied the
RobGun cell over to the SteWhi cell. I have 4 formulas failing like this;
but, 52 working in this budget report.

:

You may want to share the formula that's failing.

BACinTX wrote:

I have a budget summary report that pulls multiple names from several
different pipeline detail reports. I have 4 cells that return #N/A when
cells are updated in the budget summary report. If I open the actual
pipeline detail report file though, then the correct name is populated on the
budget status report. I have to do this each time though. There are a
multitude of other names that are updated from the various pipeline details
files with no problems nor without having to open the respective files. I
have checked the cell formatting in both spreadsheets, copied the formula,
deleted and re-entered the formula, etc. Any ideas what's wrong with just
these 4 cells out of many other that are working properly?