Nov 05 2021 07:41 AM
Hi there,
I am trying to figure out how to find the match for an input cell in a list, and begin a sequence counting up from this point in said list. See attached screenshot. For example, I have a list of all of the weeks in the year, as well as a cell with the "start week" for a project (A1). I also have the project duration (A2). My goal is to scan the list of weeks, find the cell that contains the "Start Week" value, and then begin counting up from there to create a list of project weeks based on the duration in A2.
Please help!!
Nov 05 2021 08:32 AM - edited Nov 05 2021 08:42 AM
First, is it possible for you to attach the actual spreadsheet rather than an image? Delete (or modify) any data that are proprietary or confidential first....or give us made-up project names (or whatever).
Second: when you say "find the cell that contains the "Start Week" value, and then begin counting up from there to create a list of project weeks based on the duration in A2," the word up needs some clarification. Visually, "up" in your screen shot would be going backwards in time; do you mean that? l don't think you do, but since words are important, I want to make sure.....so please confirm what you actually mean.
I've attached a spreadsheet that does some of what you want, perhaps enough to point you in the desired direction. This uses the function FILTER, which does require the newest version of Excel to work.
Nov 07 2021 03:03 AM
Solution
Something like this?
C1 formula: ="F"&FILTER(IF($F$1:$F$46=$B$1,ROW($F$1:$F$46)-MIN(ROW($F$1:$F$46))+1),ISNUMBER(IF($F$1:$F$46=$B$1,ROW($F$1:$F$46)-MIN(ROW($F$1:$F$46))+1)))
D1 formula: ="F"&FILTER(IF($F$1:$F$46=$B$1,ROW($F$1:$F$46)-MIN(ROW($F$1:$F$46))+1),ISNUMBER(IF($F$1:$F$46=$B$1,ROW($F$1:$F$46)-MIN(ROW($F$1:$F$46))+1)))&":"&"F46"
C5:C46 formula: =IF(D5=1,"Start","")
D5:D46 formula: =IFERROR(FILTER(ROW(INDIRECT($C$1):OFFSET(INDIRECT($C$1),$B$2-1,0))-MIN(ROW(INDIRECT($C$1):OFFSET(INDIRECT($C$1),$B$2-1,0)))+1,ISNUMBER(FIND(INDIRECT($C$1):OFFSET(INDIRECT($C$1),$B$2-1,0),F5))),"")
cheers
Nov 11 2021 07:50 AM
Nov 07 2021 03:03 AM
Solution
Something like this?
C1 formula: ="F"&FILTER(IF($F$1:$F$46=$B$1,ROW($F$1:$F$46)-MIN(ROW($F$1:$F$46))+1),ISNUMBER(IF($F$1:$F$46=$B$1,ROW($F$1:$F$46)-MIN(ROW($F$1:$F$46))+1)))
D1 formula: ="F"&FILTER(IF($F$1:$F$46=$B$1,ROW($F$1:$F$46)-MIN(ROW($F$1:$F$46))+1),ISNUMBER(IF($F$1:$F$46=$B$1,ROW($F$1:$F$46)-MIN(ROW($F$1:$F$46))+1)))&":"&"F46"
C5:C46 formula: =IF(D5=1,"Start","")
D5:D46 formula: =IFERROR(FILTER(ROW(INDIRECT($C$1):OFFSET(INDIRECT($C$1),$B$2-1,0))-MIN(ROW(INDIRECT($C$1):OFFSET(INDIRECT($C$1),$B$2-1,0)))+1,ISNUMBER(FIND(INDIRECT($C$1):OFFSET(INDIRECT($C$1),$B$2-1,0),F5))),"")
cheers