I have data in consecutive cells in a column in Sheet_2. (In my case, they happen to be dates that are approximately a month apart, but this is probably unimportant.) Something like this:
A | B | |
---|---|---|
1 | Start of Period | |
2 | ||
3 | … | 30-Dec-2019 |
4 | … | 30-Jan-2020 |
5 | … | 2-Mar-2020 |
I want to fill in a column on Sheet_1 with references to these values on Sheet_2. To clarify: I want to auto-fill formulas in Sheet1 that reference Sheet2. Each value should be repeated three times, followed by a gap (blank cell). It should look like this:
A | B | C | D | |
---|---|---|---|---|
1 | {} |
|||
2 | Cheque A | Cheque N | Date | Inclusive |
3 | 11150 | 30-Dec-2019 | x | |
4 | 30-Dec-2019 | x | ||
5 | 30-Dec-2019 | x | ||
6 | ||||
7 | 11150 | 30-Jan-2020 | x | |
8 | 30-Jan-2020 | x | ||
9 | 30-Jan-2020 | x | ||
10 | ||||
11 | 11150 | 2-Mar-2020 | x | |
12 | 2-Mar-2020 | x | ||
13 | 2-Mar-2020 | x | ||
14 |
but it should actually be references, like this:
A | B | C | D | |
---|---|---|---|---|
1 | … | |||
2 | … | … | Date | … |
3 | … | =sheet2!B3 | ||
4 | =sheet2!B3 | |||
5 | =sheet2!B3 | |||
6 | blank | |||
7 | … | =sheet2!B4 | ||
8 | =sheet2!B4 | |||
9 | =sheet2!B4 | |||
10 | blank | |||
11 | … | =sheet2!B5 | ||
12 | =sheet2!B5 | |||
13 | =sheet2!B5 | |||
14 | blank |
At present, if I reference it manually and then drag and fill, it skips a bunch of rows.
I've tried a couple of variations of INDIRECT, but I must be using it incorrectly as I can't get it to work.
Any help is greatly appreciated.