I'm trying to dynamically fill a range of cells in a column based on a number in another cell.
So, fill x amount of cells with a value set in, say, E4. x is the value in cell E5.
If cell E4 = 25, fill the cells in the desired column below a certain point with "25" for x amount of rows. If E5 = 5, and the start point is A3, it would fill A3 through A8 with "25" or whatever desired string or formula.
For me, it's ="Layer " & ROW() - 2 and I want to fill every cell in that column to a range determined in E5. The amount of cells in that row would change dynamically with whatever is set in E5
Problem is, I can't find a way to set a variable and retrieve it's value in a single cell's forumula, and I can't use nested formulas either (SUM(Var1:E1) where Var1 is the string E2 or something like that)
In this case, starting at cell A3 = Layer 1, it would named each cell Layer 2, and then Layer 3, until the layer number matches the value in E5.
I also need to be able to count how many layers there are for a forumula in the next column that depends on the total number of layers (plus 1)
I don't really know a ton about Calc or Excel formulas, but they don't follow a convention that makes sense to me.
I would guess a nested sum function would work like: =SUM(SUM(A1:A5):A6) or =SUM((SUM(A1:A5)):A6) but it doesn't. And it doesn't seem like there is a way to set variables inside one cell's formula.
I also tried creating a string that would create the relevant cell reference and thus the right value, like =("E"&E1) which would create E5 if E1 was set to 5, but I can't use this value as a cell reference
To get the result shown in the images, enter =IF(ROW(D1)<=E$1;"Layer " & ROW(D1);"") in cell D3 and fill down to D10 by dragging the dark dot in the corner. Then change the value in E1 to see the difference.
The $ means do not change the following 1 in the reference when the formula is copied or filled elsewhere. Otherwise the formula references will be adjusted relative to the new location.
If I use =("E"&E1) to create "E5" based on the value in E1, I want to use the E5 as a cell reference and not a string.
That is what INDIRECT does.
=SUM(SUM(A1:A5):A6)
It should be =SUM(SUM(A1:A5);A6) instead. The first one is a range of cells, whereas the second gives an additional cell to include. You could also do for example =SUM(SUM(A1:A5);A6:A10).
I don't really understand why you would expect a : to work where the ; is. That would define a range that starts at the row of the result of the SUM and ends at a cell I suppose? Doesn't make sense to me. Maybe you intended something like =SUM(INDIRECT("A"&SUM(A1:A5));A6). Still I don't get why anyone would want to do that.
Spreadsheet formulas are different from programming languages. It may take some time to learn how to use them effectively. LibreOffice also has extensive support for macros if you need an actual programming language, although in most cases there is probably a better way to solve the problem without resorting to macros.
