Static Formulas in Excel

Question

I have a formula in excel that looks like such: =COUNTIF(Imports!$B$2:$B$999,"1")

It works like I want it to but part of my manipulation of the spreadsheet requires me to insert new columns in the sheet that formula is referencing. When I do that it changes the formula from column B to column C which is actually not what I want. I want it to reference column B no matter what. How would I achieve this in Excel? Thank you!

Answer 1

Use INDIRECT to reference range from its text address:

=COUNTIF(INDIRECT("Imports!$B$2:$B$999"),"1")