# Named range of cells as variable in equation.

#### Bonse

##### New Member
Good Morning,

I am currently working on a sheet to create CNC drilling programs.

I have several ranges of cells all with defined names.

I wish the defined names to change dependant on what options are selected elsewhere in the sheet.
Code:
``````=IFERROR(IF(\$F\$38+VLOOKUP(\$F\$36,CHECKDRILLING56E1,K7+1,FALSE)=\$F\$38,"",
\$F\$38+VLOOKUP(\$F\$36,CHECKDRILLING56E1,K7+1,FALSE)-VLOOKUP(\$F\$36,CHECKDRILLING56E1,2,FALSE)),
"")``````
Above is the code I'm currently using, I'm wishing the "checkdrilling56e1" part to change... now I have no problem with another cell producing this value, however I don't know how to get it into my formula for excel to still treat it as a "defined range".

Tom

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Andrew Poulsom

##### MrExcel MVP
If the name of the range is in A1 you can replace CHECKDRILLING56E1 with INDIRECT(A1).

#### Bonse

##### New Member
If the name of the range is in A1 you can replace CHECKDRILLING56E1 with INDIRECT(A1).

I literally just found that out a second ago and was about to post!

#### Bonse

##### New Member
I'm currently having an issue with an odd part of the formula.

F38 houses a number. However its not recognising it as a number. When I put the number In manually it works...

Any ideas?

#### Andrew Poulsom

##### MrExcel MVP
What does:

=ISNUMBER(F38)

return? Is there a formula in the cell? If so what is it?

#### Bonse

##### New Member
What does:

=ISNUMBER(F38)

return? Is there a formula in the cell? If so what is it?

It's telling me false.. however this formula currently works for 10 other similar options.

edit: it doesnt have to be a number for example the one that isn't working Is "3962" however I have a different 3962 working along with "5690 T1" etc

Last edited:

#### Bonse

##### New Member
added a "d" to everything, it worked. Removed the "d". It worked...

Must be a bug of some sort

Replies
11
Views
283
Replies
11
Views
2K
Replies
1
Views
413
Replies
3
Views
124
Replies
2
Views
194

1,191,133
Messages
5,984,856
Members
439,920
Latest member
mejatom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back