MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

referencing another worksheet in different workbook?

  Asked By: Bill    Date: Feb 19    Category: MS Office    Views: 1118

I frequently work with dates, being a genealogist. I have written over,
and over, a VLOOKUP for months, month name, month values.

Can I put this is its own worksheet in a workbook all by itself? Just
name the range something like MonVals, MonNams, MonAbbs and VLOOKUP will
refer to that range on a different workbook? (Without my having go open
it previously?)

For example, if have:
A1: Year, 1832
B1: Month, April
C1: Day, 28

Then in D1, have something like:

And the formula will look on a different book and sheet to find
MonVals? That would a TREMENDOUS help to me. The above should come out to:



1 Answer Found

Answer #1    Answered By: Hariz Burki     Answered On: Feb 19

How about this:
In the VB Editor, I created a function in my Personal.xls file that looks like:
Function DateFormat(YR, MO, DY)
Dim StrDate
Select Case MO
MO = "01"
MO = "02"
Case "MAR", "MARCH"
MO = "03"
Case "APR", "APRIL"
MO = "04"
Case "MAY", "MAY"
MO = "05"
Case "JUN", "JUNE"
MO = "06"
Case "JUL", "JULY"
MO = "07"
Case "AUG", "AUGUST"
MO = "08"
MO = "09"
MO = "10"
MO = "11"
MO = "12"
End Select
StrDate = MO & "-" & DY & "-" & YR
If (IsDate(StrDate)) Then
DateFormat = Format(StrDate, "yyyy-mm-dd")
DateFormat = ""
End If
End Function
Then, in D1 I put:

Your Personal.xls file will open automatically whenever you open Excel, so the
is always available.

(I put  the worksheet  function in as a comment so that I can cut/paste it when I
need it
rather than using the wizard)
Would you like a different approach? I could come up with something.
hope this helps

Didn't find what you were looking for? Find more on referencing another worksheet in different workbook? Or get search suggestion and latest updates.