How to reference column name in formula

Paul Newcome

Another option would be to build into your column formula to create the helper row.

Insert a checkbox column and manually check the box on the top row. Adjust your column formula to start with an IF statement that says if the box is checked then output whatever you want on your helper row, otherwise rung the original formula.

Original Formula:

=COUNTIFS({Range}, @cell = "On Track")

Updated formula:

=IF([Checkbox Column]@row = 1, "Specific Text", COUNTIFS({Range}, @cell = "On Track"))

There are a number of different ways to mark the helper row. Some manual. Some automated. The above is just a quick example.

SPB

Oooh - I got all excited for a moment.

Here's my issue, though:

Each column A-F is an item. Each row 1-10 is a date, with the most recent date at the bottom. Each item completed on each specific date gets a checkbox. Row 1 = A, B, C, E, F checked. Row 2 = B, E, F checked, and so on.

In each row, hidden columns G-L count how many boxes were checked in the last seven rows. In row 9, col G counts the number of boxes checked in col A for rows 3-9. Col H counts B3:9, etc. In row 10, col G counts A4:10, and so on.

Then, in each row, the max of G:L and the min of G:L is identified.

Now I just need to pull the column name/ header for the max and the min for the latest date (row 10).

For example, column E had the most boxes checked over the past 7 days, and column B had the least. The resulting reference in a dashboard needs to read "Max in last 7 days = E" and "Min in last 7 days = B"

How do I match the max of G:L with the column header of A:F?

Thank you for considering :)

Cole Lusty

@Andrée Starå/ @Paul Newcome - is this functionality created yet?

While the workaround of putting the match in the first row does work, you can't put column formulas if the first row is what you need to match, and that is a major limitation.

Can this update be prioritized if not already possible?

zoltan.dobai

Hi, referencing a column name is an essential function for all database functions - such as countifs, sumifs. Without this option, there is too much manual work! please include this function! thanks

amhba

We use the separate sheet workaround and column formulas, with the desired column names as row values in that separate sheet, and then in the main sheet, use Index/Match and hard code the reference to the main sheet's column name there and return the desired value from the separate sheet.

Our use case is to provide role-based Dynamic View links in customized email notifications, e.g., a Project Manager has a different Dynamic View link than a Product Owner.

Because Dynamic View links are indecipherable to humans, we need to provide people with direct links in their alert messages, but also do not want to maintain individual links in each Automation. For example, when creating an Automation Alert to a Project Manager, the sheet manager just needs to reference {{Project Manager Link}} instead of manually maintaining, e.g., https://dynamicview.smartsheet.com/views/b1234567-aa12-12aa-a123-12a34bc12d34 in each alert for each role.

We maintain a separate Links sheet with these columns:

Role | Dynamic View Link

The Role row values match the column names in the main sheet, and the Link values correspond to each Role.

We then have a role-based column for each Link (e.g., "Project Manager Link") and maintain a column formula in each of those, so each row has an appropriate per-role link which we pull from the Link sheet using Index/Match.

For example, assuming your Project Manger column maintains the Project Manager's contact information, the "Project Manager Link" column formula in your main sheet would be: =INDEX({Reference to Dynamic View Link Column in Links sheet}, MATCH("Project Manager", {Reference to Role column in Links sheet}, 0))

While this is not ideal, when managing Automations, this makes it easier to see that the Project Manager is getting the Project Manager link instead of the Product Owner link.