Formula vs Name

[foxguy]

I have a range that seldom changes values. It has over 200 cells in it.
The range covers C22:C230, but can increase in size on occasion. But it will not change any values over 99% of the time.

I would prefer not to name the range because it only contains intermediate calculations between the raw data and the finished display.

But if it's more efficient then I will name it.

The only cells that refer to the range are D22:D230. If I don't name the range then the formulas in column D would look like this:
CodeCount is a name that is calculated on a different worksheet.

Please Login or Register  to view this content.

If I name the range then the name and formulas in column D would look like this:

Please Login or Register  to view this content.

Since the values in column C never change 99% of the time, I don't think I need to worry about the Count() being executed during day to day work.

Is there any other reason that I should consider naming the range in column C?

[royUK]

Why would using a named Range be a problem? It makes formulas easier to write

[foxguy]

It's not so much that it's a problem. It just gets in my way at times.

The sheet has a lot of names attached to it, plus several global names. When I want to paste a name into a formula I have to scroll down to find the name to paste. Since this range will never be used in any more formulas it just clutters up the name list causing me just a tiny irritation and interferes slightly with my work.

I recognize that sounds petty, but I have several situations like this and every one causes me just a tiny irritation that I would like to get rid of, as long as it doesn't cause the workbook to be less efficient.

[teylyn]

Offset is volatile and will cause the cells to recalculate every time anything in the worksheet changes. You'd be better off using Index instead.

[foxguy]

I admit that I don't think of that very often, but I'm lost.
How can I define a dynamic range with Index?

[teylyn]

Like this, maybe

=Match($A22,$C$22:index($C:$C,CodeCount+22),0)

or as a range name

refers to =$C$22:index($C:$C,CodeCount+22)

[foxguy]

I like it. I learn something new every day.

[teylyn]

foxguy, please stop quoting whole posts. Use the reply button, not the quote button to respond.

[foxguy]

Like this, maybe

=Match($A22,$C$22:index($C:$C,CodeCount+22),0)

or as a range name

refers to =$C$22:index($C:$C,CodeCount+22)

Using index to define a dynamic range is new to me.
Can this RefersTo be simplified?

Please Login or Register  to view this content.

I'm trying to count all the non empty cells below $C$8, by only referencing $C$7 in the formula.
I want to make sure that if a row or column is inserted anywhere in the sheet that the name will still count the correct range.

[teylyn]

Does C7 hold a number? Do you want to use that number in the Index, or the row number of cell C7 (which is 7). Since you reference C7 absolutely, you may as well use a 7 instead.

Or do you want to use the number stored in that cell? Then don't wrap it in a Row() function. But you say above you want to count in all rows below row 8, so you start in row 9, right? Then just use C9.

If you use Index for a one column or one row range, you don't need to enter the third parameter.

Using Counta all the way down to the last row of the sheet is not the most efficient. You could set up the index to find the last populated cell in the column, instead of the last row in the spreadsheet.

So, I'd probably do it this way (count all non empty cells below C8)

If column C is numeric data:

=COUNTA($C$9:INDEX($C:$C,MATCH(99^99,C:C,1)))

If column C is text :

=COUNTA($C$9:INDEX($C:$C,MATCH("zzzz",C:C,1)))

[foxguy]

teylyn;
I like the Match("zzzz",...) for columns with text in them. I could probably use Match(999999,....) in most of the other situations.
What I'm doing is trying to name a dynamic column of data with a heading in cell $C$7. The data starts 2 cells below the heading. Each sheet will probably use a different row for all the headings.
Since I may insert a new row(9) at some time in the future, I don't want $C$9 referenced.
I could use $C$8 in my formula, but I've just gotten used to using the heading cell for my anchor for the name.

I've also gotten used to using all 3 parameters in INDEX so that anyone reading my code cannot possibly misunderstand. I'm constantly reconsidering that decision, but I always reach the conclusion that I want to keep using all 3 parameters.

So it looks like my RefersTo will be:

Please Login or Register  to view this content.

It's not really easier to compose but it will be more efficient since it's not counting all the rows in the sheet.

It just now occurred to me that I could have a name "HR" (short for "HeadingRow") RefersTo =ROW($C$7) then my formula would be

Please Login or Register  to view this content.

I believe that this will not be volatile and also allow me to insert a row or column any where in the sheet (except between row(7) and row(8)) and my formula will automatically adjust accordingly. And it's easy to create a name for data in columns D-H by just changing the C to the appropriate letter.

I also just realized that I could have a name "BR" (short for "BottomRow") RefersTo: = MATCH("zzzz",$C:$C,1) then my formulas reduce to

Please Login or Register  to view this content.

And I can see the entire formula in the RefersTo box when I'm creating the name.

Thanks for the help.

[teylyn]

If you want to use Index instead of a direct reference to C9 to ensure the range always starts in row 9, then using C7 in the Index won't help you much, since that reference will adjust if rows are deleted/inserted. To make row 9 the absolute reference, no matter what, just use

Index(C:C,9)

Whenever you use cell references, no matter whether via range names or directly, they will adjust for row deletes/inserts

[foxguy]

teylyn;
Row(9) may become Row(10), but Row(7) would remain Row(7), at some point in the future, but the data would still start in row(9), because I may insert a row between 8 & 9. The only cells that are guaranteed not to move relative to $C$7 are Row(7) & Row(8).

[foxguy]

teylyn;
The data will always start 2 rows below the heading cell. The heading cell may move (usually not, but just in case) and the data may have rows inserted anywhere inside it, but it will always start 2 rows below the heading cell, so I only want the heading cell referenced directly. If the heading cell moves the data will always move with it.

[foxguy]

teylyn;
I just tried to implement my new ideas and ran into a problem.
I wanted to created a name "BottomRow" RefersTo: =Match("zzzz",$C:$C,1) and realized that if I move the table there may not be any data in $C:$C and trash my names.
Is there some way to refer to the $C$7.Column, so that if $C$7 gets moved then name would look in the new column for the Match?

In case that's not clear:
My heading is in $C$7 and the data is in C9:C202. If I cut and past the entire table so that the heading is now in $E$9, I would want BottomRow RefersTo: to automatically change to =Match("zzzz",$E$E,1)

Any help?

[teylyn]

Don't cut and paste the table. Insert columns instead, then the formulas will adjust.

[foxguy]

teylyn;
That's what I do, but I can't guarantee that the owner of the file will always do it. I have had a client move data around and my formulas crashed and made a real mess of things. I'm trying to make all my formulas guard against it happening again.

Is there some way to refer to the column that contains $C$7, so that if it's cut and pasted somewhere else the formula would still refer to the correct column?

I'm trying to play around with R1C1 addressing to see if I can do it that way, but any help would be appreciated.

[foxguy]

That's why I was using Offset in the first place, I only had to name one cell and if it got cut and pasted all the formulas would adjust accordingly, but I really like the idea of using Index instead.

[foxguy]

teylyn;
Well, using Index() doesn't work like I was hoping. I can't use $C:$C in the formula because if $C$7:$C$202 is cut and pasted the formula doesn't follow it.
Until I can figure out how to refer to $C$7.Column I'll have to keep using Offset().