Conditional Cell Update

[mgruber]

Hi,

Ideally I would like a function that looked something like;

Function copycell(source, dest)
dest = source
End Function

so I could enter in my spreadsheet;

=copycell(A1,B2)

to allow me to copy the contents of A1 to B2

Why?, because I have a spreadsheet that creates different totals, depending on what period (date range) is selected.

If A1 = (Period) 1 is selected the total in Cell A2 = X, if A1 = (period) 2 is selected A2=Y

So the logic is;

When A1 = 1 then copy A2 to A3
When A1 = 2 then copy A2 to B3
When A1 = 3 then copy A2 to C3

But I need the cells in row 3 to RETAIN their data unless the number in A1 is changed back to their relevant value because the values in row 3 are read and used as the starting values for the next period.

A simple IF formula, means only 1 cell in row 3 will hold its value ONLY when its condition is true.

I know now that UDF's other return a value to the same cell.

So maybe I need something that says, if A1 value changes paste A2 value?

Any ideas or tips would be muchly appreciated.

Regards
Michael

 

[Damon Ostrander]

Sorry to be the bearer of bad news Michael, but what you ask cannot be done. Excel does not allow a function to modify any cell other than the "calling" cell, and even this cell may only be modified to the extent of its return value (can't modify the cell color, for example).

Nevertheless, you can do what you describe with worksheet event code. How you do this depends on where the value in cell A1 comes from, i.e., whether it is calculated or linked from somewhere else, or manually entered. If manually entered the code would go into the worksheet's Change event. Assuming the latter, here's code that does this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = [a1].Address Then
Select Case Target.Value
Case 1: [a3] = [a2]
Case 2: [b3] = [a2]
Case 3: [c3] = [a2]
End Select
End If
Application.EnableEvents = True
End Sub

This code must be placed in the worksheet's event code module. To do this, right-click on the worksheet's tab, select View Code, and paste the code into the VBE code pane. The code will immediately be in effect so what whenever the value in A1 changes the "paste" occurs (this code doesn't actually do a paste since my guess is that you just wanted to copy the value in the cell, not all the cell properties).

 

[mgruber]

Damon,

A1 is manually entered on sheet1, A2 is a total calculated on sheet3, and this total needs to be "stored" in a table on sheet2.

I assume in the "[]" brackets, I can enter sheet! and cell references to copy to and from different sheets?

My requirements also include being able to select different rows (that was what I wanted to attempt originally).

Assume;

Sheet1!A1 = Period Number; and
Sheet1!B1 = Client Number; and
Sheet3!A2 = Period total
Sheet2!A1-J1 = Client 1, Period 1-11
totals
Sheet2!A2-J2 = Client 2, Period 1-11
totals
Sheet2!A3-J3 = Client 3, Period 1-11
totals

So when;
Sheet1!A1 = 1 AND Sheet1!B1 = 2; then
Sheet2!A2 = Sheet3!A2

or if;

Sheet1!A1 = 3 AND Sheet1!B1 = 1; then
Sheet2!C1 = Sheet3!A2

etc

I know the position of;

Sheet1!A1 and Sheet1!B1 and Sheet3!A2

and I know the relative positions of the detination cells ie Ax to Jx but the number of rows on Sheet2 depend on the number of clients currently managed.

Actually on sheet2 the position of the row for each client can be located via a vlookup because the client number is in column k.

So when Sheet1!B1 (Client Number) matches the number in Sheet2!(column K) it will identify relative row where the total needs to be posted.

Does that make it more confusing?

Regards
Michael

 

[Legacy 98055]

Hi
Maybe follow the advice given on your duplicate thread. Better help awaits.
http://www.mrexcel.com/board/viewtopic.php?topic=20073&forum=2&3

 

[Damon Ostrander]

Hi again Michael,

I think I understand what you want to do, except for one part. Because of your original question, I assume you want the code to execute (via the worksheet's Change event) whenever A1 or B1 changes (due to manual entry) on Sheet1. Is this right? If so, suppose you want to set A1=3 and B1=1, as in your example, and the current values of A1 and B1 are 1 and 2, respectively. So you start by setting A1 to 3. But the instant you do this the code runs with the combination A1=3 and B1=2 which is NOT the combination you wanted since B1 hasn't been updated yet. Is this a problem?

Other than this one issue what you want to do is relatively straightforward.

Damon