Formulas with Named Ranges
Posted: Thu May 30, 2013 11:37 am
I have some formula's that have named ranges in them such as the following
=IF(D5="", 0,INDEX( TLocationTypeID,MATCH(D5,TLocationType,0), 1))
XLSRW5 doesn't seen to be able to evaluate those formulas, when I call .Calculate all I get is #N/A for these formulas
What is even stranger is that some similar formulas in other cells (using different named ranges) do give the right values
If I copy/Paste the formula back into the same cell inside Excel it does work.
The definition of those named ranges is on another spreadsheet
TLocationID
1
2
3
TLocation
Single Building
Multiple Building
Production
(I'm finding the offset down in TLocation of the text in Cell D5, which contains Single Building, then looking that up in TLocationID to get 1)
If I change the formulas to use the actual ranges then it does work, but I really do want to use the named ranges (maintainability and readability)
=IF(D5="", 0,INDEX( TLocationTypeID,MATCH(D5,TLocationType,0), 1))
XLSRW5 doesn't seen to be able to evaluate those formulas, when I call .Calculate all I get is #N/A for these formulas
What is even stranger is that some similar formulas in other cells (using different named ranges) do give the right values
If I copy/Paste the formula back into the same cell inside Excel it does work.
The definition of those named ranges is on another spreadsheet
TLocationID
1
2
3
TLocation
Single Building
Multiple Building
Production
(I'm finding the offset down in TLocation of the text in Cell D5, which contains Single Building, then looking that up in TLocationID to get 1)
If I change the formulas to use the actual ranges then it does work, but I really do want to use the named ranges (maintainability and readability)