 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2007Why oh Y
I've just come across a very weird oddity with Excel 2003. I have a spread sheet which has formula which reads information from another (external) worksheet. The information is a reference in the format "AAAnumbersY". If the source file is open when the problem file is open it will return the full reference. However if the source file is not open it returns #NA. This happens ONLY when the last character of the reference is "Y". (Yes, I've tried them all!) This seems to be very strange behaviour indeed. Why oh why is this happening? Any ideas please? Regards. Bill Ridgeway There are some worksheet functions that won't work with closed workbooks--but IIRC, most of them would return #ref! errors. =sumif(), =countif(), =indirect() Are a few. You may want to share the formula that you're using if this isn't close to the problem. > I've just come across a very weird oddity with Excel 2003. > [quoted text clipped - 11 lines] > > Bill Ridgeway  SignatureDave Peterson Thanks Dave. The formula is a simple =[file and cell reference] of which there are 23 other examples on the same worksheet that are OK. The problem exists wherever I put this particular reference that ends in "Y". I have already checked that all the cells (both source and target) are formatted the same. Regards. Bill Ridgeway > There are some worksheet functions that won't work with closed > workbooks--but [quoted text clipped - 27 lines] >> >> Bill Ridgeway You sure that the column is wide enough <bg>??? The only time I've seen anything like this truncated is when the string coming back is over 255 characters. Excel will chop the results to 255 if the sending workbook is closed. (And that doesn't return an #n/a error.) And you're sure that the formula the cell points to doesn't get recalculated to be #n/a during the final close/save????? > Thanks Dave. The formula is a simple =[file and cell reference] of which > there are 23 other examples on the same worksheet that are OK. The problem [quoted text clipped - 41 lines] > > > > Dave Peterson SignatureDave Peterson Thanks Dave. The cell in question is one of 24 in a column. All the other 23 cells display correct information. The error occurs anywhere in the 24 cells in which I put the "=[file and cell reference] and anywhere I put the particular reference that ends in "Y" (without quotes) in the source file. If I omit the "Y" or substitute another letter (I've tried them all) it displays OK. The error doesn't occur if I have the source file open at the time of opening the file with the formula. It occurs only if I open the file containing the formula without having first opened the source file. The simple answer would be to omit the "Y" but a) it wouldn't be a correct reference and b) it wouldn't answer the question why the heck is it doing this. Regards. Bill Ridgeway > You sure that the column is wide enough <bg>??? > [quoted text clipped - 58 lines] >> > >> > Dave Peterson I've never seen a problem like this. I don't have any other guesses. But you are sure you're pointing to the correct workbook, worksheet, address? (Yeah, you've already checked this 18 times...) > Thanks Dave. > [quoted text clipped - 82 lines] > > > > Dave Peterson SignatureDave Peterson Hi Bill I'm not quite understanding where the "y" is. Is it the last character in the source file name, or the last character in the contents of the cell in the (closed or open) workbook? I have tried it with ='C:\Documents and Settings\Roger Govier\My Documents\[summary.xls]Sheet1'!$B3 and 50 in cell B3 and my result is 50 either with the file open or closed. Equally, with the word Summary in cell B3, it returns Summary to me wither with the file open or closed. Perhaps I am not understanding the problem you are describing. SignatureRegards Roger Govier > Thanks Dave. > [quoted text clipped - 84 lines] >>> > >>> > Dave Peterson The letter "y" appears as the last character in the cell of the source file. Regards. Bill Ridgeway > Hi Bill > [quoted text clipped - 95 lines] >>>> > >>>> > Dave Peterson Hi Bill Then I cannot re-create the problem. If you want to mail the files to me direct, I will be quite happy to take a look and see if I can see what's happening. To mail direct, remove NOSPAM from my mail address. SignatureRegards Roger Govier > The letter "y" appears as the last character in the cell of the source > file. [quoted text clipped - 108 lines] >>>>> > >>>>> > Dave Peterson And your sending workbook is a regular old normal .xls workbook????? (If I use a .csv file, I got a different warning, though--not able to update links.) > The letter "y" appears as the last character in the cell of the source file. > [quoted text clipped - 110 lines] > >>> > >>> Dave Peterson SignatureDave Peterson Thanks Dave, Both source and target files are normal Excel (.xls) files. The problem (in summary) is that - Source file is open- No problem Source file not open- Source cell information is AAA-(numbers)Y - target cell returns #NA Source cell information is AAA-(numbers) - target cell returns AAA-(numbers) This occurs in whatever cell in the source file the string AAA-(numbers)Y is placed The same string but with any other letter does not produce an error. Regards. Bill Ridgeway > And your sending workbook is a regular old normal .xls workbook????? > [quoted text clipped - 129 lines] >> >>> >> >>> Dave Peterson And if you build that same formula in a brand new workbook, does it work ok? How about if you put it in a different cell in the same worksheet or a different worksheet?? How about if you point at a different cell, a different worksheet or a different workbook? (Grasping at straws to find some sort of pattern--I can't believe that the Y causes the problem--I don't want to believe that!) > Thanks Dave, > [quoted text clipped - 152 lines] > > > > Dave Peterson SignatureDave Peterson Thanks Dave. <<And if you build that same formula in a brand new workbook, does it work ok?>> No <<How about if you put it in a different cell in the same worksheet or a different worksheet??>>Neither in a different worksheet within the spreadsheet or another spreadsheet. <<How about if you point at a different cell, a different worksheet or a different workbook?>>Pointing to a different cell, different worksheet and different worksheet (containing data copied from the original cell, not typed in) is OK. <<(Grasping at straws to find some sort of pattern--I can't believe that the "Y" [(without quotes)] causes the problem--I don't want to believe that!)>> If I substitute ANY other letter it works OK. I have just tried substituting "Y" with "_Y" and it also works OK. It doesn't seem possible or, indeed, logical but that is how it is. The plot just seems to be thickening! Have I stumbled on an undocumented and secret facet of Excel?? Regards. Bill Ridgeway > And if you build that same formula in a brand new workbook, does it work > ok? [quoted text clipped - 184 lines] >> > >> > Dave Peterson It doesn't sound like it's the extra Y that's causing the trouble if this worked ok. > <<How about if you point at a different cell, a different worksheet or a > different workbook?>> > Pointing to a different cell, different worksheet and > different worksheet (containing data copied from the original cell, not > typed in) is OK. Can you copy the two workbooks and then modify them so that the receiving file contains two formulas--one that works and one that doesn't. And so the sending workbook only has those two cells. Maybe you could zip the pair and put them on www.savefile.com. I'd like to see them and I bet others would, too. > Thanks Dave. > <<And if you build that same formula in a brand new workbook, does it work [quoted text clipped - 214 lines] > > > > Dave Peterson SignatureDave Peterson Yet more attempts- Saved both files in new name and changed references to pull data from now new source file name: Same problem Extracted (copy and paste) source data into a new file and changed references to pull data from now new source file name: Problem resolved Copied and pasted data from the new file to the original source file Same problem This proves that it can be done (we knew that all the while) whilst simultaneously proving and disproving that the last alpha character is, quite illogically, causing a problem. Any more thoughts whilst I go quietly mad please? Regards. > Thanks Dave. > <<And if you build that same formula in a brand new workbook, does it work [quoted text clipped - 215 lines] >>> > >>> > Dave Peterson Just to skinny down that pair of workbooks and share them via savefile.com. Maybe someone will download them and figure out the problem. > Yet more attempts- > [quoted text clipped - 240 lines] > >> > >> Dave Peterson SignatureDave Peterson Thanks for all your help on this subject. I've got round this problem by the simple expedient of deleting the letter "Y" which seemed to be the cause. It would be nice to find out what the heck was happening but life is just too short. Bill Ridgeway You don't want to share a skinnied down version of both files? > Thanks for all your help on this subject. I've got round this problem by > the simple expedient of deleting the letter "Y" which seemed to be the > cause. It would be nice to find out what the heck was happening but life is > just too short. > > Bill Ridgeway SignatureDave Peterson I have produced a slimmed down version into which I put dummy data. However, it is in the nature of the problem that even changing one character resolves the issue so a demonstration spreadsheet isn't possible. Thanks for the offer. Regards. Bill Ridgeway Computer Solutions > You don't want to share a skinnied down version of both files? > [quoted text clipped - 5 lines] >> >> Bill Ridgeway
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.