Vlookup returns #NA

[MrFMC]

All,

I am having a problem with my Vlookup formula. I am using a date picker (MS Date and TimePicker 2.0 (SP2)) on sheet "Overall" to select a date. The range is set at 1/1/2014 - 12/31/2014. I am using the Linked Cell property and set it a AA1.

I have a table on tab "Data" that grabs data from the rest of the workbook. Column D is a formula (=IF(C353=0,"",B353/C353)). The formula returns the proper values.

My formula, =VLOOKUP(AA1,Select_a_Date,4,FALSE), only returns a value when I select 1/1/2014 from the date picker. Any other date that I select I get the nasty #NA error.

I have been working on this for about a week now, with no success. Can anybody help me out? It would be much appreciated.

Regards,

Charles

 

[Andrew Poulsom]

I've never used a Date Picker on a worksheet with the LinkedCell property set, but see if this helps you with your VLOOKUP problem:

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

 

[MrFMC]

Thanks for replying Andrew. I am sorry it took so long to reply. I have looked at the link and determined that it doesn't provide the answer. But, I did come up with a solution. I simple entered the DateValue formula in cell AA2 and used AA1 as the data. That converted the text date from AA1 into a serialized date. Then I simply referenced AA2 in my Vlookup and Whalla!

Now, one more problem. I have the date picker on a sheet that is normally locked. When I lock the worksheet, I get a protected worksheet error. How do I unlock the ActiveX control?

Charles

 

[Andrew Poulsom]

Did you unlock the linked cell?

 

[MrFMC]

Andrew,

I didn't even think about that. I will give it a go as soon as I get to my computer.

Regards,

Charles

 

[MrFMC]

Andrew,

Problem solved. Thank you very much. I can hide the columns where my reference cells reside to prevent them from being tampered with. Enjoy the rest of your week. I know I will.


Regards,

Charles