why does excel keep putting the entire filename into my formulas, as well as tablenumber,this row, etc?

[BSWJON]

For some reason, in some of my formulas excel decides when I use the 'click on a cell to insert it into the formula' method of entering a formula, it also adds the ENTIRE file location and name as well as 'table1,this row' into the formula. Not only does this clutter up my formula but whenever the file name of the workbook is changed, and for some reason when I change the name of a copy of the same workbook, it changes the destination info in the formula to something about appdata and roaming and destroys the formula.

Can anyone tell me why it is adding this extra info into the formulas, and why workbook 1's formulas keep changing according to changes I make in workbook 2?

This is ruining my workbook and prohibiting me from continuing my work, if anyone knows any solutions I would love to hear them!

 

[Andrew Poulsom]

If a formula references an external workbook, the reference will change to show the full path if the external workbook is closed.

 

[BSWJON]

I think this needs further explanation on my part- while it was never meant to in the first place, when the path was shown, it at least referenced the workbook the formula was in (i.e. the same workbook) which meant that, while cluttered, the formulas still worked. Now it is not only inserting an unwanted file path, but one for a completely different workbook in a completely different location.

I suppose an example may be helpful:

formula I want and entered in the first place:
=100-Y1096

formula I used to get:
=100-'C:\Users\jonfry\Microsoft\Excel\K2 Kiln Data 26-8-14.xlsb'!table1[[#This Row],[total waste %]]

formula I know get (referencing a copy of the workbook in a different location):
=100-'C:\Users\jonfry\AppData\Roaming\Microsoft\Excel\K2 Kiln Data 26-8-14 (version 1) (version 1).xlsb'!table1[[#This Row],[total waste %]]

Now imagine a longer formula, this is a nightmare.

I have noticed that if I manually type in cell data into formulas rather than click onto them, it behaves itself.

 

[Andrew Poulsom]

How are you opening the workbook? What version of Excel are you using?

 

[BSWJON]

Opening via shortcut on my desktop to the file which is in my documents, using excel 2007. (Is that what you meant?)

 

[Andrew Poulsom]

In Excel 2007 I get only this:

=1-Table1[[#This Row],[total waste %]]

Do you still get the same problem if you open the workbook in Excel rather than via a shortcut?

 

[BSWJON]

it seems to behave itself if I open from file location in excel rather than from shortcut. I don't understand why it even needs to insert the Table1[[#This Row],[total waste %]] though, it never used to.

 

[Andrew Poulsom]

Excel will use structured references like that if the formula is in a Table. You can turn that feature off by unchecking 'Use table names in formulas' on the Formulas tab of the Excel Options dialog.

 

[BSWJON]

ahh that's going to make things a lot easier! Thank you!