1

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.

2
  • 1
    Do you always want Sheet_1 to have groups of three? If so, say so. If not, how are we supposed to know how many times to repeat each value? … … … … … … … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete. Jun 19, 2021 at 18:08
  • People have put effort into answering your question. Please look at the answers and provide feedback — comments, votes and/or acceptance. Jun 25, 2021 at 23:17

2 Answers 2

0

Assuming that you always want Sheet_1 to have groups of three:  In Sheet_1!C3, enter

=INDEX(Sheet_2!B$3:B$99, INT((ROW()-ROW($C$3))/4)+1, 1)

replacing 99 with the last row where Sheet_2!B has data.  Copy that formula into cells C4 and C5.  Leave C6 blank.

Select cells C3:C6 and drag/fill down.

1
  • Thank you! This was a simple formula and it worked perfectly. Also, thank you for editing my question to be more succinct and clearer. Jun 27, 2021 at 11:46
0

This formula:

=IF(ROUNDUP((ROW()-2)/4,0) = (ROW()-2)/4,"",INDIRECT("Sheet2!A"&(ROUND(ROW()/4,0)+1)))

Will take dates in Sheet2 and place each one on three rows on Sheet1, followed by a blank cell before the next date repeated three times, and so on.

1
  • Thank you for your suggestion. I tried the answer from Scott and it worked perfectly. Being so, I did not try yours. Thank you though. Jun 27, 2021 at 11:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .