dynamic range with formula

[Noz2k]

Is it possible in excel to only apply a formula to a range, based upon the amount of values in another range?


To keep it simple, as an example if I have a formula which says $B1 = $A1+1 from B1-B5. But then I add a value into A6 I want the formula to automatically be input into B6.

 

[repairman615]

Hello,

To automatically enter the formula would take some VBA.

Here is a formula that will only show if an entry is made:

<TABLE style="WIDTH: 200pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=266><COLGROUP><COL style="WIDTH: 200pt; mso-width-source: userset; mso-width-alt: 4864" width=266><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 200pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=266>=IF(A1="","",$A1+1)</TD></TR></TBODY></TABLE>

 

[repairman615]

Right click the sheet tab. Select veiw code then paste this:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br><SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("A1:A100"))<br><br><SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Value = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    Target.Offset(0, 1).Formula = "=A" & Target.Row & "+1"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Jonmo1]

that should be the default behavior...

Check in Tools - Options - Edit
put a check on "Extend data range formats and formulas"

 

[Noz2k]

Yeah, it might be that I'll just do it in vba.

My formula isn't actually as basic as the example, and I tend to place if or iferror statements around them to not show invalid values. However the problem is that because generally I'm dealing with data extracted from elsewhere the amount of data can vary massively dependant upon the timescale being looked at.

If someone looks at data over the past day, it might only be 1000 cells, where as over the year it might be 300,000. I don't really want to place the formula into 300,000 cells if the majority of the time 1000 would do, as it makes the workbook very large in terms of Kb's.

 

[Noz2k]

Jonmo, I am using Excel 2010 would this be the checkbox that says 'Enable iterative calculation'?

repairman615, thanks for providing the VBA, I will give that a try if I can't get it to work by default