Cell-Dependent Formula That Can Skip Cells

I am trying to build a calendar wherein the days automatically update per the current day/month/year, but that the cells adjacent to those numbers are open for content.

Here is the formula I am using (thanks ExcelisFun!):

=IF(MONTH(COLUMNS($C3:D3)+(ROWS(D$3:D3)-1)*7-WDay+FOM)=Cmonth,COLUMNS($C3:D3)+(ROWS(D$3:D3)-1)*7-WDay+FOM,"")

As you can see, the cells populate with current day, month, etc. from my Names (see how this is done here: https://www.youtube.com/watch?v=TG0sfxahdsU&t=717s). When I open the spreadsheet, the current month and all its days are smiling back at me.

Sweet.

But I want more. What I am looking for is a calendar for planning blog posts that updates each month to the current month (like it does now), but that I can add post titles next to the days they'll be posting on. Is there someway to jump every other cell with this formula without losing the integrity of the formula?

Also, I don't want to have to have an array of any sort, so INDEX is out. I tried INT, but I can't get it to accept the formula.

Any thoughts? Thank you.

Formulas & Functions

Need Help

IngeborgHawighorst
Jan 10, 2018
Hello,

you can insert a column before the first column and between each calendar column. Then the first formula goes into cell B3 and goes like this:

=IF(LEN(B$2)=0,"",IF(MONTH((COLUMNS($A3:B3)/2)+(ROWS(A$3:B3)-1)*7-WDay+FOM)=CMonth,(COLUMNS($A3:B3)/2)+(ROWS(B$3:B3)-1)*7-WDay+FOM,""))

Copy down and across.

The empty cells have the formula, too, but you can overwrite them without destroying anything. Be aware, though, that when the month rolls over, any text that you have manually typed into any cell will still be visible and you will need to clean up the data entries for the new month.

IngeborgHawighorst
MVP
Jan 10, 2018
Hello,

you can insert a column before the first column and between each calendar column. Then the first formula goes into cell B3 and goes like this:

=IF(LEN(B$2)=0,"",IF(MONTH((COLUMNS($A3:B3)/2)+(ROWS(A$3:B3)-1)*7-WDay+FOM)=CMonth,(COLUMNS($A3:B3)/2)+(ROWS(B$3:B3)-1)*7-WDay+FOM,""))

Copy down and across.

The empty cells have the formula, too, but you can overwrite them without destroying anything. Be aware, though, that when the month rolls over, any text that you have manually typed into any cell will still be visible and you will need to clean up the data entries for the new month.
- Brice Bunner
  Copper Contributor
  Jan 10, 2018
  Wow, thanks for the quick reply!
  I’ll give it a shot when I’m back at the spreadsheet. Thank you for the tip!
- Brice Bunner
  Copper Contributor
  Jan 10, 2018
  Hello.
  
  Well, I tried the formula just as you said, and nothing appears in any of the boxes. For some reason, the whole array comes out empty.
  
  I tried deleting every other cell and even adding =TODAY in A3 and nothing changes.
  
  Maybe I'm missing something? Your screen shot is exactly what I'm trying to accomplish, so I know I'm close!
  
  Are there any other formulas in your spreadsheet, maybe?
  
  Thanks.
  Calendar Sheet.xlsx12 KB
  - Brice Bunner
    Copper Contributor
    Jan 10, 2018
    maybe I spoke too soon. I retried on a different page and it worked!
    Thanks again for the tip!

Forum Discussion

Cell-Dependent Formula That Can Skip Cells

Share

Resources