CODEWITHSUNDEEP
excelexcel-formula
Lawrence Tan
Lawrence Tan

Reputation: 507

How to fix search to header cell?

I have a table like this:

Name Response Thursday
A Monday, Thursday Yes
B Tuesday No
C Wednesday No

This is an output of a Google Form response that I use to collect data, In Column C, every cell contains this formula:

=IF(ISNUMBER(SEARCH("Thursday",B2)),"Yes","")

To populate the "Yes". However, its quite cumbersome to replicate this if I want change "Thursday" to something else. When I try to insert it as C1, as I populate down the cells, it auto increment to C1,C2,C3 etc.

Is there a way to fix the formulate to

=IF(ISNUMBER(SEARCH(C1,B2)),"Yes","")

Across all cells and just have B2 increment?

Thanks!

Upvotes: 0

Views: 37

Answers (1)

Louis
Louis

Reputation: 86

The cell reference that you are using, such as C1, is relative to the formula position. So when you copy the formula one row down, C1 becomes C2 and so on. To prevent this you need to make the row and or column address absolute so that C1 remains C1 when the formula is copied somewhere else. To make a row or column absolute, precede the row and or column by the character $. To keep the row absolute, you would use C$1 instead of C1. The key F4 will help cycle through the option when the cell address is selected in the formula.

Upvotes: 2

Related Questions