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, 2023 in Others by Kithuzzz
• 38,010 points
278 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, 2023 by narikkadan
• 63,420 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,670 points
365 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
• 63,420 points
7,552 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
• 23,260 points
414 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
• 63,420 points
530 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,670 points
1,237 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,670 points
1,423 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,670 points
697 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
• 23,260 points
2,330 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
• 63,420 points
1,546 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
• 63,420 points
866 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