#1   Report Post  
Old May 14th 10, 01:16 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 25
Default #N/A error

I have SpreadsheetA which (in a column) takes data from SpreadsheetB (in a
row). (A simple = ...). So far so good and both spreadsheets are saved and
closed. The correct information is displayed in SpradsheetA on opening but,
on opening SpreadsheetB information in ONLY some cells change to #N/A.
Given that the displayed data is correct proves the link is correct but why
should it change?

Any suggestions gratefully received as I just can't think of an answer.

Thanks.

Bill R



  #2   Report Post  
Old May 14th 10, 05:00 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default #N/A error

Hi Bill,

Impossible to answer if we don't get the *exact* formulas in SpreadsheetA
and the formulas or values in SpreadsheetB
And what do you mean he "Given that the displayed data is correct proves
the link is correct "?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Bill R" wrote in message
...
I have SpreadsheetA which (in a column) takes data from SpreadsheetB (in a
row). (A simple = ...). So far so good and both spreadsheets are saved and
closed. The correct information is displayed in SpradsheetA on opening
but, on opening SpreadsheetB information in ONLY some cells change to #N/A.
Given that the displayed data is correct proves the link is correct but why
should it change?

Any suggestions gratefully received as I just can't think of an answer.

Thanks.

Bill R


  #3   Report Post  
Old May 14th 10, 05:34 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 25
Default #N/A error

Thanks Niek.

The formulae in SpreadsheetA are in the format
='[SpreadsheetB.xls]Data'!$V$5

The data to which they refer in SpreadsheetB is numeric.

All the data cells SpreadsheetB are formatted identically

What I mean by "Given that the displayed data is correct proves the link is
correct "? is this. If it is capable of displaying the correct data from
the cell in SpreadsheetB the link is correct. The mystery is why the
display changes to the error message but only on some cells when
SpreadsheetB is not open. If the formula returned correct information (or
even incorrect information) consistently then it should be easy to trace and
correct. It is the inconsistency that's beating me!

Another oddity is that when going into Formulae Editing, both Trace
Dependents and Trace Precedents return arrows between the error cell (say
A30) and a cell in row C (C28) which is a text cell with absolutely no
connection to the error cell. This is consistent through the column in
Spreadsheet A (those that return correct information and also those that
return an error).

Bill R
"Niek Otten" wrote in message
...
Hi Bill,

Impossible to answer if we don't get the *exact* formulas in SpreadsheetA
and the formulas or values in SpreadsheetB
And what do you mean he "Given that the displayed data is correct
proves the link is correct "?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Bill R" wrote in message
...
I have SpreadsheetA which (in a column) takes data from SpreadsheetB (in a
row). (A simple = ...). So far so good and both spreadsheets are saved
and closed. The correct information is displayed in SpradsheetA on
opening but, on opening SpreadsheetB information in ONLY some cells change
to #N/A. Given that the displayed data is correct proves the link is
correct but why should it change?

Any suggestions gratefully received as I just can't think of an answer.

Thanks.

Bill R




  #4   Report Post  
Old May 14th 10, 07:41 PM
Senior Member
 
First recorded activity by ExcelBanter: Sep 2008
Location: Hyderabad
Posts: 237
Default

Quote:
Originally Posted by Bill R[_3_] View Post
I have SpreadsheetA which (in a column) takes data from SpreadsheetB (in a
row). (A simple = ...). So far so good and both spreadsheets are saved and
closed. The correct information is displayed in SpradsheetA on opening but,
on opening SpreadsheetB information in ONLY some cells change to #N/A.
Given that the displayed data is correct proves the link is correct but why
should it change?

Any suggestions gratefully received as I just can't think of an answer.

Thanks.

Bill R
If you are using excel 2007,make sure Excel Options -Advanced-"Update links to other documents" is checked in

all the best
__________________
Thanks
Bala
  #5   Report Post  
Old May 14th 10, 10:45 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default #N/A error

I regret I have to say that I don't understand a word of what you're saying

Can you give an example of exactly what is bothering you? Formulas, values,
expected and actual results, anything that might show what your problem
is....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bill R" wrote in message
...
Thanks Niek.

The formulae in SpreadsheetA are in the format
='[SpreadsheetB.xls]Data'!$V$5

The data to which they refer in SpreadsheetB is numeric.

All the data cells SpreadsheetB are formatted identically

What I mean by "Given that the displayed data is correct proves the link
is correct "? is this. If it is capable of displaying the correct data
from the cell in SpreadsheetB the link is correct. The mystery is why the
display changes to the error message but only on some cells when
SpreadsheetB is not open. If the formula returned correct information (or
even incorrect information) consistently then it should be easy to trace
and correct. It is the inconsistency that's beating me!

