Formula problems with result from a query 2

[SUSANVV]

I have an Excel 2010 workbook that was created from using a Microsoft Query on another Excel 2010 workbook. When I try to do any formula, any cells in the formula that are in the row that the formula is in contain an @ and the field name, e.g. =[@Tenant1]+[@Tenant2]. Does not seem to affect the outcome of at least simple formulas. Is there any way to get rid of this. Any other cells NOT in the same row as the formula have the proper cell referencing. when copying the formula down a column the formula works correctly but every formula has the [@Tenant2] in it. Any ideas are appreciated. Thank you.

Sue

 

[SkipVought]

hi,

"Is there any way to get rid of this?"

This is like saying, "I woke up this morning, looked out my window, and discovered a brand new Lexus, with a bow and a note telling me that this is a gift to me!"

"Is there any way to get rid of this?"

I can understand you asking, "what is this [@zzz] stuff that gets into my formula?" but why would you simply dis it?

I think its pretty kool stuff! Fact is just yesterday I had inserted an MS Query in a sheet AND had added several columns of formulas and subliminally noticed the @. When I read your post this morning, the light went on. Excel version 2007 (into which I went like Linus dragging my Excel version 97-2003 blanket) and after days of struggle discovered a vast vista of new features, the BEST, IMHO, being Structured Tables. Well THIS feature with the [@zzz] references is a new feature of Structured Tables.

Here's s little "experiment:"
Select in your QueryTable. Notice that in the Ribbon a new Context Sensitive TAB pops up called Table Tools Design. SELECT the Design TAB and notice the Table Name in the Properties GROUP. Its a DEFAULT name. I often change it to something that is meaningful. Theres a WHOLE LOT of good stuff to discover in the properties within this TAB.

When you enter a formula ANYWHERE in the workbook and refer to this table name, lots of good stuff happens! So enter an =, followed by the Table Name (notice Intellisense lists the available names beginning with what you have begun to type) when you have your name het TAB and [. VOLA Y'all! And there, at the bottom of that list is the @ and an explanation!!!

Now ain't that some good stuff, just for starters?

Of course, if you still want to get rid of it, just use regular A1 referencing. But you LOOSE a lot of really useful features. AND a Lexus is downright fun to drive! ;-)

 

[kjv1611]

Definitely - I'd HIGHLY suggest just getting used to the Table features for formulas and such in Excel 2007+. It's AWESOME by comparison to the old A1 method.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SUSANVV]

Hi Skip,
Thank you for your enlightening reply. This was my first encounter with an Excel QueryTable. I will, however be ditching the referencing as I am creating this workbook for use by non-excel people who will be using it as a database. They will not need the good stuff but I will be exploring that stuff for future projects. I am slready thinking of uses for that tab.
Thanks again.

Sue

 

[SkipVought]

I will, however be ditching the referencing as I am creating this workbook for use by non-excel people who will be using it as a database.

?????

You mean that your "non-excel people" will be CHANGING YOUR FORMULAS????

 

[SUSANVV]

OMG NO. This particular table has no formulas, just information. If there were formulas the sheet would be protected with the formula cells locked. This is for an apartment complex and will contain tenant and occupant data only. The information was originally in an Access database so I used the query to join three separate tables. This is just the first time I have needed to use a query to put data together. Going to do more research on them as I can see more uses. Thanks again.

 

[SkipVought]

I use lots of queries in Excel to Oracle, DB2, SQL Server, Access, Excel, Text files. Very handy indeed!

But I didn't understand why you said that You'ld be ditching the referencing???

 

[SUSANVV]

Dumb knee-jerk reaction to something that I was not used to looking at. My bad. Why is it only in the row of the formula? Still not sure I understand the value of that referencing but would like to know.

 

[SkipVought]

So the Structured Table references uses your column header values, like [Tenant1]. Well [Tenant1] refers to the entire column of data. [@Tenant1] refers to only the value in the row containing the formula.

 

[SUSANVV]

Thank you for your explanations. Very helpful.