Resize tables using formulas

[Jai]

Hi,
Is it possible to resize a table using a formula?

I am importing data from a database and then creating a new worksheet using
some of this data. I am currently using a large table to allow for variations
in size but would much prefer a dynamic table that changes as the data from
the database does. Any ideas?

Thanks

 

[Bob Greenblatt]

Hi,
Is it possible to resize a table using a formula?

I am importing data from a database and then creating a new worksheet using
some of this data. I am currently using a large table to allow for variations
in size but would much prefer a dynamic table that changes as the data from
the database does. Any ideas?

Thanks

I'm not entirely sure what you mean or are trying to do. Have you defined a
name for the table that uses a dynamic reference? If so, the table should
resize accordingly. Try defining the name to be something like:
=offset($a$1,0,0,counta($a:$a),counta($1:$1))

 

[Jai]

Sorry for the fuzzy description. I have a table which I obtain from a
database. I then add a column to the end and insert a formula which gives the
row if the row forms part of the data I want.

In a new worksheet,which contains I table, I use the INDEX and SMALL
functions to retrieve the rows of interest. This new table is set to 30 000
rows. This is more than what I need, sometimes much more, but I cannot reduce
this number as it may not always be enough.

I want to know if it possible to use a formula to resize the table. I
originally used and INDEX and COUNT to give the range of the table but
unfortunately the formula was replaced by the results of the formula (ie.
A2:index(C:C,count('Data'!F:F)) became A2:C24592 in resize table)

Thanks,
Jai

 

[Bob Greenblatt]

Sorry for the fuzzy description. I have a table which I obtain from a
database. I then add a column to the end and insert a formula which gives the
row if the row forms part of the data I want.

In a new worksheet,which contains I table, I use the INDEX and SMALL
functions to retrieve the rows of interest. This new table is set to 30 000
rows. This is more than what I need, sometimes much more, but I cannot reduce
this number as it may not always be enough.

I want to know if it possible to use a formula to resize the table. I
originally used and INDEX and COUNT to give the range of the table but
unfortunately the formula was replaced by the results of the formula (ie.
A2:index(C:C,count('Data'!F:F)) became A2:C24592 in resize table)

Thanks,
Jai

The answer is YES! I showed you how to do it in my prior answer.

 

[Jai]

I tried that but it wouldn't let me edit the table in name manager. The
formula bar is greyed out. Is there some trick to doing this?

Thanks for your help Bob

 

[Bob Greenblatt]

I tried that but it wouldn't let me edit the table in name manager. The
formula bar is greyed out. Is there some trick to doing this?

Thanks for your help Bob

No, no trick. Is the sheet protected? If so, you can not edit the name.

 

[Jai]

The sheet is not protected. In name manager I can alter the 'refers to:' for
a range I name myself, but a table that already exists can only have its name
altered, with the bar allowing for alteration of cells to which the table
refers being greyed out. I know exactly what formula to use, problem is it
wont let me do it.

Thanks,
Jai

 

[Bob Greenblatt]

The sheet is not protected. In name manager I can alter the 'refers to:' for
a range I name myself, but a table that already exists can only have its name
altered, with the bar allowing for alteration of cells to which the table
refers being greyed out. I know exactly what formula to use, problem is it
wont let me do it.

Thanks,
Jai

Are you using the list manager to define the table, or have you defined the
table name your self?