Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
#
Creating a formula to autofill a range of cells based on input

- Home
- Microsoft Excel
- Excel
- Re: Creating a formula to autofill a range of cells based on input

Discussion Options

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 08 2019 08:44 AM

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 **B****1** 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 |

Labels:

12 Replies

best response confirmed by
tazewellj* (New Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 08 2019 09:04 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 08 2019 10:11 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 08 2019 10:16 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 08 2019 10:50 AM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 08 2019 10:53 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 08 2019 10:56 AM - edited Aug 08 2019 11:35 AM

Happy to help

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 08 2019 11:33 AM - edited Aug 08 2019 11:35 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 23 2019 10:51 PM

Hello @PReagan

Thanks for the earlier response .. this was also something i was looking for but i had an additional requirement.

In the response it was mentioned that we have to drag the formula down to the required number of cells in column A.

However can it be possible that say N number of Rows automatically fill based on the value of N entered in B2 e.g. if a person enters 512 in B2, A2:A513 all gets a number starting from 1 and incremented downwards .. ( without have to drag the formula all the way to A513 ) the number N could be any value (no max limits)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 23 2019 11:46 PM

I think your options are

- Take your existing formula and fill it down beyond your range of interest; a slight modification would return "" for values you do not require.
- Buy a copy of Office 365 and set it to insider or monthly update so that you can use the dynamic arrays and the SEQUENCE function
- Write a VBA macro to rewrite the values when N changes in cell B2
- Use PowerQuery to write the sequence when the data is refreshed

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 24 2019 01:51 AM

Thanks @Peter Bartholomew for a quick response, i was looking for a more generic solution which would not be dependent on the office version or VBA.

I dont think this is easily doable, please let me know otherwise

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 24 2019 06:43 AM

This would require VBA if you wish to fill N number of cells with the formula.

Alternatively, as @Peter Bartholomew suggested, you may "take your existing formula and fill it down beyond your range of interest;" *(for example cell A1048576) *" a slight modification would return "" for values you do not require." The downside to this, however, is that this would slow down Excel.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 23 2021 07:42 PM - edited Aug 23 2021 07:54 PM

Hi, i stumbled upon this and found solution...

For example

A | B | C |

1 | Fund | 100 (user input) |

2 | Splitter | 10 (user input) |

3 | ||

4 | ||

5 | formula * | formula ** |

* =sequence(c2,1)

** =if(b5="","",if(b6="",#,##))

# =$c$1-sum($c$3:c4)

## =$c$3/$c$4+randbetween(-5,5)/100*($c$1/$c$2)

Legends,

YOU WILL HAVE TO COPY THE ## FORMULA MANUALLY (for ex, copy from c5 to c200)

* will create sequence based on c2 input in rows, and 1 in columns

** will identify, if the cell left to it, b5, is blank, it will be blank, but if not blank (still in the sequence), it will give formula. Formula # if b6 is blank, meaning b5 is the LAST sequence. Formula ## if b6 is not blank, meaning b5 is still in the MID of sequence

# I have to make row 3 and 4 blank to manipulate formula to count for last cell, total fund, minus the sum of all the divider before this last cell

## formula to randomize the amount of fund splitted equally, but given -5 and 5 percent (per100) deviation