Home

Searching column headers instead of column letters

austin93
Occasional Visitor

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies