SOLVED
Home

Creating a formula to autofill a range of cells based on input

%3CLINGO-SUB%20id%3D%22lingo-sub-795549%22%20slang%3D%22en-US%22%3ECreating%20a%20formula%20to%20autofill%20a%20range%20of%20cells%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795549%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20trying%20to%20create%20a%20formula%20that%20takes%20the%20input%20from%20a%20cell%20and%20fills%20a%20range%20of%20cells%20based%20on%20the%20numerical%20value%20entered.%20I'm%20not%20sure%20if%20there%20is%20a%20default%20function%20to%20a%3CBR%20%2F%3E%3CBR%20%2F%3EEX%3A%20If%20cell%20%3CSTRONG%3EB%3C%2FSTRONG%3E%3CSTRONG%3E1%3C%2FSTRONG%3Econtains%20a%20numerical%20value%20of%205%2C%20Cells%20A2%3AA%20would%20increment%20by%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CU%3E%26nbsp%3B%20%26nbsp%3BA%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB%26nbsp%3B%20%26nbsp%3BC%26nbsp%3B%20%26nbsp%3BD%26nbsp%3B%20%26nbsp%3BE%3C%2FU%3E%3C%2FP%3E%3CP%3E1%26nbsp%3B%20%26nbsp%3B%7C%3CEM%3EEnter%20a%20number%3A%3C%2FEM%3E%26nbsp%3B%205%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E2%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E3%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B2%3C%2FP%3E%3CP%3E4%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B3%3C%2FP%3E%3CP%3E5%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B4%3C%2FP%3E%3CP%3E6%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B5%3C%2FP%3E%3CP%3E7%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E8%26nbsp%3B%20%26nbsp%3B%7C%3C%2FP%3E%3CP%3E9%26nbsp%3B%20%26nbsp%3B%7C%3C%2FP%3E%3CP%3E10%20%7C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-795549%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795587%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20formula%20to%20autofill%20a%20range%20of%20cells%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795587%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389321%22%20target%3D%22_blank%22%3E%40tazewellj%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnter%20this%20formula%20into%20cell%20A2%20and%20use%20cell%20B1%20to%20input%20your%20number%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ROW(A2)-1%26gt%3B%24B%241%2C%22%22%2CROW(A2)-1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20copy%20the%20formula%20down%20as%20necessary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795735%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20formula%20to%20autofill%20a%20range%20of%20cells%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3Bthanks%20for%20your%20quick%20response.%20Unfortunately%20this%20always%20returns%20a%20value%20of%201%20in%20cell%26nbsp%3B%3CSTRONG%3EA2.%26nbsp%3B%3C%2FSTRONG%3EThis%20is%20a%20great%20start%2C%20but%20I%20am%20trying%20to%20create%20a%20formula%20that%20will%20fill%20a%20range%20of%20cells.%20For%20instance%2C%20if%20I%20the%20value%203%20in%20cell%26nbsp%3B%3CSTRONG%3EB1%3C%2FSTRONG%3E%2C%20I%20would%20like%20cells%20to%20filled%20as%20followed%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EA2%3C%2FSTRONG%3E1%3C%2FP%3E%3CP%3E%3CSTRONG%3EA3%20%3C%2FSTRONG%3E2%3CSTRONG%3E%3CBR%20%2F%3EA4%20%3C%2FSTRONG%3E3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20have%20a%20numbered%20rows%20based%20upon%20user%20input.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%2C%3CBR%20%2F%3Etazewellj%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795742%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20formula%20to%20autofill%20a%20range%20of%20cells%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3BI'm%20not%20sure%20the%20ROW%20function%20will%20achieve%20this.%20The%20ROW%20function%20returns%20the%20value%20of%20the%20row.%20The%20formula%20you%20provided%20will%20always%20return%20a%20value%20of%201%20no%20matter%20what.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795800%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20formula%20to%20autofill%20a%20range%20of%20cells%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795800%22%20slang%3D%22en-US%22%3EThe%20row%20function%20Row(A2)-1%20will%20always%20be%20equal%20to%201%20for%20cell%20A2%20unless%20B1%3D0.%20When%20you%20copy%20the%20formula%20down%20to%20cell%20A3%2C%20the%20function%20becomes%20Row(A3)-1%3D2.%20Copied%20down%20again%20to%20cell%20A4%20the%20formula%20becomes%20Row(A4)-1%3D3.%20The%20formula%20continues%20to%20add%20one%20to%20the%20previous%20total%20until%20it%20has%20reached%20a%20value%20greater%20than%20B1%20at%20which%20point%20%E2%80%9C%E2%80%9D%20will%20be%20returned.%3CBR%20%2F%3E%3CBR%20%2F%3EUnless%20I%20am%20misunderstanding%20your%20problem%2C%20this%20should%20return%20your%20desired%20result.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795802%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20formula%20to%20autofill%20a%20range%20of%20cells%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3Byour%20right!%20This%20worked%20like%20a%20charm.%20I%20needed%20to%20drag%20the%20formula%20down%20for%20it%20to%20apply%20the%20way%20I%20wanted.%20Thanks%20so%20much%20for%20you%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795810%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20formula%20to%20autofill%20a%20range%20of%20cells%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795810%22%20slang%3D%22en-US%22%3E%3CP%3EHappy%20to%20help%20%3A)%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795870%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20formula%20to%20autofill%20a%20range%20of%20cells%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389321%22%20target%3D%22_blank%22%3E%40tazewellj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20default%20function%20that%20does%20precisely%20what%20you%20have%20specified%20but%2C%20unfortunately%2C%20it%20is%20only%20just%20now%20becoming%20available%20and%20that%20to%20Office%20365%20users%20only.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20name%20the%20number%20cell%20'%3CSTRONG%3En%3C%2FSTRONG%3E'%20(as%20you%20describe%20the%20problem%20it%20is%20cell%20%24B%241%20but%20there%20is%20no%20reason%20it%20shouldn't%20move)%2C%20the%20sequence%20%7B%3CSTRONG%3E1%3B%20...%3B%20n%7D%3C%2FSTRONG%3Eis%20generated%20by%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SEQUENCE(%20n%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20Dynamic%20Array%20formula%20is%20entered%20into%20a%20single%20cell%20and%20spills%20down%20to%20display%20the%20values.%26nbsp%3B%20To%20generate%20a%20horizontal%20number%20sequence%20the%20dimensions%20of%20the%20output%20range%20change%20to%20read%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SEQUENCE(%201%2C%20n%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20further%20parameters%20that%20specify%20the%20start%20value%20and%20the%20step%2C%201%20being%20the%20default%20in%20each%20case.%3C%2FP%3E%3C%2FLINGO-BODY%3E
tazewellj
New Contributor

Hello,

I am trying to create a formula that takes the input from a cell and fills a range of cells based on the numerical value entered. I'm not sure if there is a default function to a

EX: If cell B1 contains a numerical value of 5, Cells A2:A would increment by 1.

         A                       B   C   D   E

1   | Enter a number:  5    

2   |   1                         

3   |   2

4   |   3

5   |   4

6   |   5

7   |   

8   |

9   |

10 |

7 Replies
Solution

Hello @tazewellj,

 

Enter this formula into cell A2 and use cell B1 to input your number:

 

=IF(ROW(A2)-1>$B$1,"",ROW(A2)-1)

 

Then copy the formula down as necessary.

 

Hope this helps!
PReagan

@PReagan thanks for your quick response. Unfortunately this always returns a value of 1 in cell A2. This is a great start, but I am trying to create a formula that will fill a range of cells. For instance, if I the value 3 in cell B1, I would like cells to filled as followed:

 

A2 1

A3 2
A4
3

 

My goal is to have a numbered rows based upon user input.


Thanks,
tazewellj

@PReagan I'm not sure the ROW function will achieve this. The ROW function returns the value of the row. The formula you provided will always return a value of 1 no matter what. 

The row function Row(A2)-1 will always be equal to 1 for cell A2 unless B1=0. When you copy the formula down to cell A3, the function becomes Row(A3)-1=2. Copied down again to cell A4 the formula becomes Row(A4)-1=3. The formula continues to add one to the previous total until it has reached a value greater than B1 at which point “” will be returned.

Unless I am misunderstanding your problem, this should return your desired result.

@PReagan your right! This worked like a charm. I needed to drag the formula down for it to apply the way I wanted. Thanks so much for you assistance.

Happy to help

@tazewellj 

There is a default function that does precisely what you have specified but, unfortunately, it is only just now becoming available and that to Office 365 users only.

 

If you name the number cell 'n' (as you describe the problem it is cell $B$1 but there is no reason it shouldn't move), the sequence {1; ...; n} is generated by

= SEQUENCE( n )

The Dynamic Array formula is entered into a single cell and spills down to display the values.  To generate a horizontal number sequence the dimensions of the output range change to read

= SEQUENCE( 1, n )

 

There are further parameters that specify the start value and the step, 1 being the default in each case.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies