Row Count in Table = Cell Value

I'd like to make a table in excel, 5 columns wide. I'd like the amount of rows to be based on a value in a seperate cell.

If possible, I would like to have if dynamic, so if the number in the cell changes, so do the amount of rows.

thanks a bunch

could you show me a simple example? i dont know anything about coding, but if i see an example ill be able to decipher it.

say a table is like... B2:F4, which would be a 3 row five column table. Column b would have values 1, 2, and 3 for B2, B3, B4. Say A1 is a value (this case it would be 3). If I change it to 5, the table is then B2:F6, and column B reads 1, 2, 3, 4, 5..

If i change A1 back to 3, the table reverts back and deletes the last 2 rows.

can you show me a simple example and give me some code to just plug in and change depending on the table location, etc.

Roy,

I followed your steps, and I change the number in H1 and I don't see anything happen. Maybe I am not following.

Palmetto

Thanks for all the help, but i dont think i was clear in my question.

i attached a worksheet of what i would like. it think it displays what i would like pretty clearly.

let me know what you think

^bump.

can anybody else help with this?

thanks for all the help!

Can anybody give me some help with trying to accomplish what I have attached.

I have a list in excel that has X amount of rows. I would like to have the amount of rows be driven by the value in a cell.

Thank you very much!!

See attached.

Why are you duplicating threads?

I answered this in your previous thread: http://www.excelforum.com/excel-prog...ell-value.html

Palmetto,

this is almost what id like.

At the buttom of the table, I forgot to mention that I would like to sum the data in the above rows, so I need to be able to insert and delete rows between the header and the footer of the table.

with the excel sheet you provided, you cant do that.

this was my fault for not mentioning it before.


Thanks!!

anybody have a macro for this??

I've seen what ineed on here but i dont know how to customize it.

table that is say 3 rows by 5 colums.

top row has header information (size, weight, etc)

middle row has data i need to input

last row has footer information (summed quantities)

i need to be able to make the middle rows greater or lesser depending on a cell value. i'd like for it to be dynamic as well

Please any help would be greatly appreciated.

If you are willing to place your totals at the top of the table then it simplifies things greatly and avoids the unnecessary use of VBA code.

You only need to drag the cells down as far as needed to copy formulas and formats when new rows are required.

Optionally, format as many rows as you think you may ever need and let the table expand/contract based on the input cell. With the totals at the top of the table you don't need to scroll or accomodate any row insertions/deletions.

I would do this but there is stuff under the table that i would like to have, and the table has the potential of being from 150 to 500 rows long, so i would have to have the stuff way way down at the bottom.

second, im not sure how to have the header sum all of the rows i have.

say i have 5 rows, i put =sum(B3:B7) that would work, but i change the 5 rows to 8 rows, it would still say =sum(B3:B7), not sum (B3:B10)

I would do this but there is stuff under the table that i would like to have, and the table has the potential of being from 150 to 500 rows long, so i would have to have the stuff way way down at the bottom.

One cardinal rule of efficient spreadsheet design is to have all related raw data on a single worksheet and structured in classic database layout.
If the "stuff" at the bottom is not raw data, can't you relocate this to other columns/sheet and reference it?

second, im not sure how to have the header sum all of the rows i have. say i have 5 rows, i put =sum(B3:B7) that would work, but i change the 5 rows to 8 rows, it would still say =sum(B3:B7), not sum (B3:B10)

Create dynamic named ranges for the data columns and reference the named range in the SUM formula. Dynamic named ranged automatically expand/contract with addition/deletion of new data.

See attached for formula example and dynamic named ranges.

Thanks for the feedback.

I think this method will work ok, but I was hoping not to have to edit things so much if my number changes. Say i change from 152 to 145, i need to go back and delete things, whereas a macro will delete it for me.