I just need someone to get this one column into another workbook in excel.

I have two files in excel 2007. I need to use user_id to match up from the lapsed-user file to auto add the expire_date from the am_user file into column b of the lapsed user one. I've been fighting formulas for too long now trying to get this right.
I can't attach the files because it would be public.
 I use this:
=VLOOKUP(A:A,am_access.xlsx!$D:$H,5,FALSE)

And the result in the column is wrong, does not match anything in the other table. Example is row 2 of the lapsed file should show this: 8/15/2009 but instead shows 40040. Neither columns either place are set to anything different that just general. I definitely have the right column number set in the formula.
LVL 15
DzynitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Your VLOOKUP formula is working; you just need to format the cell with the formula to show a date.  40040 is the internal value for August 15, 2009.

However, I don't think A:A is correct; that should just be a single cell reference (ex., A2).

Regards,
Glenn
DzynitAuthor Commented:
Glenn, Is there a way to show you the files so they're not public?  One has md5 pw's, so I can't even show a screenshot.
Glenn RayExcel VBA DeveloperCommented:
You could email them to me directly, but I believe that is against EE policy (someone correct me if I'm wrong).

I usually recommend redacting sensitive content and leaving just the values in question.  Or create a replica.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

DzynitAuthor Commented:
I'm afraid if I change things and have to change back I'll make it worse ;) That's what I thought too on policy, but if you want to chance it - shoot me an email quick to h at dzynit.net and I'll send the files to you. As long as we post back the ultimate solution, I don't see what it's hurting.
Glenn RayExcel VBA DeveloperCommented:
That's why it's a good idea to keep backups! :-)

I'll see what I can do.
DzynitAuthor Commented:
I have backups, I'm just afraid I'll muff the files up if I remove what should not be public then have to put it back ;) I obviously really suck at excel!
Glenn RayExcel VBA DeveloperCommented:
I think the formula in the first cell of your lapsed user file should be:
=VLOOKUP(A2,am_access.xlsx!$D:$H,5,FALSE)

Make sure the am_access.xlsx file is open when you do this, otherwise Excel won't know where to get the data. Later, if you close am_access.xlsx, the filepath will appear in the formula.

If it returns a number, change the format from "General" to "Short Date" (or whatever date format you prefer).  Then copy down.

-Glenn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DzynitAuthor Commented:
You are awesome Glenn!! Thank you sooooo much!!
Glenn RayExcel VBA DeveloperCommented:
You're welcome.  You were really close to the solution on this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.