• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

Create a named structure reference

Folks,
I've run into a problem where I would like to learn how to create a structure name to reference headers in a table:
 
To reference the headers =Source_Data[#Headers]

I'm can't seem to connect the dots.
0
Frank Freese
Asked:
Frank Freese
  • 6
  • 4
4 Solutions
 
jkpieterseCommented:
I'm not sure in what context, in a formula, in VBA, ...?
Check out http://www.jkp-ads.com/articles/excel2007tablesvba.asp if it is VBA syntax you are looking for.
0
 
Frank FreeseAuthor Commented:
I've been trying to edit the question as follows:

=INDEX(Source_Data[[#Headers],[Spend $]:[Cost / Sale]],,selection)

and I keep getting #NAME!
0
 
Glenn RayExcel VBA DeveloperCommented:
Using a structrured reference on the table headers like:
=Source_Data[#Headers]
yields an array, so it's not a displayable value.

However, if you were using this in an indexing or array function - or wanted to create a dynamic range name, it's perfectly usable.  For example, the name of the third header lable in your table would be:
=INDEX(Source_Data[#Headers],1,3)

Regards,
-Glenn
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Glenn RayExcel VBA DeveloperCommented:
We cross-posted!  Looking at your follow-up, are you trying to do this in VBA?  "selection" makes me think so.

If so, are you trying to enter a formula in a cell with this reference or are you trying to determine the header lable based on the active cell's column on the sheet?

-Glenn
0
 
Frank FreeseAuthor Commented:
One additional question please
[#Headers] is a predefined range and one does not have to define it in Name Manager?
0
 
Frank FreeseAuthor Commented:
Actually, "selection" is a named range.
0
 
Glenn RayExcel VBA DeveloperCommented:
[#Headers] alone is not a defined range, but used with the Excel Table identifier, it can be.  And it's dynamic; will change as the table layout changes.

if "selection" is a named range, the only issue would be if the value is greater than 2 (only two columns referenced) or less than 1.

-Glenn

Resources:

Resources (Microsoft)
Using structured references with Excel tables
Use structured references in Excel table formulas
0
 
Frank FreeseAuthor Commented:
"selection" is a cell reference (I'm not having a very good Sunday)
Thanks I'm clearer now and appreciate everyone's help!
0
 
Frank FreeseAuthor Commented:
thank you kindly
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.  The links I posted are very helpful (for me, at least).

-Glenn
0
 
Frank FreeseAuthor Commented:
Those will be read!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now