How can I write a formula to pull data based on dynamic columns?
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!
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Wow, Genevieve - this worked perfectly! Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!