# How do I create a dynamic range using calculated cell references?

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

• Welcome to Superuser! Please have a look at the tour, you can also attach screenshots. – Ahmed Ashour Nov 22 '18 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 '18 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 '18 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 '18 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 '18 at 15:55