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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 01:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com