I have the following Spreadsheet
| A | B |
|---|---|
| Test A1 | =A1 |
| Test A2 | =A1 |
| Test A3 | =A2 |
| Test A4 | =A2 |
| Test A5 | =A3 |
| Test A6 | =A3 |
| Test A7 | |
| Test A8 | |
| Test A9 | |
| Test A10 |
When I autofill column B, I would expect to show =A4 (or the contents of A4) but instead it gives me =A7 (the content of A7)
How can I bring Excel to fill it like
=A4
=A4
=A5
=A5
etc... ?
CodePudding user response：
Try below formula-
=INDIRECT("A"&ROUNDUP(ROW()/2,0))
CodePudding user response：
=IF(ISEVEN(ROW()),INDEX([A],ROW()/2),INDEX([A],(ROW()-1)/2))
(or based on Harun24hr solution: =INDEX([A],ROUNDDOWN(ROW()/2,0))
You won't be able to autofill that for a formula (meaning that the row increases).
Use this formula within a table (insert > table) and it will expand itself to the whole column.
