Named Reference

[workharder]

I have created a named reference that stores functions to get around the 7 nested if statement limit for functions. However when I add the reference in the name manager, it automatically appends the current worksheet name in the variables.

original formula:

=IF($P$8>1.06,0.04,IF($P$8>=1.041,0.03,IF($P$8>=1.021,0.02,IF($P$8>=1.001,0.01,0))))

When i add the reference it changes to:

=IF(Template!$P$8>1.06,0.04,IF(Template!$P$8>=1.041,0.03,IF(Template!$P$8>=1.021,0.02,IF(Template!$P$8>=1.001,0.01,0))))

I need to use this reference in multiple worksheets. How can i make the function reference the current active worksheet?

I have tried to use

CELL("filename",$P$8)

but it still appends the current worksheet name I currently have selected when changing the formula.

 

[Jonmo1]

Welcome to the board...

To answer the question directly..
To make a named range refer to whatever sheet the formula is entered in, enter it with !

Example
=!A1
instead of
=Sheet1!A1

 

[Jonmo1]

Now to give the best advice I can..
There is no need to nest more than 7 if's..
Whenever you have a need to nest if's like that, there is almost certainly a better/more efficient way..

In this example, lookup is perfectly suited for the task.
Try
=LOOKUP($P$8,{0,1.001,1.021,1.041,1.061},{0,0.01,0.02,0.03,0.04})

 

[workharder]

Thanks for the warm welcome.

I cant believe I didnt just try ! !!!! I tried a few things. Sometimes the solution is easier than you anticipate. I didnt look into the lookup function mostly because the formula for each cell is reliant on the value of a different cell. Essentially 6 of those formulas for each cell with no macros or vb. I was trying to not get lost in my formulas.

Thanks again for the help!!

 

[Jonmo1]

Glad to help, thanks for the feedback...


Hey, don't worry about know trying it before.
If I remember, I figured that one out one day by accident...