How can I write a formula to pull data based on dynamic columns?
I'm trying to write a formula that will pull row information based on dynamic Columns.
My goal is to understand the difference in hours worked between last week and the week before. I would like to be able to flag if a Person has not updated hours in a week.
I have a 'raw data' sheet which has team member names in each row, and each column being a new week, with headers labeling '"W1", "W2", etc for Week 1, Week 2... (example below)
How can I write a formula for the 'Hours Logged Last Week' column that will look at the 'Current Week Helper', reference the column with the matching header, and then return the values from each row?
The idea is that the 'Current Week Helper' column has a 'Weekday(today())' formula so it will updated with each week and I won't have to do any additional manual steps aside from copy/pasting the raw hours data.
Help Article Resources
Check out the Formula Handbook template!