Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using relative named ranges directly in functions
'In any column to the right of column "B", I want to add the value in
cell "A1" 'to the value in the current row of column "B" 'I would like not to have to pass the 2 input cells as arguments to the function 'A1val is an absolute named range defined as: Sheet1!$A$1 'Bval is a relative named range defined as: Sheet1!$B1 (with the active cell being in row 1 at the time of name definition) ' 'Type the following in Sheet1: ' A B C D E '1 33 10 =A1val+Bval =Add1(A1val, Bval) =Add2() '2 20 =A1val+Bval =Add1(A1val, Bval) =Add2() '3 30 =A1val+Bval =Add1(A1val, Bval) =Add2() '4 40 =A1val+Bval =Add1(A1val, Bval) =Add2() ' 'From this I get the following results: ' A B C D E '1 100 10 110 110 110 '2 20 120 120 110 '3 30 130 130 110 '4 40 140 140 110 ' 'I want to get the same result in columns C, D and E, but obviously don't. 'It seems that the absolute named range and relative named range both work for Columns C and D, 'but not for Add2() in Column E. Function Add1(x, y) Add1 = x + y End Function Function Add2() Add2 = Range("A1val") + Range("Bval") End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
relative Named Ranges based on worksheet | Excel Worksheet Functions | |||
Use OFFSET and COUNT functions within Named Ranges | Excel Discussion (Misc queries) | |||
Why use Subtotals 1-11 instead of the built-in functions directly? | New Users to Excel | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
named ranges - changing ranges with month selected | Excel Programming |