auto-increment formula

[richx]

TLDR: terminating a formula that increments based on a variable value with another formula without using any built-in functions/commands/methods


Hi all,

I'm trying to auto-increment and terminate a formula based on a variable value.

For example, cell A6 has a value of 26.
In column B, I would like a formula to start at cell B2, increment to cell B27, and then stop.
Next, if I were to change A6 to something like 45, the formula in column B would automatically extend to B46.
Rather than returning a 0-length string or a 0 in cell B28/B46 and beyond, I would like for cell B28 to be completely blank, no formula or anything.
I'm trying to do all this via a formula or formulas, so not using any built-in functions/commands.

I know you can just drag to fill the formula down a column, but I'm dealing with hundreds of rows and will be repeating this step on many many sheets and am trying to automate this process.
I would rather not have to use VBA here, but if no other solutions are possible, then that's fine.

Thank you.

[6StringJazzer]

Welcome to the Forum richx !

Rather than returning a 0-length string or a 0 in cell B28/B46 and beyond, I would like for cell B28 to be completely blank, no formula or anything.

Excel is a function-based language. That is, every cell must either: be empty, have a formula, or have a value. You cannot have a cell that is empty, and then later magically has a formula.

Your problem can be solved with formulas--up until you say that you want unused cells to be completely blank with no formula. If that's what you want, the only way to do it is with VBA.

If you would a like a VBA solution please describe what values or formulas you want to see in B2:B27 (as in your initial condition).

[richx]

Thank you for the response!

Just curious if there was a way to avoid using VBA initially.

The formula I'm using in column B is

Please Login or Register  to view this content.

with cell A3 being the name of the workbook I want to copy entries from. In cell A12 I have the number of rows with non-empty cells in the original workbook, which is 717 in this case. I would like for the formula in column B to extend all the way to row 718. I will be using similar formulas for columns C through Z.

[Pepe Le Mokko]

Like =IF(ROW()<=$A$12+1,INDIRECT($A$3&"B"&ROW()),"")
(starting in row 2)?

[richx]

yes exactly! in VBA

but I would like to change the "" zero-length strings at the end of the formula to blank cells.
Since I will be sorting the rows alphabetically later, and do not want "empty" cells at the top of my spreadsheet.

[richx]

my solution at the moment is to return a zero if the indirect returns false, and then record a macro to filter the zeros and clear those cells, then sort alphabetically, but is there a more efficient way?