• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Table range

P

Pierre

Member
Hello,

I was wondering if the range of a table (and especially the last row) could be dependent on a value in an other cell?

E.g. in example attached, could the last row of the table depend on the value in cell I2?

Thank you!
 

Attachments

Hi Pierre ,

Certainly ; why not ?

Excel will alert you to the fact that the formula is inconsistent with the other formulae in the column , assuming you have checked this error-checking option , but there is no reason why you cannot do it.

Narayan
 
You only have data for one cell in I2

If used correctly every time you enter data into the last cell in a table the table adds another row so you last row is now second to last and so on and so on.

What are you trying to achieve with this?


.
 
@Hui: I just want the same formula in the last row as in all other rows.

I'm not sure if I was very clear: I would like to know if it is possible to make the range of table (and by that I specially mean where the last row of the table is) by incorporating a formula in the "resize table")

In my example it would mean replace the "14" (=value of the last row) by a formula refering to the value which is in I2.

I half-succeded when entering =Indirect("$A$1:$G$"&$I$2) in the resize table window, however as soon as the table is updated, the formula disappear and the range comes back to =$A$1:$G$20 (see the print screen "before" and "after")
 

Attachments

  • Table_range_before.jpg
    Table_range_before.jpg
    175 KB · Views: 2
  • Table_range_after.jpg
    Table_range_after.jpg
    214.6 KB · Views: 1
Hi Pierre ,

What is the reason to use a formula to resize a table ? The whole raison d'etre for a table is that it automatically resizes as you add data.

Narayan
 
Hello Narayan,

The reason is that I have a table with formulas taking information from a matrix (monthly financial review) in an other sheet. My table transform the matrix information into a database style. I would like the table to add automatically rows as new data (=a new month) are entered in the matrix (the cell I2 in my example would detect the number of month with information and tell how many rows long the table should be). Hope my explanation is clear :p
 
You must log in or register to reply here.
Back
Top