Function Output in Different Cell

Question

I'm trying to write a function in one cell, and then using that cell's address as a reference point, I'd like to change the value of a cell on Sheet2. Essentially, I'm attempting to make a function yield two different outputs, each output on a different sheet. Here's what I have so far:

Function test() As Variant
    Application.Caller.Worksheet.Cells(Application.Caller.Row,Application.Caller.Column).Row
    Application.Caller.Worksheet.Cells(Application.Caller.Row, Application.Caller.Column).Column
    Sheet2.Cells(a + 1, b + 1).Value = 8
End Function

The goal with this is to type "=TEST()" in cell A1 on Sheet1, and A1 would equal zero while cell B2 on Sheet2 would equal 8. I've heard rumors that Excel simply won't allow a UDF to affect change to any other cell other than the one in which it was written, but I really don't want to believe that rumor!

@pnuts, lots of good info. Thanks for the link. I'll see if it can help. — user2815713, Jul 24 '14 at 20:59

score 0 · Accepted Answer · answered Jul 24 '14 at 20:42

0

You can achieve your goal with a combination of a function (UDF) and an Event Macro.

The UDF would place a value in A1 and the event macro would detect this occurrence and change the cell on the other sheet.

answered Jul 24 '14 at 20:42

Gary's Student

95,722
10
59
99

I haven't used an event macro before, but after reading about it, it sounds like it might do the trick! I'll try it tomorrow and let you know. – user2815713 Jul 24 '14 at 21:00
You will need the **Calculate** event macro! – Gary's Student Jul 24 '14 at 21:01
It's a bummer that Excel won't allow all this to be done in a UDF, but at least there's a fantastic work-around. Thanks for helping me! – user2815713 Jul 25 '14 at 12:52

Function Output in Different Cell

1 Answers1

Linked