Referencing same cell on different worksheet

After lots of help from mikerickson on cell adjacency, I'm back with another problem..!

I have a workbook with several sheets, some with fixed cell values and some with values that change.

I've adapted a code from mikerickson that allows me to create a UDF called lighttest which looks at the value of a cell and then takes one of several options.

Please Login or Register  to view this content.

The code works fine as it is but I'd like to change the option for the value 3, so that it sums the active cell on the main sheet 'mastergrid' with the same cell on the sheet 'daylight input' (rather than cell A1), and ideally, the same cell on other worksheets too.

I tried replacing [A1] with [masterCell] but I keep getting #VALUE! errors

Any help would be gratefully received

Hi,

I've attached a file that should explain it more...

The grid on the left represents what I've called mastergrid, each cell will change values between 3, 2 and 1

The sheet "daylight input" is a grid of fixed values.

Essentially, I want to iterate through variations to the mastergrid based on the results of the UDF "lighttest".

Hopefully, the cells containing 3's and 1's on the mastergrid will tend to move towards the equivalent higher values on the "daylight input" sheet.

The function is cell specific so each cell contains a reference to a cell on the mastergrid. The upper right grid illustrates this principle, using the function I posted earlier (eventually it will be placed on a separate sheet)

The lower right grid contains the formula

Please Login or Register  to view this content.

where 'A1' is different for each cell, which is fine except I would like this to be an IF function that only sums the result if the value of mastergrid is 3 (i.e. if it = 2 I'd like it to do nothing, and if it = 1 I'd like it to add 5)

I think I probably just need another sign or Chr(34) or something in the function I have, it's just hard to find a solution in the help files...