Another oddity is that when going into Formulae Editing, both Trace
Dependents and Trace Precedents return arrows between the error cell (say
A30) and a cell in row C (C28) which is a text cell with absolutely no
connection to the error cell. This is consistent through the column in
Spreadsheet A (those that return correct information and also those that
return an error).

Bill R
"Niek Otten" wrote in message
...
Hi Bill,

Impossible to answer if we don't get the *exact* formulas in SpreadsheetA
and the formulas or values in SpreadsheetB
And what do you mean he "Given that the displayed data is correct
proves the link is correct "?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Bill R" wrote in message
...
I have SpreadsheetA which (in a column) takes data from SpreadsheetB (in
a row). (A simple = ...). So far so good and both spreadsheets are saved
and closed. The correct information is displayed in SpradsheetA on
opening but, on opening SpreadsheetB information in ONLY some cells
change to #N/A. Given that the displayed data is correct proves the link
is correct but why should it change?

Any suggestions gratefully received as I just can't think of an answer.

Thanks.

Bill R







  #6   Report Post  
Old May 15th 10, 09:18 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,204
Default #N/A error

We need to see the formulas in cells where you're getting the #N/A entries.
Typically the #N/A entry appears in formulas involving functions like
VLOOKUP().

You can "inject" a #N/A error in a VLookup() accidentally by allowing the
lookup table address to adjust dynamically. Consider this formula in B2 on a
sheet
=VLOOKUP(A2,Sheet2!R1:V100,2,False)
and that formula works fine, returning the data from column S on Sheet2
where the value in colum R of Sheet2 matches the value of A2 on this sheet.

Now, you "fill" this formula down your worksheet because you have over data
to return also, but because of the way you've written the addresses, they all
change! In row 3, the formula becomes:
=VLOOKUP(A3,Sheet2!R2:V101,2,False)
You actually wanted the change from A2 to A3, but you did not want the
R1:V100 to change to R2:V101 because eventually, it would become something
like
=VLOOKUP(A102,Sheet2!R101:V200,2,False)
which wouldn't be looking at your data table at all, and would always return
#N/A!!

To fix that, you'd change the initial formula to this:
=VLOOKUP(A2,Sheet2!$R$1:$V$100,2,False)
and that keeps you always looking at the same area on Sheet2 no matter how
you fill the formula on the sheet that the formula is on.

I hope this didn't take us too far off base for you -- your problem may not
have anything to with VLOOKUP(), but it could be something close to that type
of situation.




"Bill R" wrote:

Thanks Niek.

The formulae in SpreadsheetA are in the format
='[SpreadsheetB.xls]Data'!$V$5

The data to which they refer in SpreadsheetB is numeric.

All the data cells SpreadsheetB are formatted identically

What I mean by "Given that the displayed data is correct proves the link is
correct "? is this. If it is capable of displaying the correct data from
the cell in SpreadsheetB the link is correct. The mystery is why the
display changes to the error message but only on some cells when
SpreadsheetB is not open. If the formula returned correct information (or
even incorrect information) consistently then it should be easy to trace and
correct. It is the inconsistency that's beating me!

Another oddity is that when going into Formulae Editing, both Trace
Dependents and Trace Precedents return arrows between the error cell (say
A30) and a cell in row C (C28) which is a text cell with absolutely no
connection to the error cell. This is consistent through the column in
Spreadsheet A (those that return correct information and also those that
return an error).

Bill R
"Niek Otten" wrote in message
...
Hi Bill,

Impossible to answer if we don't get the *exact* formulas in SpreadsheetA
and the formulas or values in SpreadsheetB
And what do you mean he "Given that the displayed data is correct
proves the link is correct "?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Bill R" wrote in message
...
I have SpreadsheetA which (in a column) takes data from SpreadsheetB (in a
row). (A simple = ...). So far so good and both spreadsheets are saved
and closed. The correct information is displayed in SpradsheetA on
opening but, on opening SpreadsheetB information in ONLY some cells change
to #N/A. Given that the displayed data is correct proves the link is
correct but why should it change?

Any suggestions gratefully received as I just can't think of an answer.

Thanks.

Bill R




.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error of slope taking into account error of the data points cer144 Excel Worksheet Functions 5 July 7th 08 07:26 PM
Error: "Excel encountered an error and had to remove some formatti Carl Excel Discussion (Misc queries) 0 September 18th 06 06:39 PM
Counting instances of found text (Excel error? Or user error?) S Davis Excel Worksheet Functions 5 September 12th 06 04:52 PM
I have Error 1919 Error Configuring ODBC dataSource Database Texanna1 Excel Discussion (Misc queries) 1 September 12th 06 06:35 AM


All times are GMT +1. The time now is 12:32 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017