Forum Discussion
Excel VLOOKUP function missing REF
I'm pulling an excel from online and I'm trying to figure out why the formulas work, but don't calculate. When I go to NAME MANAGER, I see that I am missing something:
"='INPUT DATA'!#REF!" -more or less.
The worksheet is the main (as it says) sheet to input data. I tried to input other reference tables, but I am not having any progress. How do I troubleshoot this?(The person who made it originally has retired since posting so I don't know how to get a hold of them.)
2 Replies
- it's because of a compatibility thing with lotus.
::facepalm::
now I just have to figure out how to rewrite the formulas to reference the underscored table names. this is the formula that doesn't work
=IF(B4>"A",IF(G4<>"P",LOOKUP(G4,INDIRECT(LOOKUP(G$2,eventlookup)&"SCALE"),INDIRECT(LOOKUP(G$2,eventlookup)&$B4&TEXT(LOOKUP($C4,agelookup),"##"))),"P")," ")
the formula that does work
=IF(B4>"A",IF(I4<>"NOGO",IF(I4<>"GO",LOOKUP(IF(I4>=1000,TIME(0,LEFT(I4,2),RIGHT(I4,2)+5),TIME(0,LEFT(I4,1), RIGHT(I4,2)+5)), INDIRECT(LOOKUP(I$2,eventlookup)&"SCALE"),INDIRECT(LOOKUP(I$2,eventlookup)&$B4&TEXT(LOOKUP($C4,agelookup),"##"))),"GO"),"NOGO")," ")
