I would like to compare week number with column name

Paolo Fernandez

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

Paul Newcome

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)

Paul Newcome

Where exactly would your output go? In a single row across all columns or in multiple rows across all columns?

Paolo Fernandez

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

Paul Newcome

I understand the columns portion, but which row(s) would it go in? Every row that has something in the HC Plan column?

Paolo Fernandez

Yes, that is correct.

Paul Newcome

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)

Paolo Fernandez

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

Paul Newcome

Happy to help. 👍️