Copy array formula to non-contiguous cells

Question

Execl 2013

Windows 7

Hi all,

I found this nifty array formula that looks up the start date in a pointer cell (C5 in this example), and then figures out which cells to put the date of the week in a grid of 6 rows by 7 columns (formatted to be "d").

It works great for that if I first copy the formula, then select a 6x7 grid, then paste it into the formula bar and array-enter with Ctr+Shift+Enter.  The 6x7 grid is for six possible weeks/seven days of the week in any given month.

However, I need to be able to get the same result, but on non-contiguous cells.  The actual grid is 18 rows by 14 columns.  I need the results of that formula to go into every third row (starting with the first row, then the fourth row, etc.) and every second column (1st, 3rd, etc.).  The first result would go in C13, the second in E13, etc and then the next row's first result would be C16.

I tried selecting only the "result" cells with Ctrl + click, which does select all the cells I want.  However, when I pasted the formula and then array-entered, the result only appeared in the last-selected cell.

How do I alter this formula to get the desired result with non-contiguous cells?  Thank you for your help.

Allison

Formula that works for contiguous cells: 

=IF(MONTH(DATE(YEAR(C5),MONTH(C5),1))<>MONTH(DATE(YEAR(C5),MONTH(C5),1)-(WEEKDAY(DATE(YEAR(C5),MONTH(C5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(C5),MONTH(C5),1)-(WEEKDAY(DATE(YEAR(C5),MONTH(C5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

Answer 1

Don't think you can have an array formula over several cells where the cells are non-contigious.

But you could put your formula on on a hidden sheet (in a contigious range) and then link each individual cell on your main sheet to each of those cells individually.

Cheers

Rich

Answer 2

Don't think you can have an array formula over several cells where the cells are non-contigious.

But you could put your formula on on a hidden sheet (in a contigious range) and then link each individual cell on your main sheet to each of those cells individually.

Cheers

Rich

Thank you.  Bummer.

I'll start another thread to ask about making a linear calendar reference.  Thanks.

Allison