Can I make formulas more flexible?

Hi,

I have a table with a certain number of rows. I don't know how many rows
there are in the table. I can make a data cell that holds the number of cells
in a particular column.

My question is this - Can I then use this figure to make a range to be able
to use inside other formulas in the worksheet?

For instance, the data cell works out there's 743 valid rows in the sheet.
Can I then use this to make a formula in another cell?

I want to get my formula (SUMPRODUCT) which doesn't accept shorthand for the
column ($C:$C) be flexible enough to cope with people adding extra values in
which need to be incorporated into the formula :-)

Thanks
George

suggest using a defined name range such as
insertt>name>define>name it something like myrng
in the refers to box
=offset($a$1,0,0,counta($A:$A),6)
then refer to the name. modify to suit

--
Don Guillett
SalesAid Software
[email protected]
"George" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a table with a certain number of rows. I don't know how many rows
> there are in the table. I can make a data cell that holds the number of
cells
> in a particular column.
>
> My question is this - Can I then use this figure to make a range to be
able
> to use inside other formulas in the worksheet?
>
> For instance, the data cell works out there's 743 valid rows in the sheet.
> Can I then use this to make a formula in another cell?
>
> I want to get my formula (SUMPRODUCT) which doesn't accept shorthand for
the
> column ($C:$C) be flexible enough to cope with people adding extra values
in
> which need to be incorporated into the formula :-)
>
> Thanks
> George

=SUM(INDIRECT("A1:A"&COUNTA(A:A)))

I think INDIRECT is the function you are looking for. INDIRECT returns cell
reference specified by a text string. COUNT counts the number of numbers in
a range, COUNTA counts the number of nonempty cells in a range. The & is how
you add text strings together. If this doesn't help, I would suggest giving
actual numbers and results you want.

"George" wrote:

> Hi,
>
> I have a table with a certain number of rows. I don't know how many rows
> there are in the table. I can make a data cell that holds the number of cells
> in a particular column.
>
> My question is this - Can I then use this figure to make a range to be able
> to use inside other formulas in the worksheet?
>
> For instance, the data cell works out there's 743 valid rows in the sheet.
> Can I then use this to make a formula in another cell?
>
> I want to get my formula (SUMPRODUCT) which doesn't accept shorthand for the
> column ($C:$C) be flexible enough to cope with people adding extra values in
> which need to be incorporated into the formula :-)
>
> Thanks
> George

If the new rows are inserted into the middle of the existing range, Excel
will adjust the references automatically. The only time adding rows becomes a
problem is if the new information is just appended to the bottom (or to the
right) of an existing range.

Can you set up your data with a last "dummy" record? Maybe the contents
would be "Insert new data just above this row" or something like that.

Johnny Naperville