Lỗi reference is not valid trong excel

Which of the following retains the information it's storing when the system power is turned off?
*

Excel 2010 – “Reference is not valid” error

I have a main spreadsheet with a data link to a second spreadsheet (formula below), which works fine.

You watching: Lỗi reference is not valid trong excel

="C:\Users\Steve\Desktop\ActualPictureFolder\1205-May\Sheet1"!$B$6:$G$17 

If I try to change the formula path, for example from “1205-May” to “1204-April” I receive the error “Reference is not valid”. (Yes, the spreadsheet name and cell values are the same in both 1205-May and 1204-April spreadsheets, which is why I was only changing the folder name). 

However, if I open the ActualPicture.xlsx spreadsheet in the 1204-April folder along with the main spreadsheet, I am able to successfully change “1205-May” to “1204-April”, which will then update the chart in the main spreadsheet.

Question…is it possible, from the main spreadsheet to change the formula path to other folders without having to have those files open? 

Thanks!


Best Answer
*

Habanero
OP
TanK203
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.
Sep 4, 2012 at 19:02 UTC

Stephen,

I noticed what seems like a syntax issue in your path. At first I didn"t see a closing single quotation, but then I did, and it was after "sheet1"

Original:="C:\Users\Steve\Desktop\ActualPictureFolder\1205-May\Sheet1"!$B$6:$G$17

Modified:="C:\Users\Steve\Desktop\ActualPictureFolder\1205-May\Sheet1!$B$6:$G$17"

This could just be an oversight, and I could be way off, but I say that and it threw up a red flag. Let me know if this helps.


View this "Best Answer" in the replies below »

9 Replies


· · ·

Ghost Chili
OP
Thanks A.J. Stringham Jul 19, 2012 at 11:59 UTC

You should be able to as long as you change it to the correct directory, I"m not sure why it wouldn"t update. As far as your question goes, I would say yes. Just make sure you have everything exact to a T. If they are off at all, it won"t work as I"m sure you are aware. Check to make sure that the folder names and files names match exactly. As far as things go, I can"t remember where it is but you need to refresh the document after you do it but I can"t remember how. Keep us updated. Thanks.

A.J.


0
· · ·
*

Sonora
OP
scmosesso Jul 19, 2012 at 12:09 UTC

Hi A.J. Yes. I triple checked to make sure the paths and file names are correct, so I should be good on that front. I forgot to mention that this process did work when Excel 2007 was installed, but when I upgraded to 2010, it doesn"t work now.


0
· · ·

Ghost Chili
OP
Thanks A.J. Stringham Jul 19, 2012 at 12:17 UTC

Sounds like a compatibility issue between the two. Odd though because they use the same format.....have you gone to the Data tab and hit "Refresh all"?? It may just be that the worksheet/book needs to be refreshed much like a webpage after a change is made. Try that and let me know. Thanks.

A.J.


0
· · ·
Sonora
OP
scmosesso Jul 19, 2012 at 12:37 UTC

Yes, I did that as well. No luck.

See more: Net Price Là Gì - Định Nghĩa, Ví Dụ, Giải Thích


0
· · ·
Ghost Chili
OP
Thanks A.J. Stringham Jul 19, 2012 at 12:40 UTC

I honestly don"t know then. Anyone else got any ideas because I"m out. Sorry I couldn"t help you. Keep us updated. Thanks.

A.J.


0
· · ·
Anaheim
OP
Bradley.Mott Jul 19, 2012 at 15:37 UTC

I can recreate your issue... I created test.xls inside of \test\ and \test2\

 

Each time I swap between the two, my Excel 2010 brings up a window to have me browse to the correct file. If I escape out of the browse window, it does appear to go to the right value without me selecting the file... are you experiencing the same? I only get the #REF! error if it really is an invalid filename.

 

Also, are you copy/pasting into the formula bar, or editing right on the sheet?

 

Oddly, when I select the file, it makes my "C:" lowercase to "c:", but even if I make that change, it still brings up the Browse window like it does not exist.

 

However, I also did a bit of further testing, and just created test2 and test3 folders, and for those folders it does not ask me to browse, no matter how many new copies I make. Maybe it has something to do with either the "created" date, or ??

 

I wonder if you"ve discovered a bug... you"re definitely not doing anything wrong.


1
· · ·
Sonora
OP
scmosesso Jul 20, 2012 at 08:03 UTC

I believe I need to use the PULL function when trying to pull data from a closed workbook...see link below.

As outlined, I saved the VBA code as a new module in the workbook, but now I"m not sure on how the formula should read.

See more: Quy Định Về Đương Sự Là Gì ? Người Tham Gia Tố Tụng Dân Sự Là Gì


1
· · ·
Pure Capsaicin
OP
Scott Alan Miller Aug 19, 2012 at 16:44 UTC

Did you manage to get this one figured out?


0
· · ·
Habanero
OP
Best Answer
TanK203
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.
Sep 4, 2012 at 19:02 UTC

Stephen,

I noticed what seems like a syntax issue in your path. At first I didn"t see a closing single quotation, but then I did, and it was after "sheet1"

Original:="C:\Users\Steve\Desktop\ActualPictureFolder\1205-May\Sheet1"!$B$6:$G$17

Modified:="C:\Users\Steve\Desktop\ActualPictureFolder\1205-May\Sheet1!$B$6:$G$17"

This could just be an oversight, and I could be way off, but I say that and it threw up a red flag. Let me know if this helps.


0

This topic has been locked by an administrator and is no longer open for commenting.