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, 2013, and 2016.

**Create Custom Apps with VBA!** Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out *Mastering VBA for Office 2013* today!

Formulas are made up of operands that separate a series of terms acted upon by the operands. You may want to know, for ...

Discover MoreWant to add up a bunch of scores, without including the lowest one in the bunch? You can make a small change to your ...

Discover MoreWhen working with text phrases stored in cells, it might be helpful to be able to extract words from the phrase. In this ...

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

2016-03-31 17:39:10

Neil

2016-03-31 16:59:02

Neil

2016-03-31 16:42:52

Neil

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

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

{{{

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

{{{

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

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

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

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

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

Please clarify. This is unusual. Formulae do not get dragged in a decrementing manner.

2013-08-04 20:20:27

shirley

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.

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

Copyright © 2018 Sharon Parq Associates, Inc.

## Comments