Named Range Formula without Sheet Reference

[David Kemp]

Hi everyone,

I have a named range that refers to a formula. By default the formula always creates a worksheet reference and even if I remove it, when saving the formula, it always creates a worksheet reference.

Is it possible to get the formula in the named range to reference the active sheet, or create a formula in the named range without a worksheet reference?

Eg.
Named Range: Data_Grab
Refers to: =IF(Sheet1!$C109='P&L'!$D$9,IF(Sheet1!$A$1='P&L'!$D$13,VLOOKUP(Sheet1!C$3,'P&L'!$B$22:$D$91,3,0),0),0)

Obviously I could create a seperate named range formula for each worksheet, but with many sheets and VBA that plots this formula in, having no sheet reference would be ideal.

 

[Jonmo1]

use just the ! before the range reference ( no sheet name, just the ! )

Like

!A1:A100

 

[David Kemp]

Wow, so simple! I should have known that too. Thanks Jonmo1, you're a legend.

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[tsooj]

@Jonmo1 I created an account on this site specifically to thank you for this excellent trick.

Who would have thought that such a simple action could relieve my headache! Was looking in all kinds of weird directions in order to solve this (CELL/OFFSET/INDIRECT...), and all I needed was an exclamation mark.

Cheers!