How do I create a dynamic range using calculated cell references?
up vote
1
down vote
favorite
I'm struggling since I'm not the best with working out the most efficient way to do these things.
Check the following:
"First row with data: 10".
Calculated by a formula that looks for the first data in a column.
"Last row with data: 1128".
Calculated by a formula that looks for the last row with data - the value calculated above.
"Data range AH10:AH1128
Calculated by a formula that creates the result from using the previous two result cells.
This then needs to feed into two further calculations:
C: = EXP(INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1,2))
B: = INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1)
At present I have to change the above formulas manually every time the data changes!
The data is based on a Pivot that updates monthly and the range with expand dynamically. The bottom two formulas calculate the power trend-line C: and B: values which then feed a ready-reckoner calculation in another linked sheet.
I have multiple sheets doing the same job but for filtered results as I cannot see any way to make the formulas work all from one dataset.
It is particularly the first problem though that I need to solve.
Can anyone suggest an idea to fix the issue?
enter image description here
Hi Raj - I find that the formulae that you suggested goes doolally when you have blank cells and cells with formulas in that return #N/A, etc results in the list of cells to be counted. How do you adjust to remedy? Thanks
microsoft-excel vba
asked Nov 22 at 9:42
Ross Barnes
62
|
show 4 more comments
up vote
1
down vote
favorite
I'm struggling since I'm not the best with working out the most efficient way to do these things.
Check the following:
"First row with data: 10".
Calculated by a formula that looks for the first data in a column.
"Last row with data: 1128".
Calculated by a formula that looks for the last row with data - the value calculated above.
"Data range AH10:AH1128
Calculated by a formula that creates the result from using the previous two result cells.
This then needs to feed into two further calculations:
C: = EXP(INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1,2))
B: = INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1)
At present I have to change the above formulas manually every time the data changes!
The data is based on a Pivot that updates monthly and the range with expand dynamically. The bottom two formulas calculate the power trend-line C: and B: values which then feed a ready-reckoner calculation in another linked sheet.
I have multiple sheets doing the same job but for filtered results as I cannot see any way to make the formulas work all from one dataset.
It is particularly the first problem though that I need to solve.
Can anyone suggest an idea to fix the issue?
enter image description here
Hi Raj - I find that the formulae that you suggested goes doolally when you have blank cells and cells with formulas in that return #N/A, etc results in the list of cells to be counted. How do you adjust to remedy? Thanks
microsoft-excel vba
asked Nov 22 at 9:42
Ross Barnes
62
Welcome to Superuser! Please have a look at the tour, you can also attach screenshots.
– Ahmed Ashour
Nov 22 at 9:47
@Ross Bames,, better you create a dynamic defined named Range, using such formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) and use the Named Range in Formula.
– Rajesh S
Nov 22 at 11:27
Cont... or you may use this one too, =$A$2:INDEX($1:$100,COUNTA($A:$A),COUNTA($1:$1))
– Rajesh S
Nov 22 at 11:29
Hi Raj - sorry for my ignorance but tried these and not worked. What I think you are suggesting trying to do is create dynamic defined named ranges from data held in a column of data. This needs to exclude some blank cells at the top and bottom and also exclude the data title in AH9. when I try and use your suggestions and adapt for my spreadsheet neither seem to work?
– Ross Barnes
Nov 22 at 15:45
Show us the formulas you use to get Row 10 and Row 1128, these can be combined to make a dynamic NAMED range for you
– PeterH
Nov 22 at 15:55
|
show 4 more comments
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I'm struggling since I'm not the best with working out the most efficient way to do these things.
Check the following:
"First row with data: 10".
Calculated by a formula that looks for the first data in a column.
"Last row with data: 1128".
Calculated by a formula that looks for the last row with data - the value calculated above.
"Data range AH10:AH1128
Calculated by a formula that creates the result from using the previous two result cells.
This then needs to feed into two further calculations:
C: = EXP(INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1,2))
B: = INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1)
At present I have to change the above formulas manually every time the data changes!
The data is based on a Pivot that updates monthly and the range with expand dynamically. The bottom two formulas calculate the power trend-line C: and B: values which then feed a ready-reckoner calculation in another linked sheet.
I have multiple sheets doing the same job but for filtered results as I cannot see any way to make the formulas work all from one dataset.
It is particularly the first problem though that I need to solve.
Can anyone suggest an idea to fix the issue?
enter image description here
Hi Raj - I find that the formulae that you suggested goes doolally when you have blank cells and cells with formulas in that return #N/A, etc results in the list of cells to be counted. How do you adjust to remedy? Thanks
microsoft-excel vba
asked Nov 22 at 9:42
Ross Barnes
62
I'm struggling since I'm not the best with working out the most efficient way to do these things.
Check the following:
"First row with data: 10".
Calculated by a formula that looks for the first data in a column.
"Last row with data: 1128".
Calculated by a formula that looks for the last row with data - the value calculated above.
"Data range AH10:AH1128
Calculated by a formula that creates the result from using the previous two result cells.
This then needs to feed into two further calculations:
C: = EXP(INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1,2))
B: = INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1)
At present I have to change the above formulas manually every time the data changes!
The data is based on a Pivot that updates monthly and the range with expand dynamically. The bottom two formulas calculate the power trend-line C: and B: values which then feed a ready-reckoner calculation in another linked sheet.
I have multiple sheets doing the same job but for filtered results as I cannot see any way to make the formulas work all from one dataset.
It is particularly the first problem though that I need to solve.
Can anyone suggest an idea to fix the issue?
enter image description here
Hi Raj - I find that the formulae that you suggested goes doolally when you have blank cells and cells with formulas in that return #N/A, etc results in the list of cells to be counted. How do you adjust to remedy? Thanks
microsoft-excel vba
microsoft-excel vba
asked Nov 22 at 9:42
Ross Barnes
62
asked Nov 22 at 9:42
Ross Barnes
62
edited Nov 23 at 9:58
asked Nov 22 at 9:42
Ross Barnes
62
asked Nov 22 at 9:42
Ross Barnes
62
asked Nov 22 at 9:42
Ross Barnes
62
62
Welcome to Superuser! Please have a look at the tour, you can also attach screenshots.
– Ahmed Ashour
Nov 22 at 9:47
@Ross Bames,, better you create a dynamic defined named Range, using such formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) and use the Named Range in Formula.
– Rajesh S
Nov 22 at 11:27
Cont... or you may use this one too, =$A$2:INDEX($1:$100,COUNTA($A:$A),COUNTA($1:$1))
– Rajesh S
Nov 22 at 11:29
Hi Raj - sorry for my ignorance but tried these and not worked. What I think you are suggesting trying to do is create dynamic defined named ranges from data held in a column of data. This needs to exclude some blank cells at the top and bottom and also exclude the data title in AH9. when I try and use your suggestions and adapt for my spreadsheet neither seem to work?
– Ross Barnes
Nov 22 at 15:45
Show us the formulas you use to get Row 10 and Row 1128, these can be combined to make a dynamic NAMED range for you
– PeterH
Nov 22 at 15:55
|
show 4 more comments
Welcome to Superuser! Please have a look at the tour, you can also attach screenshots.
– Ahmed Ashour
Nov 22 at 9:47
@Ross Bames,, better you create a dynamic defined named Range, using such formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) and use the Named Range in Formula.
– Rajesh S
Nov 22 at 11:27
Cont... or you may use this one too, =$A$2:INDEX($1:$100,COUNTA($A:$A),COUNTA($1:$1))
– Rajesh S
Nov 22 at 11:29
Hi Raj - sorry for my ignorance but tried these and not worked. What I think you are suggesting trying to do is create dynamic defined named ranges from data held in a column of data. This needs to exclude some blank cells at the top and bottom and also exclude the data title in AH9. when I try and use your suggestions and adapt for my spreadsheet neither seem to work?
– Ross Barnes
Nov 22 at 15:45
Show us the formulas you use to get Row 10 and Row 1128, these can be combined to make a dynamic NAMED range for you
– PeterH
Nov 22 at 15:55
Welcome to Superuser! Please have a look at the tour, you can also attach screenshots.
– Ahmed Ashour
Nov 22 at 9:47
Welcome to Superuser! Please have a look at the tour, you can also attach screenshots.
– Ahmed Ashour
Nov 22 at 9:47
@Ross Bames,, better you create a dynamic defined named Range, using such formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) and use the Named Range in Formula.
– Rajesh S
Nov 22 at 11:27
@Ross Bames,, better you create a dynamic defined named Range, using such formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) and use the Named Range in Formula.
– Rajesh S
Nov 22 at 11:27
Cont... or you may use this one too, =$A$2:INDEX($1:$100,COUNTA($A:$A),COUNTA($1:$1))
– Rajesh S
Nov 22 at 11:29
Cont... or you may use this one too, =$A$2:INDEX($1:$100,COUNTA($A:$A),COUNTA($1:$1))
– Rajesh S
Nov 22 at 11:29
Hi Raj - sorry for my ignorance but tried these and not worked. What I think you are suggesting trying to do is create dynamic defined named ranges from data held in a column of data. This needs to exclude some blank cells at the top and bottom and also exclude the data title in AH9. when I try and use your suggestions and adapt for my spreadsheet neither seem to work?
– Ross Barnes
Nov 22 at 15:45
Hi Raj - sorry for my ignorance but tried these and not worked. What I think you are suggesting trying to do is create dynamic defined named ranges from data held in a column of data. This needs to exclude some blank cells at the top and bottom and also exclude the data title in AH9. when I try and use your suggestions and adapt for my spreadsheet neither seem to work?
– Ross Barnes
Nov 22 at 15:45
Show us the formulas you use to get Row 10 and Row 1128, these can be combined to make a dynamic NAMED range for you
– PeterH
Nov 22 at 15:55
Show us the formulas you use to get Row 10 and Row 1128, these can be combined to make a dynamic NAMED range for you
– PeterH
Nov 22 at 15:55
|
show 4 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1377522%2fhow-do-i-create-a-dynamic-range-using-calculated-cell-references%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Welcome to Superuser! Please have a look at the tour, you can also attach screenshots.
– Ahmed Ashour
Nov 22 at 9:47
@Ross Bames,, better you create a dynamic defined named Range, using such formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) and use the Named Range in Formula.
– Rajesh S
Nov 22 at 11:27
Cont... or you may use this one too, =$A$2:INDEX($1:$100,COUNTA($A:$A),COUNTA($1:$1))
– Rajesh S
Nov 22 at 11:29
Hi Raj - sorry for my ignorance but tried these and not worked. What I think you are suggesting trying to do is create dynamic defined named ranges from data held in a column of data. This needs to exclude some blank cells at the top and bottom and also exclude the data title in AH9. when I try and use your suggestions and adapt for my spreadsheet neither seem to work?
– Ross Barnes
Nov 22 at 15:45
Show us the formulas you use to get Row 10 and Row 1128, these can be combined to make a dynamic NAMED range for you
– PeterH
Nov 22 at 15:55