Searching column headers instead of column letters

Copper Contributor

I have developed a formula that searches inside an identified column letter (AB or F) for information. Sometimes my coworkers add or remove columns from the spreadsheet which causes the formula not to work. 

If I know that the column i want to search every time is called "Day of the week". how do I change the column letter (AB) to find the column "Day of the week" and then search that column for the info.

 

=SUMPRODUCT((LEN('TAB NAME'!AE:AE&",")-LEN(SUBSTITUTE('TAB NAME'!AE:AE&",",A1&",","")))/LEN(A1&","))

 

The data has multiple entries in the cell (Monday, Tuesday, Wednesday) so to get a count by day i use the above formula. just trying to prepare for standardized header name (Day of the week) with a column that varies 

 

 

------------------------------------------------------------------------------

Update

Sheet1 and Sheet3 is the data and Sheet2 and Sheet4 is the formula. When a new column is added in Sheet3 it messes up the formula in Sheet4 because the column that was B is now C. I would like the formula to look for column month instead of B. 

 

I hope this helps

 

 

 

3 Replies

Hi @austin93,

 

You could use the Name Manager to define a name for the columns you are using your formula. 

 

Step 1: Define a name for the column you wish to use in your formula. Step1-NameManager.PNG

Step 2: Use the name defined instead of the column reference in your formula. 

Step2-NameManager.PNG

For clarity, please attach a sample file with your data and desired results.

Hello Austin,

In Sheet2!C2 of the attached file, I modified the formula to this:

=SUMPRODUCT(--ISNUMBER(SEARCH(B2,Sheet1!A:A)))

I see no error in the results in Sheet2 although you insert a column in Column B of Sheet1 because the relative references will automatically adjust to return the correct results.