MS Excel question

[jimmyjazz]

I want to create a spreadsheet with "N" rows, and "N" is an input variable. In other words, if I put "3" in a particular cell, calculations proceed below on 3 rows. If I put "17" in that input cell, calculations proceed below on 17 rows.

Everything below those rows needs to be blank.

How in the world do I do this?

 

[JohnnyM]

why exactly would you have an input value determine the # of rows? that might help with a solution.

you could have column A be numbers 1-x. then you could run an IF formula in the calculation columns that if the value in A is greater than your input value, no calculations are to happen.

however, my guess is there is a better method than having a cell value be the determining factor in the # of rows.

 

[jimmyjazz]

Well, I have a total quantity of material I want to split into a variable ("N") number of parts. I want to create a distribution of "N" values according to a variety of rules (all min, all max, random or uniform, normal, etc.). "N" varies from case to case.

 

[jimmyjazz]

In reply to:

---

---

 

[Genco]

In reply to:

---

---

 

[PiGuy]

I posted this elsewhere, but to share with the HF community:

You want the INDIRECT function. It will create an reference to a cell based on a string which you can then use in a function.

For example, INDIRECT("C4") would create a reference to the cell C4. INDIRECT(CONCATENATE("C",B1)) would create a reference to the cell in column C and whatever row B1 is set as (this is your input variable).

So if you had a column of numbers in Column A and wanted to sum some of them in cell A1, as you suggest, based on a number in cell, say C1, then you would use:
=SUM(A2:INDIRECT(CONCATENATE("A",C1)))

 

[jimmyjazz]

PiGuy, I'm not even close to understanding your example. I'll have to play with it at work and see if it does what I need. Thanks for the advanced lesson, regardless.

Just in case I'm not being clear, let me restate the problem:

I want a column of integers, starting with "1" and ending with "N" (where "N" is an input from somewhere else on the sheet) to run from, say, cell A4 to A4+N-1; i.e., A4 to A9 for N=6:

Cell A4: 1
Cell A5: 2
Cell A6: 3
Cell A7: 4
Cell A8: 5
Cell A9: 6
<blank>

I don't want a long string of integers running below the "6" in cell A9. If my input "N" is 3, I want 3 rows and nothing else. If it's "27", I want 27 rows and nothing else.

Really, I'm just trying to automate a spreadsheet and make it bulletproof for future use. The calcs are easy, but "N" could vary as time goes on.

 

[PiGuy]

Ah, I misunderstood. Yeah, sounds like you want a macro.

 

[PiGuy]

To go back to what JohnnyM was saying, the only way to do it without a macro would be to use an IF statement in the row of numbers. There's no need to have two columns, just the one.

You would have to fill all cells in that column with the formula down to your max value, but I don't see the scenario where you wouldn't have to do that anyhow? You only need a single column, I don't see the need to hide anything.

So in Col A, with M as your starting row and N as your final row, you fill cells from Row M to Row N with:
=IF(ROW() <= $C$1, ROW()-$A$M+1, "")

Then in $C$1 you have your number of cells to fill. For all cells from the Row M to Row N you have your incrementing value. For all cells after, the cells are blank.

To do it without entering the formulas in each cell, you'll probably need a macro.

 

[jimmyjazz]

Thanks! I'll try it in a bit. I didn't know about the ROW function, but it looks like that will point me in the right direction.

One problem is that the row AFTER my string of "N" rows needs to be some summary calcs (not really blank), but I bet I can figure that out, either with buried "IF" statements or some other logic.

Thanks again.