Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Defined Names > Arguments
Deze pagina in het Nederlands

Range Names In Excel

Passing an argument to a named formula

As shown in the file Arg2Name.zip (see the download page), there is a trick to pass arguments to a defined name formula.

First the named formula called Myref (see below) has to be created, which evaluates the string of the formula of the "active" cell (the cell that calls one of the other named formulas in this example) and extracts an address out of that. In order to use these formulas, a trick is involved. The function that contains the address should look like this:

=IF(ROW(ref),NameOfTheDefinedFormula)

The Myref function finds the "Row(" part in the string of the formula and takes all text between that and the first closing paren to be a valid cell reference. Myref in part is used as an argument to the other functions.

Define Myref as:

=MID(GET.CELL(6,INDIRECT(GetRC,FALSE)),FIND("ROW(",GET.CELL(6, INDIRECT(GetRC,FALSE)))+4,FIND(")",GET.CELL(6,INDIRECT(GetRC,FALSE)))-FIND("ROW(",GET.CELL(6,INDIRECT(GetRC,FALSE)))-4)

Note, that Myref has used the GetRC name demonstrated here.

Here are some examples that may be used in combination with the Myref named formula. Define these names:

IsFormula =GET.CELL(48,INDIRECT(myref)))+0*now()

CellColor =get.cell(63,indirect(MyRef))+0*now()

RowIsHidden =IF(GET.CELL(17,INDIRECT(Myref))=0,TRUE,FALSE)+0*now()

RowHeight =GET.CELL(17,INDIRECT(Myref))+0*NOW()

An example how to use these functions in worksheet cells:

=IF(ROW(D3),CellColor)

Shows the colournumber of the background of cell D3.

=IF(ROW(D3),RowHeight)

Shows the rowheight of cell D3 (in effect of row D).