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

[carsto]

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]

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:

=ExerciseData[[#Headers],[TotalReps]]

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

=ExerciseData[[#All],[TotalReps]]

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:

=SUM(ExerciseData[[#All],[TotalReps]])

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.