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]

You could do that with vba code. Try this in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try selecting different cells.

My code changes the formula if you select C1, D1 or E1 and removes the formula if you select anything else. Not sure if that's what you wanted but should give you something to experiment with.

<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">Const</SPAN> fBase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=SUM(#4:#10)"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Intersect(ActiveCell, Range("C1:E1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Range("C3").ClearContents<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Range("C3").Formula = _<br>            Replace(fBase, "#", _<br>                Replace(ActiveCell.Address(0, 0), 1, ""))<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]

Ok thanks for that. Now I want to complicate matters... I have the following array formula:

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

If the active cell is within G7:G26 then E$28:E28 would become G$28:G28 and E$5 would become G$5.
If the active cell is within I7:I26 then E$28:E28 would become I$28:I28 and E$5 would become I$5.

Please could you explain how this can be done in VBA?

Thanks

 

[Peter_SSs]

With the named ranges you have, and I don't have, I cannot easily set up a sheet to test. Also, I am about to go away for a few days.

However, I'm wondering why you have this complicated arrangement that depends on the active cell. Why not just have a set of results for each circumstance and look at the one that interests you?

 

[videozvideoz]

Due to lack of space on the sheet I want a third of the sheet to be variable dependant on where the active cell is. Basically certain data applies to certain columns and that list goes off the page if it is only displayed solely within that column. My challenge is that I want the whole of the bottom third of the sheet to be variable so that the data changes when the active cell changes. This enables all the data to fit onto one sheet without scrolling.

 

[videozvideoz]

Also each column has approximate 250 set of results and to get all 250 results on 1 page is impossible for all columns at once so I am trying to get 1 set of results displayed at a time depending on which is the active cell.

 

[videozvideoz]

Ok, to make it simpler try this...

If active cell is within D2:D27 then D30:D40 would contain reference to !Sheet2 A1:A11
If active cell is within F2:F27 then F30:F40 would contain reference to !Sheet2 B1:B11
If active cell is within H2:H27 then H30:H40 would contain reference to !Sheet2 C1:C11

 

[pgc01]

Hi

If I understand correctly you want to make the formulas to change the references to the active column when a cell is in some specified range is selected.

If that's the case, I would:

- define a name with the reference column
- write the formulas using that name
- use the SelectChange event procedure to change the reference column when a cell in the specified range is activated

From what I understood this would be an example similar to what you need.

Let's say that you have values in columns B:E. In each columns you have values in rows 2:8 and if the sum of those values is less that 100 you want to display the value in A1, else you want to display the value in the corresponding column in Row 10.

For ex., for column B:

=IF(SUM($B$2:$B$8)<100,$A$1,$B$10)

If you activate a cell in, for ex. E2:E8, you'd like the formula to change to:

=IF(SUM($E$2:$E$8)<100,$A$1,$E$10)


My suggestion

1 - Use the SelectChange event procedure to add/change the reference column when a cell in the range B2:E8 is activated:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B2:E8")) Is Nothing Then _
    Names.Add "RefCol", Target.EntireColumn
End Sub

2 - change the formula syntax to isolate the reference column. In I5:

=IF(SUM(RefCol $2:$8)<100,$A$1,RefCol $10:$10)

Now try selecting cells in B2:E8. You'll see the value in I5 updating.

This seems a simple solution for your problem, that you can adapt to your needs. Please try.

 

[videozvideoz]

Hi there. Thanks for this but I have 13 columns so the script will need to cater for 13 different variables. The example given just caters for 2 columns... i don't know how to add conditions for more.

Lets say on Sheet2 i have values in A1:L10
On Sheet1 if my active cell is A1:A5 then I would like to copy the contents of !Sheet2A1:A10 to A10:A19 of sheet 1
On Sheet1 if my active cell is B1:B5 then I would like to copy the contents of !Sheet2B1:B10 to B10:B19 of sheet 1
On Sheet1 if my active cell is C1:C5 then I would like to copy the contents of !Sheet2B1:C10 to C10:C19 of sheet 1

 

[videozvideoz]

Scrap this! I have a new solution that is a lot easier to manage. Please tell me how to do the following:

If the active cell is within column E, I would like the value in E4 to be copied to C30.
If the active cell is within column G, I would like the value in G4 to be copied to C30
If the active cell is within column I, I would like the value in I4 to be copied to C30

and so on... This would work perfectly for me and avoid copying/replacing complicated formulas