Structured References

The Horse

Board Regular
Joined
Feb 20, 2004
Messages
68
Trying again on this...

Does anyone know if you can use a structured reference (e.g. [column name]) from an Excel Table as part of a named formula ?

Tried and failed on this...

Thanks

Mick the Horse
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Yes you can: =SUM(Table1[ColumnName]) should work fine as a named formula for example.
 
Upvote 0
Please explain exactly what you are trying to do.

This named formula worked for me:
name of MYTABLEN
definition =COUNTA(Table1[ColName])
 
Upvote 0
Thanks...I have a database with (for simplicity's sake):

Column A - total costs (Costs)
Column B - worked days (Days)

Obviously a1/b1 gives me the cost per day

However, in order to drive everything from a single formula, I want to use a named formula instead, with the database as an Excel Table.

The formula converts to @[Costs]/@[Days] - which is fine as a standard formula. However, if I then attempt to use this formula, with an error check for division by zero as a named formula (paste iferror(@[Costs]/@[Days],0) into a range name called CPD, I get the error message "The name that you entered is not valid".

Doing it as iferror(a2/b2,0) works fine.

What's the problem with the structured references ?

Thanks

Mick the Horse
 
Upvote 0
Why not add the formula to the table? It will automatically replicate for all rows.
 
Upvote 0
Rory - that's all very well, but some formulae will be a lot more complex (sumproducts for one thing). What I want is the ability to have a single range name for any given formula (makes it very easy for audit purposes), without having to worry about whether the column reference a2:a20000 is sufficient for future needs - hence the attraction of using [column name] and @[column name] instead of a dynamic range name.

Thanks

Mick the Horse
 
Upvote 0
In that case, you need to specify the table:

iferror(Tablename(@[Costs])/Tablename(@[Days]),0)
 
Upvote 0

Forum statistics

Threads
1,224,570
Messages
6,179,610
Members
452,931
Latest member
The Monk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top