INDIRECT formula

[qvfr034]

Hello.

I'd love some help with "baking" the indirect formula, as i've never worked with it before.

My formula is as follows:

=IF(COUNTIFS($X$100:$X$500;$AC3;$W$100:$W$500;">"&1)>1;"2";IF(COUNTIFS($X$100:$X$500;$AC3);"1";"0"))


I have a VBA macro for endusers to create a new row

Range("A100").Rows("1:1").Insert shift:=xlDown

I need the count formula to be intact even after users input new rows, or delete previous rows.

 

[Useful]

Re: Help with INDIRECT formula

Hello,
to count formulas you may use isformula and sum functions.
=SUMPRODUCT(--ISFORMULA(your range goes here))

 

[qvfr034]

Re: Help with INDIRECT formula

Sorry this isn't what i'm looking for.
It's my understand that I need to use Indirect to keep my current formula intact, since Excel changes the range whenever users insert/delete rows.

 

[Useful]

Re: Help with INDIRECT formula

Sorry this isn't what i'm looking for.
It's my understand that I need to use Indirect to keep my current formula intact, since Excel changes the range whenever users insert/delete rows.

Not so clear for me. About Indirect
For better understanding it needs to be explained as more detailed.

 

[Jonmo1]

Re: Help with INDIRECT formula

You just have to put the ranges in quotes in your indirect functions.

change
$X$100:$X$500
to
INDIRECT("$X$100:$X$500")

You will have to do that to EVERY range reference in that formula.
And indirect being a volatile function, this will decrease your sheet's performance.

Since you're already using VBA to insert rows, I would suggest using the same vba code to just re-enter the formulas correctly after you've inserted rows.
Or better yet, avoid inserting rows in the first place.
Try copying and pasting data from rows 1:10 to rows 2:11, then clear contents of row 1.