Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
#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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
#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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
#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
|
|||
|
|||
Quote:
all the best
__________________
Thanks Bala |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
#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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
#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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error of slope taking into account error of the data points | Excel Worksheet Functions | |||
Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) |