Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

I need help with Excel 2007 Error

I am using the following formula and get this error:  "You cannot use an internet address here. Enter a path that points to a location on your computer or on the network."

I have mapped a drive to the excel spreadsheet and verify I can open it without any permission issues. Found very little information relating to excel on this issue using google.

Here is the formula:

=IF(ISERROR(MATCH(B4,'P:\[Report1.xls]Sheet1'!$P$14:$P$1000,0)),(OR(C4="Complete",C4="Complete - Successful")+0),INDEX('P:\[Report1.xls]Sheet1'!$AX$14:$AX$1000,MATCH(B4,'P:\[Report1.xls]Sheet1'!$P$14:$P$1000,0)))
0
wrt1mea
Asked:
wrt1mea
1 Solution
 
ryan_johnstonCommented:
Instead of using P:\ have you tried using the unc path to the file?
0
 
wrt1meaAuthor Commented:
I tried that, but am not sure of the sytax to specify which sheet. Here is what I tried and it didnt work:

\\serv1\area1\state\reports\building\development\[West Report.xls]Projects'!

I need to be able to specify the sheet.
0
 
Michael FowlerSolutions ConsultantCommented:
Try changing

P:\[Report1.xls]

to

[P:\Report1.xls]

I don't believe that the formula knows what to do with the P:\ in its current location

Michael


=IF(ISERROR(MATCH(B4,'[P:\Report1.xls]Sheet1'!$P$14:$P$1000,0)),(OR(C4="Complete",C4="Complete - Successful")+0),INDEX('[P:\Report1.xls]Sheet1'!$AX$14:$AX$1000,MATCH(B4,'[P:\Report1.xls]Sheet1'!$P$14:$P$1000,0)))

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
wrt1meaAuthor Commented:
Now it says I have too few arguments for this function
0
 
wrt1meaAuthor Commented:
Too few arguments at $AX$14:$AX$1000
0
 
wrt1meaAuthor Commented:
I will post more in the morning...
0
 
byundtCommented:
The easy way to resolve issues like this is to open the target workbook and select the ranges directly as you create the formula. Verify that it works. Then save the target file in the desired location and close it. Your formula should still work, but will now look like:
=IF(ISERROR(MATCH(B4,'P:\[Report1.xls]Sheet1'!$P$14:$P$1000,0)),(OR(C4="Complete",C4="Complete - Successful")+0),INDEX('P:\[Report1.xls]Sheet1'!$AX$14:$AX$1000,MATCH(B4,'P:\[Report1.xls]Sheet1'!$P$14:$P$1000,0)))

Because selecting three ranges down through row 1000 is somewhat tedious, I actually only selected from row 14 through row 100. After closing the workbook, I added the extra 0 in three places to extend the range.

I realize that my formula looks exactly like the one that you posted. This suggests that there might be an issue with the name of the file or worksheet of the target workbook. Rather than trying to find the discrepancy, please follow the formula building approach described above. It has saved my butt on many an occasion.

Brad
0
 
wrt1meaAuthor Commented:
Great advice...I will learn to use the easier way that provides the correct syntax. Thanks again.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now