Refer to columns by "field headings" in an Excel Table / eliminate dynamic named ranges

**carsto** · 08-05-2015, 04:55 PM

I have a “database” in excel with 193 dynamic named ranges
Most columns are a dynamic named range. Some use the OFFSET & COUNTA functions and some use the INDEX & COUNTA functions.

In my calculating workbook I have many formulas such as the one below:

{ = IF(ISERROR(INDEX(‘CCDatabase.xlsm'!RngDbRMSL,MATCH(1,(‘CCDatabase.xlsm'!RngDbRMCD=$CU24)*(‘CCDatabase.xlsm'!RngDbRMG = $CH24)*( ‘CCDatabase.xlsm'!RngDbRMSL>=$CP24),0),1)),
MAX(IF(‘CCDatabase.xlsm'!RngDbRMCD=$CU24,IF( ‘CCDatabase.xlsm'!RngDbRMG = $CH24,’CCDatabase.xlsm'!RngDbRMSL))),
INDEX(‘CCDatabase.xlsm'!RngDbRMSL,MATCH(1,(‘CCDatabase.xlsm'!RngDbRMCD=$CU24)*( ‘CCDatabase.xlsm'!RngDbRMG = $CH24)*(‘CCDatabase.xlsm'!RngDbRMSL>=$CP24),0),1)) }

(FYI, “RM” is the code for the sheet name and “SL” is the code for the column name so I know what the named range means and it’s location.)

Unfortunately the database has to be open for most of my formulas to calculate.

If I change my ranges of data in the database to Excel “TABLES” (Insert > Table) can I then somehow refer to the columns by their headers and eliminate my dynamic named ranges?

**skywriter** · 08-05-2015, 06:05 PM

If you type = and then select the header and the existing data in that column in a table Excel will show you the syntax you need to refer to the data in the column. I have a table named ExerciseData, one of the headers is named TotalReps, If I just select the header I get:

Formula:

Please Login or Register  to view this content.

If I select the header and all the existing data in that column I get:

Formula:

Please Login or Register  to view this content.

To add up the column and have it be dynamic I type =Sum( and then select the header and the data), I then get a formula that updates when I add to the table. The formula looks like this:

Formula:

Please Login or Register  to view this content.

So this should give you an idea of the syntax required if you want to type your formulas as opposed to selecting the data, which is the easiest since Excel will do the hard stuff for you.

Good Luck.

Refer to columns by "field headings" in an Excel Table / eliminate dynamic named ranges

LinkBack

Thread Tools

Rate This Thread

Display

Refer to columns by "field headings" in an Excel Table / eliminate dynamic named ranges

Re: Refer to columns by "field headings" in an Excel Table / eliminate dynamic named range

Thread Information

Users Browsing this Thread

Similar Threads

Dynamic named range that expands "horizontally"

Display "TEXT" in "Values" field in PIVOT TABLE using VBA!!

Display "TEXT" in "Values" field in PIVOT TABLE using VBA!!

Error when "mirroring" named ranges between worksheets

How to use named ranges in VBA code using "Columns" and a variation on "Range"

Pivot Table - How to "add a data column" "of the same field" "across multiple sheets"

Set the "RefersTo" property of Named Ranges

Bookmarks

Bookmarks

Posting Permissions