Formula Help (referencing a column by name)

Dustinkli

Board Regular
Joined
Mar 26, 2019
Messages
62
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
I have a column on a spreadsheet that combines a lot of "IF" statements such as:

Excel Formula:
=IF(ISBLANK(J2),"",(IFERROR(IF(J2="Yes",2,0),""))+
IF(ISBLANK(L2),"",(IFERROR(IF(L2>0.05,1,0),""))+
IF(ISBLANK(K2),"",(IFERROR(IF(K2>0.1,1,0),"")))))

However, I would like to change it so that I am not referencing the column by rather the name of the column in row 1 so it would be something like:

Excel Formula:
=IF(ISBLANK("Calls"),"",(IFERROR(IF("Calls"="Yes",2,0),""))+
IF(ISBLANK("Time"),"",(IFERROR(IF("Time">0.05,1,0),""))+
IF(ISBLANK("Year"),"",(IFERROR("Year"(K2>0.1,1,0),"")))))

The goal here is that I can shift the columns around or delete columns or add columns and it won't affect the formula as they would find the column where the cell in row 1 is "Calls" opposed to just "J2" or "Time" opposed to "L2".
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Apart from deleting a column that is used in a formula, none of the things you are mentioning will impact a formula referencing a column letter.
Only formulas outside the table which use Column "No" are generally affected by those sorts of changes eg vlookup referencing column 3.

Having said that I am a fan of using tables and that will give you what is called "Structured Referencing" which looks like the below:-
the @ symbol signifies look in the same row the formula is in (used to be referred to as #This row

Excel Formula:
=IF(ISBLANK([@Calls]),"",(IFERROR(IF([@Calls]="Yes",2,0),""))+
IF(ISBLANK([@Time]),"",(IFERROR(IF([@Time]>0.05,1,0),""))+
IF(ISBLANK([@Year]),"",(IFERROR(IF([@Year]>0.1,1,0),"")))))

The benefits of using tables include:-
  • Formulas inside the table will automatically fill down when you add more rows.
  • The Table as a whole is a dynamic range and any functions referencing the entire table such as Pivot Tables will capture additional rows as the Table expands (after refreshing the Pivot Table)
  • Any formulas such as look ups and summing formulas using Table Column references will auto expand as additional rows are added.
To convert your range into a Table
  • Click anywhere in the table and either
    • Insert > Table OR
    • Ctrl+T
  • Under Table Design change the Table Name from Table1 to something meaningful (white box below the File Tab). Please do this, it will be worth it later.
    I prefer to prefix tables so that they sort together in a Names Drop down box and I generally use tbl.
    It also helps when you have forgotten the name later and you can type into a formula tbl and have a list of just the tables come up.

  • Sadly you will have to go into the 1st data row and click on each cell reference to select it and then the actual cell in the table to convert them to the new referencing (Unlike range names there is no Apply Names option.
    When you do that on the 1st data row it will automatically apply the change to all rows.
    eg in your case double click in the formula on J2 and then click on cell J2 and it will now replace J2 with [@Calls]
PS: Your formula is erroring out when there are blanks. This is because your isblank and iferror are using "" as the default value and this is causing the "+" operator to error out. Swap the "" for a 0 as the replacement option.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top