Formula bar view changes when document is saved and reopened

4

I have an Excel 2007 document with a lot of formulas of the format:

SUMIFS(view1[COLUMN1],view1[COLUMN2],$C13,view1[COLUMN3],I$10)

but when I save the sheet and reopen it each formula is changed to the much less readable (but still accurate) format:

SUMIFS(data!$F$2:$F$65536,data!$B$2:$B$65536,$C13,data!$K$2:$K$65536,I$10)

The formulas all still work, I just want to keep the view as view1[COLUMN1] to make it more readable and to make edits easier.

How do I stop Excel from automatically changing how this shows up in the formula bar?

Bryan Schwimmer

Posted 2015-02-04T20:48:53.630

Reputation: 43

If view1 is a named table, go to the Office button-->options-->formula tab-->second header from the top and you'll find a box option with something like "use tables name in formula" (sorry I cannot be more precise, my Excel version is not in English) – P. O. – 2015-02-06T21:32:40.343

Answers

1

Click the Microsoft office Button and select Excel Options. Choose the Formulas category and in the "Working with formulas Section", select the "Use table names in formulas" option.

Hojjat Jashnniloofar

Posted 2015-02-04T20:48:53.630

Reputation: 26

Asked: 2015-02-04T20:48:53.630

Viewed: 67 times

Active: 2015-02-09T11:24:27.390