Need help on a formula please

[rbeattie222]

I want to have the “company” show up on each day between the start and end date but I cannot seems to figure out.  I have been using Index and Aggregate but this is as far as I can get it to go. 

Can you please help me?

I have attached a screen show of what I have so far.

[#2]

@rbeattie222 

Since 365 wasn't tagged, I'll presume  you don't have access to FILTER.

See attached.

[#3]

@Patrick2788 

Hi Patrick sorry, I have Microsoft Office Professional 2010

Hope you can still help me?

Thanks Ross

[#4]

@rbeattie222

Yes, take a look at my attached workbook.

[#5]

@Patrick2788 

I looked at your sheet but I need the company to populate on all the days in the date range?

Thank you

[#6]

@rbeattie222 

Attached

[#7]

@Patrick2788 

That is almost what I was looking for, the only issue I see is that I need the range to have a max as well.  Example line one you have for Start Mar 2  and End Mar 5 but as you can see Company B still shows up on Mar 6.

I need these to fall in the date range.

Thank you again.

Ross

[#8 · Accepted solution]

@rbeattie222 

Add to A2:

=IFERROR(INDEX($K$1:$K$9,SMALL(IF(A$1>=$I$1:$I$9,IF(A$1<=$J$1:$J$9,ROW($K$1:$K$9))),ROW(A1)),1),"")

Ctrl+Shift+Enter to calculate

[#9]

@Patrick2788 

Patrick, I think you have saved me A TON of grief,  I cannot thank you enough.

Ross

[#10]

@rbeattie222 

Glad I could help. Have a good day!

[#11]

@Patrick2788 

Sorry to bother you gain Patrick but I can get the formula to work in the test files we have been using but I cannot get them to work in the spreadsheet that I want to use,  I have attached the file do you have time to review and see what I am doing wrong.

Thank you

Ross

[#12]

@rbeattie222 

There are 7 rows above your data that need to be taken into account.

Simple adjustment to the formula:

=IFERROR(INDEX('Gnatt Overview'!$E$8:$E$39,SMALL(IF(A$1>='Gnatt Overview'!$B$8:$B$39,IF(A$1<='Gnatt Overview'!$C$8:$C$39,ROW('Gnatt Overview'!$E$8:$E$39))),ROW(A1))-7,1),"")

[#13]

@Patrick2788 

Thank you very much,  I am not sure what I was doing but I typed the formula and I could not make it work, when I copied over your formula it works fine.   

But again thank you very much.

Ross