Brackets in formulas
Anybody know what this formula means?
=SUBTOTAL(109,[Meals])
You'll see this (and others like it) if you open the built-in template named ExpenseReport found in Excel 2007.
Presumably, Meals is a range name, but it doesn't turn up in the list of range names for the workbook.
Any what do the brackets mean? The Excel Formulas Book (by John Walkenbach) is a pretty good source for all things formula-related, but he says not a word about it.
Any ideas?
Brackets in formulas
I utilize tables often in excel. With the correct option checked, Excel automatically names ranges within formulas if I select cell ranges to insert into functions. When dealing with an entire column of the table, the resulting formula inserted by Excel always utilizes brackets. For example:
=SUMIF('YTD Actual'!$B$2:$AF$9999,'ACTPROJ Annual'!AC$5&AC$6,Table_Query_from_MS_Access_Database[NetRevenue])+SUMIF('YEnd Proj'!$B$2:$AG$9999,'ACTPROJ Annual'!AC$5&AC$6,Table_Query_from_MS_Access_Database4[NetRevenue])
However, after a quick cursory glance I don't see any formulas in which this is the case without an adjacent table name as in your example.
Hope this helps.
I know one more
Reference to value in table loks like this:
=tbl1[clmn1]
When you take value within the table it looks like this:
=[clmn1]+[clmn2]
[] brackets
Cyril - I think you're onto something...
[] definitely seem to do something in relation to data tables
I had a look, but couldn't replicate the example.
Can you ?
Here you are
I've made a post about it
Use brackets with data tables
template
hi Dennis
There are only 2 places I have seen these brackets before:
1. In referring to a different workbook within a formula:
=[Book1.xls]Sheet1!$E$13
2. In VBA, you can use [MyRange] as a substitute for Range("MyRange")... but I don't recommend doing that as it actually tells VBA to evaluate what's within the brackets.
SUBTOTAL(109,RANGE) sums visible cells in that range, but if I enter SUBTOTAL(109,[meals]), I get an error even if meals is defined as a range... local or global.
Does this formula actually work in the template ?
What happens when you bring up the formula box ?
Nick
It is total row formula in Data Table
Hi all,
Third case: bracketed column name can be seen when you choose "Show Total Row" with Data Table. When you right-click on Data Table and choose "Show Total Row" and choose "SUM", this is what you get within a Total cell. "Meal" within brackets refers to column "Meal" within Data Table, not a named range. So, "SUBTOTAL(109,[Meals])" means SUM of 'Meals' column, where "Meals" is a column within Data Table.
Sum cell was probably copied from a Data Table, and that is why it looked strange.
Look at example where I have replicated it: http://excelexperts.com/example-bracketed-column-name