Named range of cells as variable in equation.

[Bonse]

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.

=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".


Thanks for reading.

Tom

 

[Andrew Poulsom]

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

 

[Bonse]

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!

Thanks for your help! :D

 

[Bonse]

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]

What does:

=ISNUMBER(F38)

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

 

[Bonse]

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

 

[Bonse]

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

Must be a bug of some sort

 

[Andrew Poulsom]

No bug. Have a read of this:

http://www.contextures.com/xlFunctions02.html#trouble

 

[Bonse]

No bug. Have a read of this:

http://www.contextures.com/xlFunctions02.html#trouble

very interesting ty.