Filepath in cell problem.

Hi All

I was trying to use the following formula to return the file path of the current excel spreadsheet as part of a dynamic macro for refreshing external data.

Please Login or Register  to view this content.

Can anyone tell me why this formula will return the same file path in two different worksheets saved in two different locations?

For example:

I have a worksheet saved as 'C:\A\worksheet1.xls' and a worksheet saved as 'C:\B\worksheet2.xls'. I open them both up and insert the formula into A1 on worksheet1 and "C:\A\" appears. I then switch to worksheet2 and insert the formula into A1 and "C:\B\" appears. All appears fine until I switch back to worksheet1 and find "C:\B\" in A1...

Also, I tried using =INFO("directory") but this seemed to be even more unstable.

Any advice will be greatly appreciated as this makes my brain want to explode!

Thanks

Hi Gus,

without a concrete cell reference, the Cell() function will show the value of the last time it was run. Start your formula with

=SUBSTITUTE((CELL("filename",A1)),RIGHT((CEL....

and it will reference the current file.