Complete Cells Based on Date Range

[miltonvonmises]

I can't seem to get to the bottom of this two step issue:

#1) In my sheet there are cells that reference a starting YEAR (B2) and starting QUARTER (B3) as well as cells that reference the ending year (E2) and ending quarter (E3). I can't figure out how to populate a column (B7 down) with all of the quarters between my start and end (inclusive).

#2) Furthermore, I want that list to repeat based on the number of persons entered into the dashboard at the top. So, for example, if someone filled in 3 names (Example: "Tom" in A5, "Mary" in B5, "Steve" in C5) then I want the list from #1 to repeat three times with each iteration having the person's name next to it. (So, "Tom" would appear in Column A next to each quarter between the start and end date in column B, below that "Mary" would appear in column A next to each quarter in column B, etc)

I have attached my workbook here but am concerned that it mightn't be possible to do this....

[FDibbins]

Hi, welcome to the forum

I can't figure out how to populate a column (B7 down) with all of the quarters between my start and end (inclusive).

I dont see any data to populate with? Did you forget to incude it?

[miltonvonmises]

Thanks FDibbins. If you're referring to the data to populate columns C, D and E I didn't include it because it wasn't pertinent to challenge I'm having here. I'm trying to figure out how to populate Columns A & B (from row 7 down). So, for example, as the document is currently filled out it would return the bollowing results B7=2013Q3, B8="2013Q4", B9="2014Q1" with "Tom" in the adjacent cell in column A. This would continue until the end date/quarter is reached at which point it would begin again but this time with "Mary" in the adjacent cell in Column A.

Does this answer your question FDibbins?

[FDibbins]

It is relevant as far as testing suggestions is concerned. I cannt see what anything is based on, where are the dates relating to what goes where?

[miltonvonmises]

Hmm.... my newness must be hindering my ability to explain my issue with the right semantics. Since a picture is worth a thousand word, I've copied screenshots of how I would like the output to appear.

Example 1 Start:2013Q2; End:2014Q4; Persons: 3 (Tom, Mary, Steve)
Motion Chart_FH Example 1.JPG

Example 2 Start:2012Q4; End:2013Q3; Persons: 2 (Tom, Mary)
Motion Chart_FH Example 2.JPG

[FDibbins]

OK this will give you what you need for 1 name.

B7=IF(B7="","","Tom")
copied down
B8=IF(OR(B7="",B7=$F$2&$F$3),"",LEFT(B7,4)+IF(RIGHT(B7,1)+0=4,1,0)&"Q"&IF(RIGHT(B7,1)+0=4,1,RIGHT(B7,1)+1))
copied down.

Im still working on how to repeat that for more than 1 name.

Where would it show the names that you want to use?