INDIRECT formula

qvfr034

New Member
Joined
Oct 5, 2017
Messages
2
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Re: Help with INDIRECT formula

Hello,
to count formulas you may use isformula and sum functions.
=SUMPRODUCT(--ISFORMULA(your range goes here))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,993
Messages
6,175,842
Members
452,675
Latest member
duongtruc1610

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top