Forum Discussion
Find a specific list entry and start sequence?
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!!
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
3 Replies
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
- Yes! This worked perfectly. Thank you so very much!
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.
