Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Can I make formulas more flexible?
suggest using a defined name range such as
inserttnamedefinename 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 "George" wrote in message ... 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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disabling formulas and turning file into text only | Excel Discussion (Misc queries) | |||
Formulas not working | Excel Worksheet Functions | |||
how do I make a copy of a worksheet and retain formulas but not data | Setting up and Configuration of Excel | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions | |||
Tricky formulas needed | Excel Worksheet Functions |