Hello all,
I've been doing a lot of work recently with nesting formulas such as vlookup, if, sumif, countif, etc., to reference areas as large as 7-Columns x 5,000-Rows. Although most of the formula references go down to the 5,000th row, the data that I paste into these areas may only go to 2,500 or so rows. I've done this because the amount of rows can change week to week, and will growth over time just the same, so I want to make sure my formulas capture any new data rows (columns will always be 7). After a little research, I understand that I need to get smart on Structured Table References. This table structure will allow me to paste my new data into the area, and have Excel more efficiently perform calculations... without having a lot of nested formulas continually referencing empty cells.
My question is... is the "table" part of Structured Table References simply setting up a basic table, then applying the special structured syntax (ie, using table names, using brackets, colons, etc)? Or, is there a special way to set this table up? I'm using Excel 2010, how does the Name Manager (or Create from Selection) play into this? Does that create the same table?
Thank you,
-Rich
I've been doing a lot of work recently with nesting formulas such as vlookup, if, sumif, countif, etc., to reference areas as large as 7-Columns x 5,000-Rows. Although most of the formula references go down to the 5,000th row, the data that I paste into these areas may only go to 2,500 or so rows. I've done this because the amount of rows can change week to week, and will growth over time just the same, so I want to make sure my formulas capture any new data rows (columns will always be 7). After a little research, I understand that I need to get smart on Structured Table References. This table structure will allow me to paste my new data into the area, and have Excel more efficiently perform calculations... without having a lot of nested formulas continually referencing empty cells.
My question is... is the "table" part of Structured Table References simply setting up a basic table, then applying the special structured syntax (ie, using table names, using brackets, colons, etc)? Or, is there a special way to set this table up? I'm using Excel 2010, how does the Name Manager (or Create from Selection) play into this? Does that create the same table?
Thank you,
-Rich
