Комментарии:
What do to if we want to display all the values in that column?
Like displaying the entire 'Weekends' column in table 'TblRow' starting on other sheet?
how do i locate where a table is, which is used in a formula, in an excel workbook that contains multiple sheets, in a structure reference, where the formula is in the different sheet with the table being referred to?
ОтветитьtblFANTASTIC!!
ОтветитьShort and sweet - nice.
ОтветитьHow do I trace back to the table ? If someone is new to the table and they see tblraw, how do they know where that is in 50 sheets ?
ОтветитьGreat tips. I do something similar. (Background: Our principal data sources are Access apps, Excel apps, & SharePoint list & libraries. I use VBA (both Access & VBA), M/Power Query, dynamic arrays, Power Apps (starting to work with Dataverse), & some DAX/Power Pivot). My naming conventions: in Access (tbl for tables, qry for queries); in Excel (t for user-entered tables, pq for PQ-generated tables, r for named ranges); in Excel VBA (rg for range objects, lo for list objects); in LET formulas (v for variables, x for comments). These conventions have helped immensely when working in the overlap among tools, especially when using VBA to automate apps (e.g., defining ranges in VBA based on hard VBA assignments v. named ranges v. tables). What has been helpful both in Excel & in VBA is the “t” v. “pq” prefix for tables; I know instantly whether table structure/data are maintained manually (t) or programmatically (pq).
ОтветитьNice one! Structured referencing for tables can be confusing, until you learn the rules. Thanks for the tips :)) Thumbs up!!
ОтветитьWhen using table references, if you use a standard table reference as shown, when you drag to copy the formula, the table reference is not fixed, so selecting the sample AVERAGE cell and dragging it to the right to copy the formula results in the copied formulas to follow the sequence in the table (Discount %, Total, Store ID). Occasionally I've wanted to use a fixed reference (without turning off structured references) so that the formula can be dragged/copied without changing the (column) reference. In this case the formula would be
=AVERAGE(TblRaw[[Qty]:[Qty]])
which, when dragged/copied in any direction would not change. Why MS hasn't set up the F4 key to automatically fix a table reference the way it does for cell references I don't understand. A quick video of how that works would be helpful.
Great Tips!Always Name Tables It Makes Life So Much Easier...Thank You Sir :)
ОтветитьGreat tutorial! Thanks!
Ответить