Understanding Tables and their Impacts on Formulas

Relatively easy one here, I've never used tables heavily, because up until Excel 2013 there wasn't much reason (in my humble opinion) now because of Slicers, Data Models, Power Pivot, etc... I can see an advantage. Here's my question....

I've become very used to cell references in formulas, take for example the below array (CTRL+SHIFT+ENTER):
{=AVERAGE(IF((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0)),AircraftData!$F$2:$F$301))}

Now "AnalysisEngine!$B18" is in my newly created table. But I notice that it hasn't changed the name of that cell reference to the name of the header Column "[@Asset]". What I'm trying to figure out for my self is if I Love/Hate that it doesn't do that automatically, and if I should change it.

So basically, should I be using them instead of cell refs, and what do I need to be aware of if I do? For example, pressing F4 no longer makes something absolute in position.... what if I wanted that.
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I will advise using the table names in the formula rather then ranges because table makes it compartively more friendly then the normal ranges when you add new data to it as table automatically extend where as in the formula you have to go manually and expand them...

Here are 10 benefits of table for your reference which can give you more context around it..

Link to tables

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Alrighty, thank you, just cautious because I don't have the greatest amount of time to learn the nuances. For example, I just discovered that I cannot use the table names for a rule that I made in conditional formatting...
0
Saurabh Singh TeotiaCommented:
Polak..

Let me tell you this everything has its pro's and con's... Now for conditional formatting i won't be able to comment about it till the time i see or know what you are talking about...

Saurabh...
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

-PolakAuthor Commented:
As long as there are more pros.... haha

It's wasn't really a question, just simply go try and create a rule on a table where you set the range of the rule to apply to [HeaderName@] you'll see that the rule is created. However, it sets [HeaderName@] back to a normal range.
0
Saurabh Singh TeotiaCommented:
Will try that in sometime in a middle of a code right now which need to complete.. and will post here my findings..
0
-PolakAuthor Commented:
Sure thing when you do can you also let me know why some structured references in my table appear as [@[XXXXX]] where others just appear as [@XXXXX] when I click the cell to go into a formula....?
0
Saurabh Singh TeotiaCommented:
Okay i did some findings and here are your answers...

1. You are right you need to give range as in range format..Because you can't give that value like [@[xxxx]] outside table as it won't recognize that..and it will give you an error as it recognizes that in the table only..

2. If you give a value which has a space or character in between it will take it as [@[xxxx]], However if doesnt have a space or special char it will take it as [@xxxxx].. to give you an example

If i write Header1 it is shown as-->=[@Header1]
Now if i write header-1..it is shown as -->=[@[Header-1]] or even header_1 shown as-->=[@[Header_1]] and header 1 shown as-->=[@[Header 1]]

Hope this helps..it sort of programmatic language of office that how it reads it if it has spaces...
0
-PolakAuthor Commented:
Cool, all good stuff to know, this syntax is confusing wish they would have just kept the $$ for absolutes.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.