Formula to return contents of a cell using file path and cell coordinates

**gaker10** · 01-13-2015, 01:42 PM

Is there a formula to look up an excel file with the file pathway, sheet name, and cell number?

For example, return the contents of cell G55 on Sheet1 in file path P:\Stuff\CUSTOMERS\Customer1\ExcelFile.xlsm.

The other thing is I put the file path in a cell. In the example above, the file path is in cell P2. So the formula would have to read in cell P2 (the file path), then the sheet name, then the cell and return whatever is in that cell.

I've tried INDIRECT.EXT and also direct links using "=", but it always asks me to give it the path again, even after I told it the file path.

**gaker10** · 01-13-2015, 01:58 PM

Just tried a VLOOKUP with a wildcard and it still asked me for a path:

=VLOOKUP("*",[P2]SUMMARY!D55,1,0)

Where P2 contains the excel file path. It might be my syntax.

**gaker10** · 01-13-2015, 02:09 PM

UPDATE: I typed this in manually and it worked. But I would like to automate this necessary syntax into my formula.

**gaker10** · 01-13-2015, 02:35 PM

I managed to extract the file name (workbook name) only from the path. Now I need to extract the path without the filename, do a concatenation to put the right syntax in, and then run the formula using the concatenated string (path).

Formula to extract a workbook file name from a full path: =MID(P2,FIND(CHAR(1),SUBSTITUTE(P2,"\",CHAR(1),
LEN(P2)-LEN(SUBSTITUTE(P2,"\",""))))+1,LEN(P2))

Where P2 is the cell where my full path is.

**gaker10** · 01-13-2015, 02:37 PM

In regards to post #4, SUCCESS! The formula to extract parent path ONLY:

=LEFT(P2,FIND("?",SUBSTITUTE(P2,"\","?",LEN(P2)-LEN(SUBSTITUTE(P2,"\","")))))

**Glenn Kennedy** · 01-13-2015, 02:39 PM

Hi there. Drag both files to your desktop. open Book 2. Change the bit illustrated in bold, in the actual formulas in Book 2 to suit your circumstances. Check answer in Book1.xlsx.

Any queries, please shout...

**gaker10** · 01-13-2015, 02:50 PM

None of it was in bold, but I see where you mean. I'm on a network computer at work so I'm not sure this will work for me the same way as it does for you.

**Glenn Kennedy** · 01-13-2015, 02:52 PM

Ha! You're right. All that was in bold was C:\windows8\user\desktop

I mustn't have saved it before posting...

I haven't tried using it over a network, but I dont see whay it SHOULDN'T work. At home now, so I can't check it out...

**gaker10** · 01-13-2015, 02:55 PM

I know how to put in the right pathway for this computer, but I'm not sure it will have the same effect as a home PC. Also, dragging the workbooks to my desktop only makes a firefox html format of the workbook, and not the actual workbook, so I need to download them first and then throw them into my desktop.

**gaker10** · 01-13-2015, 02:59 PM

The formulas gave me a #NAME? error. I followed the path exactly as I typed it and I don't know where I went wrong.

**Glenn Kennedy** · 01-13-2015, 03:03 PM

Some others that you may find useful...

Formula to return file path minus filename
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

Filepath +filename & sheetname
=CELL("filename",A1)

Filename only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Sheetname only
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

**Glenn Kennedy** · 01-13-2015, 03:04 PM

Yea, I meant for you to download them. What is the filepath to the desktop on the PC that you're currently at?

**gaker10** · 01-13-2015, 03:20 PM

The domain within C: is protected, so I tried another drive and it still did not work. I can't actually give you the path info because it's sensitive. But I'll give you something close: E:\MyFolder\Book1.xlsx. The formula then would be: =INDIRECT.EXT("'E:\MyFolder\[Book1.xlsx]Sheet1'!A$1"). This produces a #NAME? error.

**Glenn Kennedy** · 01-13-2015, 03:41 PM

Mmm.

Have you seen/tried this?

http://www.excelforum.com/excel-form...work-path.html

Martin is very active on this forum. Maybe you should drop him a PM...

**gaker10** · 01-13-2015, 04:15 PM

Tried some of the suggestions on there with no luck. Sent him a PM.

**gaker10** · 01-13-2015, 04:19 PM

I sent him a PM but its not showing up in my Sent Items.

**Glenn Kennedy** · 01-13-2015, 04:36 PM

It's a Form feature, I think. They only save in sent if you specifically ask them to.

**gaker10** · 01-13-2015, 05:34 PM

I will update when I receive a response.

**gaker10** · 03-18-2015, 09:22 AM

I did not receive a response, and I accidentally posted a thread with the same subject, totally forgetting about this one.

The link: http://www.excelforum.com/excel-form...ml#post4018806

Formula to return contents of a cell using file path and cell coordinates

LinkBack

Thread Tools

Rate This Thread

Display

Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Re: Formula to return contents of a cell using file path and cell coordinates

Thread Information

Users Browsing this Thread

Similar Threads

Ignore file path in cell contents

[SOLVED] Formula to return first valid cell in a range ignoring specific cell contents

[SOLVED] Formula to return first valid cell in a range ignoring specific cell contents

Embedding contents of a cell into a file path

macro to include cell contents b1 in file path

Bookmarks

Bookmarks

Posting Permissions