Creating a relative named range that can be used across more than one worksheet

[jase250]

Hi there,

I am using named ranges to determine conditions for formatting in Excel 2007. In this example the named ranged would resolve to 1 or 0:

Fail=(Dashboard!G6<Dashboard!$E6)*(Dashboard!$R6="H")+(Dashboard!G6>Dashboard!$E6)*(Dashboard!$R6="L")

I would like to be able to reuse this across multiple sheets, i.e. enter the formula like this in the Name Manager:

Fail=(G6<$E6)*($R6="H")+(G6>$E6)*($R6="L")

However, Excel refuses to let me enter a formula into the Name Manager without the reference to the worksheet being present. To be clear: I want to be able to type =Fail in a cell in a given worksheet (or have it referred to in a format condition) and have the relative reference refer to the cell in that worksheet.

If anyone knows of a workaround for this I would be most appreciative!

Cheers,
Jason

[FDibbins]

can you put the worksheet name into a cell and then reference that using indirect()?

[jase250]

Thanks, but unfortunately Excel is too 'smart' to be outwitted by that - it insists that even in the indirect you must include the sheet reference...

To be fair, though - the number of functions required to achieve this solution would have been huge for some of the formulas... the simple example I gave before blows out to:

=(INDIRECT(A1&"!"&ADDRESS(ROW(I7),COLUMN(I7)))<INDIRECT(A1&"!"&ADDRESS(ROW($E7),COLUMN($E7))))*(INDIRECT(A1&"!"&ADDRESS(ROW($R7),COLUMN($R7)))="H")+(INDIRECT(A1&"!"&ADDRESS(ROW(I7),COLUMN(I7)))>INDIRECT(A1&"!"&ADDRESS(ROW($E7),COLUMN($E7))))*(INDIRECT(A1&"!"&ADDRESS(ROW($R7),COLUMN($R7)))="L")

Cheers all the same.