Filling Cells with Decreasing Cell References

To make my formula below immune to the insertion or deletion of rows above the start of the vertical data but below the horizontal series, which would otherwise mess up the referencing, you can add a reference to the row you want. If my vertical data started in A16 but my horizontal series started in B3, my formula in B3 could be, "=INDIRECT("A"&ROW(14:14)+COLUMN())". Then, if a row is inserted between rows 3 and 16, Excel is smart enough to dynamically change the formula to "=INDIRECT("A"&ROW(15:15)+COLUMN())".

Oops, should say "=A2+1" in previous post. In regards to the original gist of the tip, I run into the same inability of Excel to figure out what I am trying to do in a formula series when I have a horizontally expanding series that references a vertically decending series (e.g. B2 is "=A2", C2 is "=A3", D2 is "=A4"), etc. I know I can transpose vertical data to horizontal data, but in the case where I have to add a column every month, it is a nuisance to hand-enter each formula. I found that Alan's Indirect formula above can be co-opted for my needs. If A3 is where my vertical series starts, I can put "=INDIRECT("A"&1+COLUMN())" in B3 and drag it to the right to get what I need.

@Prince & @Maria

Let's say your number series (e.g. 801, 802...) starts at A2 where you would put the first number. What you can then do is put a formula in A8 that is "=A1+1". Then select A8 through A13 and then drag the selection down. Every 6th cell will then have the incremented number with the five in-between cells blank.

@Prince & @Maria,
To be able to help you and to "make my life easy as possible" - please upload your Workbooks to a Hosting Site and return to post the link to it.
Please present 2-3 expected results(!) which were HAND TYPED BY YOU.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

Prince, have you found the formula? I am also having the same problem. :(

please anyone tell me the solution of this problem .

hi @ Michael (Micky) Avidan ! Your formula is working perfectly. But the problem is my all sheets are reference based. i have to access all the data from main sheet to other sheets . I have applied this formula not only number . i have applied on Name and Marks also.

{{{

NAME :- Rozer Class:- Viii Roll-no 801

Sr. Subject Obtained Marks Maximum Marks
1 English 89 90
2 Hindi 72 90
3 Punjabi 77 90
4 Math 68 90
5 Science 86 90
6 S.Stu. 54 90
7 D.S 67 90

}}}}
I have to create such type of format for each students.All information are accessing from sheet1. i want if i enter the data in sheet one, then sheet 2 automatically create such type of forms.I hope you will again suggest me the best formula.
Thank you !

hi @ Michael (Micky) Avidan ! Your formula is working perfectly. But the problem is my all sheets are reference based. i have to access all the data from main sheet to other sheets . I have applied this formula not only number . i have applied on Name and Marks also.
{{{
NAME :- Rozer Class:- Viii Roll-no 801
Sr. No Subject Obtained Marks Maximum Marks
1 English 89 20
2 Hindi 72 20
3 Punjabi 77 20
4 Math 68 20
5 Science 86 20
6 S.Stu. 54 20
7 D.S 67 20

}}}}
I have to create such type of format for each students.All information are accessing from sheet1. i want if i enter the data in sheet one, then sheet 2 automatically create such type of forms.I hope you will again suggest me the best formula.
Thank you !

@Prince,
In cell A1 type the following formula and drag down along the column:
---------------------------------------
=IF(MOD(ROW(),6)=1,INT((ROWS(A$1:A1)-1)/6)+801,"")
---------------------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

Hi ! will you please tell me how can i increase the value of cell address to skip(blank) the few lines.
such as:
801
skip line
skip line
skip line
skip line
skip line
802
skip line
skip line
skip line
skip line
skip line
803

such type of pattern how can we set in excel.
one more thing is
The series of 801
is calling through cell address of sheet1.
in sheet1 all number are in sequential
order. that we want in sheet 2 in given upper pattern.
Thanks !

There is also a procedural method to get the needed formulas/references. I think it is important to consider both because of INDIRECT references will give a different result than a direct "=Mxxx" when rows are inserted & deleted in the source and target ranges.

1. Select and copy the data in the source range
2. At A1, PasteSpecial…Values at A1 if just the values are need, Paste Special...Link if the reference needs to be maintained.
3. In B1 enter “=ROW()” the copy-down for all needed rows (or enter 1 in B1, 2 in B2 then fill-down for the series)
4. Copy B1:Bn and PasteSpecial…Values at B1 (skip if the ROW() function was not used in step 3)
5. Sort A1:Bn using B as the sort key
6. Delete the data from B:B

There are a number of things to do to expand the re-usability of this formula. One of the things I do with formulas using local cell row or column is to add an "anchor" to the formula. This is necessary when the target range starts somewhere other than A1. For example, if there had been a row for column headers on Adams worksheet, then the formula "=INDIRECT("B"&151-ROW())" would have started bringing in data from B149 instead of the desired B150. Of course, one can always adjust the '151' in the formula for this.

To add the anchor, change the formula to "=INDIRECT("M"&150-(ROW()-ROW($A$1)))". Now, instead of counting rows or columns to find the adjustment to the starting (bottom) cell, change the "$A$1$" to the new top cell of the target range.

Shirley, you mean you could copy formulae in a decrementing manner? For example you typed =B150 in A1, =B149 in A2 then select A1 and A2 and drag down till A150 so that A150 has =B1?
Please clarify. This is unusual. Formulae do not get dragged in a decrementing manner.

Hi, I used the same example as above and excel worked perfectly to pull down the formula. It is Excel 07