How can I write a formula to pull data based on dynamic columns?

ShannaR17

Hi all, 

 

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.

Thank you!

Genevieve P.

Hey @ShannaR17

What I would do here is add 2 helper columns to help return the Row Number down a column in the sheet.

• Add an Auto Number column and title it "Auto"
• Add a Text/Number column and use a MATCH function to always return the current row number:

=MATCH(Auto@row, Auto:Auto)

Then you can use your week helper value to determine the column to look down in an INDEX(MATCH formula, where we identify the row to bring back by using our helper Row number.

Try something like this:

=INDEX([Week 1]:[Week 3], [Row Number]@row, MATCH([Current Week Helper]$1, [Week 1]$1:[Week 3]$1))

The first range is all of your week columns. The second reference is the Row number. The third range, in the MATCH function, is just the top cells in the week columns.

Does that make sense? Let me know if it works for you!

Cheers,

Genevieve

Genevieve P.

Hey @ShannaR17

What I would do here is add 2 helper columns to help return the Row Number down a column in the sheet.

• Add an Auto Number column and title it "Auto"
• Add a Text/Number column and use a MATCH function to always return the current row number:

=MATCH(Auto@row, Auto:Auto)

Then you can use your week helper value to determine the column to look down in an INDEX(MATCH formula, where we identify the row to bring back by using our helper Row number.

Try something like this:

=INDEX([Week 1]:[Week 3], [Row Number]@row, MATCH([Current Week Helper]$1, [Week 1]$1:[Week 3]$1))

The first range is all of your week columns. The second reference is the Row number. The third range, in the MATCH function, is just the top cells in the week columns.

Does that make sense? Let me know if it works for you!

Cheers,

Genevieve

ShannaR17

Wow, Genevieve - this worked perfectly! Thank you so much!