EXCEL is changing the formula

[SeniorTom]

The data I'm tracking is in Column G through DC or DD. I have this array formula that I establish in Column C and it covers my data through Column DY (an overkill to cover my bases). Each month I add 8 or 9 columns to the right of column DC (golf play dates) and remove the same month from Column G Through O. This gives me 12 months of play date information. What I have run across is, as I add and remove the columns, EXCEL adjust the formula and no longer keeps the original right most column. Previous formula went to Column DY

Is there a way to freeze the formula in Column C or can I use VBA to reestablish the original "Array" formula in rows 7 through 101?


=SUM(IF(ISNUMBER(SEARCH({0;1;2;3;4;5;6;7;8;9},F7:DR7)),1,0))
.
.
.
=SUM(IF(ISNUMBER(SEARCH({0;1;2;3;4;5;6;7;8;9},F101:DR101)),1,0))

Your thought will be appreciated.

Regards,
Tom

 

[Peter_SSs]

I'm actually wondering exactly what that formula is doing? Or rather, what your data in columns F, G, H etc is like? Could we have small sample?

In any case, you could try this array formula
=SUM(IF(ISNUMBER(SEARCH({0;1;2;3;4;5;6;7;8;9},INDEX(7:7,6):INDEX(7:7,122))),1,0))

 

[SupremeDr]

Attempt using $F$7:$DR$7 and $F$101:$DR$101

Not sure if Excel will change that or not but try it out.

 

[SeniorTom]

I'm counting the number of times a person is playing golf (Number) and then I look at the drives (x) to calculate when they are due to drive. The formula counts the plays.

<colgroup><col width="107" span="6"></colgroup><tbody>
[TD="class: xl68, width: 107"]2zSNAPP[/TD]
[TD="class: xl68, width: 107"]SNAPP
[/TD]
[TD="class: xl68, width: 107"]3zSNAPP[/TD]
[TD="class: xl68, width: 107"]x4zSNAPP[/TD]
[TD="class: xl68, width: 107"]SNAPP[/TD]
[TD="class: xl68, width: 107"]1zSNAPP
[/TD]

</tbody>

 

[SeniorTom]

SupremeDR.

Thanks, but no difference.

 

[SeniorTom]

Peter_SSs,

Thanks, the index is holding the address after the adds and deletes, but It takes some figuring out to see the actual range. For the benefit of other users, I would like to show the addresses a little more clearly. Is there a way to index using F7/DY7 type addressing? I've tried but have had no luck.

Thanks again and I will use it as is, if needed.

Regards,
Tom

 

[Peter_SSs]

I'm counting the number of times a person is playing golf (Number) and then I look at the drives (x) to calculate when they are due to drive. The formula counts the plays.

<colgroup><col width="107" span="6"></colgroup><tbody>
[TD="class: xl68, width: 107"]2zSNAPP[/TD]
[TD="class: xl68, width: 107"]SNAPP
[/TD]
[TD="class: xl68, width: 107"]3zSNAPP[/TD]
[TD="class: xl68, width: 107"]x4zSNAPP[/TD]
[TD="class: xl68, width: 107"]SNAPP[/TD]
[TD="class: xl68, width: 107"]1zSNAPP
[/TD]

</tbody>

Ok, so I assume that no cell can contain more than one digit?

Peter_SSs,

Thanks, the index is holding the address after the adds and deletes, but It takes some figuring out to see the actual range. For the benefit of other users, I would like to show the addresses a little more clearly. Is there a way to index using F7/DY7 type addressing? I've tried but have had no luck.

Thanks again and I will use it as is, if needed.

Regards,
Tom

You can use INDIRECT but be aware that this is a volatile function so may slow your sheet if used a lot.

So the formula in C7, also array-entered, would be

{=SUM(IF(ISNUMBER(SEARCH({0;1;2;3;4;5;6;7;8;9},INDIRECT("F" & ROW(C7)):INDIRECT("DR"&ROW(C7)))),1,0))}

 

[SeniorTom]

Peter,

Thanks, I'm using the INDEX for now, it works fine. I will experiment with the INDIRECT as the link seems to discourage its use. I mark the thread as solved.

Again Thanks for your help.

Tom

 

[SeniorTom]

Solved !

 

[Peter_SSs]

Peter,

Thanks, I'm using the INDEX for now, it works fine. I will experiment with the INDIRECT as the link seems to discourage its use. I mark the thread as solved.

Again Thanks for your help.

Tom

You're welcome.
The INDIRECT should be fine unless you have many thousands of them in the sheet and/or a great deal of other calculations going on. Anyway, you have a couple of choices.