I've created a sequence of dates for a dynamic calender. This works fine. But now I want to implement a condition, using 'IF' statement, where the step of the sequence is changed based on the value in the cells in column D (starting in cell D8). If the cell in column D contains "Y" then the step in the sequence for that specific cell must be 8 in stead of 7. The idea is that the sequence generates a list of all Mondays of a specific year (defined in B6), but when the Monday is a holiday, the return value must be a Thuesday.
The problem is that the reference for the column (D8) doesn't change and stays on the first cell reference. It should change to D9 for the next sequence value, D10 for the 3rd sequence value, etc.
- Dutch formula -
=REEKS(54;1;DATUM($B$6;1;1)-WEEKDAG(DATUM($B$6;1;1);2) 1;ALS(D8="Y";7;8))
- English version formula -
=SEQUENCE(54,1,Date($B$6,1,1)-WEEKDAY(DATE($B$6,1,1),2) 1,IF(D8="Y",7,8))
Edit 04/01/2023
This is the first cell in the sequence
This is currently the second cell in the sequence, where reference to D8 needs to be D9
CodePudding user response：
I'm not sure if I understand what you mean: I have copied your formula (the English one) in cell "E2" and this is what I get:
=SEQUENCE(54,1,DATE($B$6,1,1)-WEEKDAY(DATE($B$6,1,1),2) 1,IF(D8="Y",7,8))
In another cell ("G3"), this turns into (select cell "E2", press Ctrl C, press cell "G3", press Ctrl V):
=SEQUENCE(54,1,DATE($B$6,1,1)-WEEKDAY(DATE($B$6,1,1),2) 1,IF(F9="Y",7,8))
So, when I apply a formula to another cell (two columns further, one row further), the reference to "D8" turns into "F9" (two columns further, one row further).
The other reference ("$B$6) does not change. Obviously, because the dollarsigns prevent that value to be changed (this is exactly what absolute and relative cell references are about, as described here).
Unfortunately, I don't know what you mean when you say that your cell references don't change: the ones, who should, do, and the ones, who shouldn't, don't, which is correct behaviour.
Oh: when you enter your formula in an external tool (like Notepad or so), you paste your formula in a cell and you paste it again in another cell, Excel won't realise that the cell references need to be update, is this the problem you're having?
CodePudding user response：
Now I understand your question (in my first answer, I thought you were copying your formula on another place).
There seems to be a difference between earlier Excel versions, where a formula could only have one single cell as a result. Now there are formulae (like =SEQUENCE(), whose answer spreads over different cells. All those cells contain one element of the formula result, which means that the formula itself does not change over the multi-cell result.