How to reference the last column in a formula

[jonvesguerra]

Hello all,

I am making a workflow that tracks attendance of people who come to events that I hold. The spreadsheet has a column for people's names, a column of the total number of events they attend, and finally a column for every event held with an "X" marked if that person attended that event.

Every time I hold a new event, I want to append a new column containing the attendance info for the event and update the "total events attended" column if needed. It will then save as the master file and when another event comes around, I can just change the input and run the workflow to add the new information. I've attached an example workflow and output that details what my formula looks like.

My question is, is there any way I can reference the last column of the spreadsheet within my formula without typing in the column name? The last column will always be the latest event, and I'd like to avoid manually changing that part of my formula every time I want to update my attendance list. I know that you can index columns like this in Python, but all of the articles I've seen thus far around the community haven't gotten me close to what I'm trying to do. 

Thanks in advance!

[geraldo]

@jonvesguerra 

export the workflow with excel file or put a sample data to analyze your formula. workflow alone is not understandable

[jonvesguerra]

Apologies! Here is an updated version with sample data. 

[dwstada · Accepted solution]

@jonvesguerra here is your modified workflow

the transpose tool will put the data of all event columns and new columns into one column, the formula changes X to 1, select changes the datatype of the column with all the 1s to numeric data, the crosstab then groups by the person and sums up all the 1s giving you the total events.

then join it with the original file to get your specific event columns back, but take the newly created "total events attended" column instead of the existing one

 

 

[geraldo · Accepted solution]

@jonvesguerra 

Here's another workflow example.

[jonvesguerra]

@geraldo @dwstada Thank you guys! Both of these worked. I appreciate it :)