EXCEL Auto number rows until value in cell

0 votes

It's probably extremely simple, but I'm having trouble making it work.

I need a formula that will automatically number rows based on the value in B1. I want to automatically number rows starting at 1 down to the value specified in B1 starting from column B1.

This must be a formula rather than using VBA due to additional technical challenges that it cannot overcome.

I succeed up until the defined value (blank), after which the subsequent row returns #VALUE due to the calculation's failure.

I have tried every combination of IF, IFS, >"", =, >=, AND, and OR that comes to mind.

What should I define in order to prevent the #VALUE error? The defined value in B1 will fall within the ranges of 6 and 48.

screendump of result

Jan 20 in Others by Kithuzzz
• 27,740 points
28 views

1 answer to this question.

0 votes

You can utilize SEQUENCE if you have Excel 365:

Put =SEQUENCE(B1,1,1,1) in the cell below Month

Because the values will flow down, there must be sufficient vacant cells below the formula cell.

This will produce a numbered sequence, where the number of rows is determined by a value in B1 and the first column is numbered 1.

answered Jan 20 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

Spell number in excel 2013 using 2 rows

Hello, there are a few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
147 views
0 votes
1 answer

Excel - Make a graph that shows number of occurrences of each value in a column

There is probably a better way to ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 51,240 points
282 views
0 votes
1 answer

Calculate the number of days between a cell and today in excel?

Use the DATEDIF function when you want ...READ MORE

answered Nov 8, 2022 in Others by gaurav
• 22,940 points
67 views
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 51,240 points
109 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
205 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
365 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
165 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
144 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 51,240 points
285 views
0 votes
1 answer

Number format in excel: Showing % value without multiplying with 100

You just need to select Custom from ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 51,240 points
80 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP