I would like to compare week number with column name
I have a column for each work week of the year (1 to 53). I would like to append the value of HC Plan on its respective column, based on Start Week and End Week. For example, if a project starts on week 1 and end on week 2, I need to append a Head Count of 5 to the columns of Work Week 1 and 2, to keep track of HC needed throughout the weeks. I tried an IF(AND()) formula, but didn't make much progress
Best Answer
-
Ok. Try putting this in the top left cell of the portion you want to fill in then drag-filling down and to the right:
=IF(AND($[Start Week]@row<= [1]$1, $[End Week]@row>= [1]$1), $[HC Plan]@row)
Answers
-
Where exactly would your output go? In a single row across all columns or in multiple rows across all columns?
-
Hi, Paul. It should add the value to the respective work week column. If a project starts on Work Week 2 and ends in Work Week 5, then it should add the value to columns WW2, WW3, WW4 and WW5
-
I understand the columns portion, but which row(s) would it go in? Every row that has something in the HC Plan column?
-
Yes, that is correct.
-
Ok. Try putting this in the top left cell of the portion you want to fill in then drag-filling down and to the right:
=IF(AND($[Start Week]@row<= [1]$1, $[End Week]@row>= [1]$1), $[HC Plan]@row)
-
Thank you very much, this has helped me a lot, not only to get the cell to display the value I want, also to understand the logic behind it.
Best regards
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!