Autopopulate Vlookuped Cells with specific entries if condition is met

[marcellebuff]

Hi Mr. Excel Fam,
I'm in need of some help to build a formula to streamline a daily report I run that takes hours.
I've attached a sheet for easy understanding.


In this example, Today's date hypothetically is 10/8/18.

How do I add a formula that populates Column J (after performing Vlookup for Column J) that will do the following:

Only replace vlookuped values in column E to do the following:

1) Starting with tomorrow's date (in Column E ((Ex. 10/9)) -- Highlighted Yellow,
In the Vlookuped value in "Column J", Populate with the most current Friday Date along with all dates that fall within two weeks of tomorrow's date (in Column E (*if the date in Column E is a Friday, populate Column J with that same Friday's date). In the example, on the sheet, 10/12 is populated.

2) After populating the Friday in Column J for all dates in column E that are between tomorrow's date (ex. 10/9/18 to 10/23/18 -- two weeks), Column E entries are replaced with the following Friday's date 10/19 (Friday's Date),

Then:
Count 65 rows after the first condition and replace the next 65 rows with next friday's date ( 10/26/18)
Then count another 65 rows, and replace with the next Friday Date (11/2/18).

Continue this until 65 row count and population until the date in Column E is the last day of Next Month (11/30/18), starting month was October.


If this can be done, you are a magician!!!
Really appreciate anyone taking the time to help!

 

[marcellebuff]

How come I can't upload a file?

 

[Fluff]

No one can upload files to the site. However there is the XL2BB add-in that you can use to include some sample data in a post. XL2BB