Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
George
 
Posts: n/a
Default 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
  #3   Report Post  
Sloth
 
Posts: n/a
Default Can I make formulas more flexible?

=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

  #4   Report Post  
Johnny Naperville
 
Posts: n/a
Default Can I make formulas more flexible?

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Disabling formulas and turning file into text only Christine Excel Discussion (Misc queries) 3 April 1st 05 05:46 AM
Formulas not working John Lovin Excel Worksheet Functions 4 January 24th 05 03:27 AM
how do I make a copy of a worksheet and retain formulas but not data FireBrick Setting up and Configuration of Excel 2 December 29th 04 08:33 PM
calculating formulas for all workbooks in a folder Chad Excel Worksheet Functions 3 November 13th 04 06:22 PM
Tricky formulas needed Myrna Larson Excel Worksheet Functions 2 October 29th 04 03:29 AM


All times are GMT +1. The time now is 05:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"