How to keep a set range in a Search function

[Connie]

I am using the following function to look at numerous worksheets and count
how many times a certain number shows up:

=SUMPRODUCT(--ISNUMBER(SEARCH(A2,'Site #02-Maine Medical Center'!G2:G42)))

However, when I try to copy the function in the next cell, it always changes
my search range (ex. G2:G42 goes to G3:G43). How do I keep the range from
increasing? I always want to search this range for this particular
spreadsheet.

 

[scottgorilla]

When typing the formula and you type G2 then hit the f4 button until the G2 looks like this $G$2:$G$42


Hope this helps,


scottgorilla

 

[Glenn]

I am using the following function to look at numerous worksheets and count
how many times a certain number shows up:

=SUMPRODUCT(--ISNUMBER(SEARCH(A2,'Site #02-Maine Medical Center'!G2:G42)))

However, when I try to copy the function in the next cell, it always changes
my search range (ex. G2:G42 goes to G3:G43). How do I keep the range from
increasing? I always want to search this range for this particular
spreadsheet.

Change to absolute references.

$G$2:$G$42