How to reference values in a column whose values change dynamically

I have this worksheet which contains a single column of values. The values in this column are those "studies" which i want to exclude from my calculations.

I have another worksheet which references this studies_to_exclude sheet/column

Please Login or Register  to view this content.

I know referencing an entire column in this way is not the best way to do things.....i was wondering how else to do it......ie the reference Study_Exclusion!C:C ....is bad practice.

Any suggestions?

thanks for the info. It really helped.

What about functions like "Countifs"..........if i am referencing a column and not a range using this function......is this bad? Should i do a defined name range in this case?

COUNTIFS is pretty efficient, but it is approaching the realm of Array formulas. Depending on the number of COUNTIFS-like formulas and the size of your data, it might be worthwhile to use dynamic named ranges.

The link below is an excellent article on the INDEX function. I recommend you read it all, but the section that deals directly with the subject at hand is towards the bottom under the header "The Dynamic Range".
http://www.excelhero.com/blog/2011/0...ing-index.html

Another method is to use OFFSET. The link below will detail this method.
http://www.ozgrid.com/Excel/DynamicRanges.htm

I prefer the INDEX method because it is less volatile.

Whizbang, thanks for the info.

I will read the articles you listed.

One question......index vs offset.......you said index less volatile.....what did you mean by this? I am just learning about both and i did not read about this difference.

ok just started reading the index article and foudn the answer to volatile


"Microsoft Excel functions are volatile, which simply means that all formulas that include these functions will recalculate every single time anything on the worksheet changes."

great article on index. thanks!

The link below will give you more information on volatile functions.

http://www.decisionmodels.com/calcsecretsi.htm

In short, OFFSET calculates every time there is a change to the sheet, whether or not that change affected the cell using the OFFSET function. INDEX calculates every time the workbook opens (when used on the right side of a reference), or when a change is made affecting the cell containing the INDEX function (ie precedent).

So, INDEX will cause fewer calculations, thereby speeding up your workbook when compared to OFFSET.

after reading the index article i have one question:

Creating dynamic range:
From the article using say the index function. Instead of using

Please Login or Register  to view this content.

lets suppose that i have 100 entries in ColA

instead of A2:A100 replace this with

Please Login or Register  to view this content.

so my formula would now be

Please Login or Register  to view this content.

My question is ......is it better to do the dynamic range in the manner as above or is it better to use a defined name range which is dynamic as you mentioned

Dynamic Named Ranges will still calculate for every cell that uses the Name. So, there isn't any gain in performace for using Named Ranges.

For example:
Named Range ColA =A2:INDEX(A:A, COUNTA(A:A))
Cell B1 =COUNTIFS(ColA, ">" & 0)
Cell B2 =COUNTIFS(ColA, "<=" & 0)

Will calculate the same as
Cell B1 =COUNTIFS(A2:INDEX(A:A, COUNTA(A:A)), ">" & 0)
Cell B2 =COUNTIFS(A2:INDEX(A:A, COUNTA(A:A)), "<=" & 0)


BUT, named ranges allow for "self documenting". Pretend you put aside this workbook for a year and come back to it. You read the formula:
=COUNTIFS(A2:INDEX(A:A, COUNTA(A:A)), ">" & 0)

What does that mean? Obviously we are counting the occurances in column A that are greater than 0, but why? What is in column A? Why do we care if they are greater than 0? It is simple enough, in this case, to just look at column A, and maybe the entire table, to determine the purpose of the formula. But wouldn't it be better if the formula read this?
=COUNTIFS(CustomerFeesDue, ">" & 0)

We see right away that we are counting the number of customers who owe fees, without having to refer to the table at all. Naming ranges or formulas are a great way to make your workbook more readable.

Another significant gain is that you can change the formula once, rather than throughout the workbook.

Pretend that we filled a workbook with formulas that use the dynamic range method described. But then you shuffle things around and you need to change the formula to reflect the changes. You could go through and update each and every formula, or you could just use a named range/formula and update only the Name.

Really, as far as I know, you don't gain anything performance-wise by naming your ranges/formulas, but you do gain readability and maintainability.

very good explanation! Very good! Made a lot of sense. Thanks.