Variable formulas in cells

[videozvideoz]

Hi There. Please could you tell me if it's possible to make a formula in a particular cell variable, depending on which cell the cursor is in?

For example, say cell C3 had the formula "=sum(C4:C10)" and my cursor was in C1. If I then selected cell D1 then the formula in C3 would change to "=sum(D4:D10)"

Thanks.

 

[Peter_SSs]

Something like this?

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Intersect(ActiveCell, Range("E:E,G:G,I:I")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Range("C30").ClearContents<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Range("C30").Value = Cells(4, ActiveCell.Column).Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[videozvideoz]

This is brilliant - works a treat. Just need one modification. I have attempted it but as I don't know VBA it didn't work.

Rather than: Range("C30").ClearContents

I would like it to replace ClearContents with the content of cell E5 as a default

 

[Peter_SSs]

Rather than: Range("C30").ClearContents

I would like it to replace ClearContents with the content of cell E5 as a default

Range("C30").Value = Range("E5").Value

 

[videozvideoz]

Thanks so much. Last question... How do I make my array formula not say "#NUM!"? I'd either like it to report "0" or just be blank

{=IF(ROWS(E$28:E28)>$E$2,"",INDEX(Available,SMALL(IF(Names=$C$2,IF((Available<>0)*($C$29>=Next_Date_Available),ROW(Names))),ROWS(E$28:E28))))}