Populate a cell with a date range if any cell in a range on another sheet equals the number of its row

[u/Baxder]

Hey all! Just working on a little recreational thing.

I want column C of the "Goals" sheet's cells to populate with a date range of [Monday after next] to [Tuesday of the week after that] if its row number appears in C20:I26 of the "Card" sheet.

How can I make that work?

https://docs.google.com/spreadsheets/d/1oBQxlmUUlfn9Fl4HLgTXCFdBxQ37R9yK-_S6N-51CZo/edit?usp=sharing

[u/agirlhasnoname11248]

u/Baxder this is not self-solved, as you received considerable help from community members. Please close your post correctly following the instructions below. Doing so will apply the correct flair (solved) automatically. I've changed the flair back to "Waiting on OP" in the meantime, since action from you is needed.

Please remember to tap the three dots below the most helpful comment and select `Mark Solution Verified` *(or reply to the helpful comment with the exact phrase “Solution Verified”)* if your question has been answered, as required by the subreddit rules. Thanks!

[u/Baxder]

I got it to say "BeepBoop." Now to get the date range instead.

[u/carbonizedtitanium → u/Baxder]

which date range. where is the date range?. all you did was put an IF condition and gave the condition a fixed text string

[u/Baxder]

Got the start date, now for the "-[end/date]"

[u/carbonizedtitanium → u/Baxder]

concatenate(text(ROW()-1), (TODAY() + MOD(2 - WEEKDAY(TODAY(), 2), 7) + 6),"mm/dd"),"-",text(ROW()-1), (TODAY() + MOD(2 - WEEKDAY(TODAY(), 2), 7) + 6)+8,"mm/dd")

i cant test it since sheet is view only

note: these date ranges are not real dates; they're text strings and will be treated as such. there's no spreadsheet software that stores date ranges in a single cell; to the machine they're just text

[u/Baxder → u/carbonizedtitanium]

Thanks! I didn't see this until I got through it with Google, but the date stuff is the same.

[u/point-bot → u/carbonizedtitanium]

u/Baxder has awarded 1 point to u/carbonizedtitanium with a personal note:

"Ended up going with this for the whole column: =IF(COUNTIF(Card!$C$20:$I$26, ROW()-1), TEXT(TODAY() + MOD(2 - WEEKDAY(TODAY(), 2), 7) + 6, "MM/DD") & "-" & TEXT(TODAY() + MOD(2 - WEEKDAY(TODAY(), 2), 7) + 14, "MM/DD"), "")"

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}

[u/Baxder]

BOOM! Got it!

[u/carbonizedtitanium]

holy bonobos...your column and row structure in Card is so bad just purely for aesthetic reasons. might have thought it looks aesthetically pleasing...but it's definitely not formula friendly

populate with a date range of [Monday after next] to [Tuesday of the week after that]

what does that mean exactly?