Formula for Week 1 and 2 rotation including cells that are blanks for Holidays

Hi,

I am doing a Data Merge from Excel to InDesign for a School Diary.
I have all the dates etc in already and the merge works fine.
I have a Formula for Week 1 & 2. For example if the previous week is a Week 1 then the following week is a Week 2 and vice versa. I have used an IF formula for this and that works out fine. =IF(B2=1,2,1)
However what i want to do is expand the formula so that if i put a blank cell in (Like remove when a School is on Holiday), the formula skips the blank cell until it reaches a cell with a value and continues counting from there.

For now it just repeats Week 1 if there is a blank and the first value before is a Week 1. If it is a Week 2 then it works for some reason.

Does anyone know or have any ideas?

Thank you in advance
John W
Screen Shot 2015-08-07 at 12.24.08.png

Anybody please?

Hi,

Can you post a sample excel file rather than image please? See the URL below for help on uploading a file.

http://www.excelforum.com/members/da...ch-a-file.html