Filling Cells with Decreasing Cell References

by Allen Wyatt
(last updated March 31, 2016)

14

Adam has two formulas in cells A1 and A2. In A1 he has the formula =B150 and in cell A2 he has the formula =B149. He tried to copy this sequence downward, using the fill handle, but the sequence wouldn't work right. (For instance, after filling cell A3 contained =B152 when it should have contained =B148.) Adam wonders if there is a way to have such a fill work properly.

Adam is right; filling will not work in this instance. The reason is because the fill feature in Excel works with values, not with formulas. So, there are a couple of ways you can approach this problem.

The first is to place text values of some sort into cells A1 and A2. For instance, you could place the text "x=B150" (without the quotes) into cell A1 and "x=B149" (again, without the quotes) into cell A2. Excel rightfully parses these cells as text. You can then select A1:A2 and drag downward as far as you need. Excel follows the pattern in the text (remember, it isn't a formula) and decreases the numbers. (Don't drag past cell A150. When you hit A151 the text will be "x=B0" and then Excel starts counting upwards again in cell A152.)

Now, select all the cells that you just filled and use Find and Replace to search for the preface character (x) and replace it with nothing. You end up with the equal sign in the leading position, and Excel now parses the cells as formulas, just as you want.

Another approach is to use a more complex formula to accommodate the need of decreasing references. In this instance you can do it using the INDIRECT function, in this manner:

=INDIRECT("B"&151-ROW())

Paste this into cell A1 and then copy it down through cell A150. It works because you are subtracting the current row number from 151, adding a "B" to the front of it, and then using INDIRECT to reference the value in that calculated cell address. Obviously, if you want to reference a different cell (other than starting with B150), you'll need to adjust the formula so that the proper cell address is calculated.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8443) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Curving Text Around the Edge of a CD

Word works great with text, but not so great if you need to do some specialized things with the text, such as printing it on ...

Discover More

Increasing the Capacity of AutoCorrect

AutoCorrect can be a great tool to, well, "correct" information that you type. If you get a little creative, you can even use ...

Discover More

Adding Ampersands in Headers and Footers

Add an ampersand to the text in a header or footer and you may be surprised that the ampersand disappears on your printout. ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Locating a Single-Occurrence Value in a Column

Given a range of cells containing values, you may have a need to find the first value in the range that is unique. This tip ...

Discover More

Reversing Integer Values

Do you need to reverse a series of integer values, such as 5 becomes 1, 4 becomes 2, etc.? There are several ways you can ...

Discover More

Alphabetic Column Designation

Want to know the letters assigned by Excel to a particular column? Excel normally deals with column numbers, but you can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five less than 5?

2016-03-31 17:39:10

Neil

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())".


2016-03-31 16:59:02

Neil

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.


2016-03-31 16:42:52

Neil

@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.


2016-03-31 08:57:19

Michael (Micky) Avidan

@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


2015-04-13 01:39:58

Maria

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


2015-03-02 10:26:09

Prince

please anyone tell me the solution of this problem .


2015-02-27 07:32:05

prince

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 !


2015-02-27 07:26:57

Prince

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 !


2015-02-26 05:45:13

Michael (Micky) Avidan

@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


2015-02-25 14:15:33

Prince

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 !


2013-08-06 10:48:32

Don

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


2013-08-06 10:41:22

Don

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.


2013-08-05 05:24:20

Shreepad S M Gandhi

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.


2013-08-04 20:20:27

shirley

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


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.