Summary of problem:
I need each row of a query's custom column to populate based on a formula referencing cells within the query, as well as values from a different worksheet. What is the best way to do this?
Original Post:
I've been stuck on this problem for a few days, and connot figure it out. I'm making a task-management workbook that sorts data based on which week of our program you submit your action items. What this tries to do is to return which week of my program an entry belongs to. I.e. if 23 days have passed since the start, then an entry belongs to week 3. If 76 days have passed, then its week 7. This is important because I use the result when filtering for a report macro.
This is the query table I need to add the calculation to
The last column is the custom column, and those values should be calculated using the following cells: This is the source of the other info needed to calculate the week number of the program, with references shown
The approach I take works fine if there's only one row, but the named cell returns #VALUE! when it reloads if there is more than one new row in the custom column. I think this is because the cell I'm referencing is a formula with a variable that has multiple values? I'm relatively new to PQ so please be patient.
The Transformation I'm trying to perform/the formula in the named cell I want to apply to each row of the new column is:
=IFS(AND($B$2<WEEKNUM(New_Items_to_Save[Start Date]),WEEKNUM(New_Items_to_Save[Start Date])<54),$G$2-($D$2-(-53+WEEKNUM(New_Items_to_Save[Start Date]))),WEEKNUM(New_Items_to_Save[Start Date])<$B$2,$G$2-($D$2+WEEKNUM(New_Items_to_Save[Start Date])))
Which, using the above picture, solves to: =IFS(AND(33<[WeeknumStart Date], [Start Date]<54),47-(26-(-53+[WeeknumStart Date])), [WeeknumStart Date]<33, 47-(26+[WeeknumStart Date]))
AND: if the date falls within the range of the current year ie. week# is less than 54, then perform this calc.
IFS: otherwise, if week# is before the end of the program ie. 2023, then perform this calculation.
The above formula definitely works if I replace the "New_Items_to_Save[Start Date]" with a specific cell, so I don't think it's that. Maybe I'm not referring to it correctly? I'm not sure how parameters work or how to create new functions, but I can troubleshoot/google reasonably well.
Thank you very much in advance!