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

Dzynit
Dzynit used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
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

Author

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 Developer
Top Expert 2014

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 Developer
Top Expert 2014

Commented:
That's why it's a good idea to keep backups! :-)

I'll see what I can do.

Author

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!
Excel VBA Developer
Top Expert 2014
Commented:
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

Author

Commented:
You are awesome Glenn!! Thank you sooooo much!!
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
You're welcome.  You were really close to the solution on this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial