On a vlookup, I am getting strange results on only 5% of the values.
I have a Worksheet A containing hundreds of resources showing:
A B C D
Resource Name Start Date etc etc
Joe 3/16/2006
Mary 4/5/2006

I have a Worksheet B containing a fiscal calendar containing 52 weeks:
A B C
Start Date of Week End Date of Week Week $
22-Oct-05 28-Oct-05 1
29-Oct-05 4-Nov-05 2
5-Nov-05 11-Nov-05 3
12-Nov-05 18-Nov-05 4
€¦. €¦. €¦.
17-Dec-05 23-Dec-05 9
24-Dec-05 30-Dec-05 10
31-Dec-04 6-Jan-05 11
7-Jan-05 13-Jan-05 12
€¦. €¦. €¦.
14-Oct-06 20-Oct-06 52

I am using the following vlookup that takes the Start Date in worksheet A
and compares it against the calendar range in worksheet B to find out which
week it falls into within the fiscal year.
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )
To test this out, I created a similar Worksheet as A that contains an entry
for every day of the fiscal year - 365 entries.
95% of the results are fine - but every entry from Dec 18 thru to Jan 6 all
point to week 12. Strange.

Can anyone help me figure this out.

WorksheetB!B$2:C$53,3??

--
Don Guillett
SalesAid Software

"Mary-Lou" wrote in message
...
On a vlookup, I am getting strange results on only 5% of the values.
I have a Worksheet A containing hundreds of resources showing:
A B C D
Resource Name Start Date etc etc
Joe 3/16/2006
Mary 4/5/2006

I have a Worksheet B containing a fiscal calendar containing 52 weeks:
A B C
Start Date of Week End Date of Week Week $
22-Oct-05 28-Oct-05 1
29-Oct-05 4-Nov-05 2
5-Nov-05 11-Nov-05 3
12-Nov-05 18-Nov-05 4
.. .. ..
17-Dec-05 23-Dec-05 9
24-Dec-05 30-Dec-05 10
31-Dec-04 6-Jan-05 11
7-Jan-05 13-Jan-05 12
.. .. ..
14-Oct-06 20-Oct-06 52

I am using the following vlookup that takes the Start Date in worksheet A
and compares it against the calendar range in worksheet B to find out
which
week it falls into within the fiscal year.
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )
To test this out, I created a similar Worksheet as A that contains an
entry
for every day of the fiscal year - 365 entries.
95% of the results are fine - but every entry from Dec 18 thru to Jan 6
all
point to week 12. Strange.

Can anyone help me figure this out.

a) I forgot to mention I enter the vlookup formula in column D in Workbook A.
b) I also realized for a heading in Worksheet B I entered "Week $" instead
of "Week #".
c) Also, I notice the columns in my example have lost their alignment which
makes the example harder to read

For the vlookup statement: =IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )

B2 = Column B in Worksheet A (Start date of resource)
WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and column C
(end date of week)
3 = the week # (this is the value I'm trying to return)
1 = true

Hope this helps.


"Don Guillett" wrote:

WorksheetB!B$2:C$53,3??

--
Don Guillett
SalesAid Software

"Mary-Lou" wrote in message
...
On a vlookup, I am getting strange results on only 5% of the values.
I have a Worksheet A containing hundreds of resources showing:
A B C D
Resource Name Start Date etc etc
Joe 3/16/2006
Mary 4/5/2006

I have a Worksheet B containing a fiscal calendar containing 52 weeks:
A B C
Start Date of Week End Date of Week Week $
22-Oct-05 28-Oct-05 1
29-Oct-05 4-Nov-05 2
5-Nov-05 11-Nov-05 3
12-Nov-05 18-Nov-05 4
.. .. ..
17-Dec-05 23-Dec-05 9
24-Dec-05 30-Dec-05 10
31-Dec-04 6-Jan-05 11
7-Jan-05 13-Jan-05 12
.. .. ..
14-Oct-06 20-Oct-06 52

I am using the following vlookup that takes the Start Date in worksheet A
and compares it against the calendar range in worksheet B to find out
which
week it falls into within the fiscal year.
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )
To test this out, I created a similar Worksheet as A that contains an
entry
for every day of the fiscal year - 365 entries.
95% of the results are fine - but every entry from Dec 18 thru to Jan 6
all
point to week 12. Strange.

Can anyone help me figure this out.

Not sure how to fix your VLOOKUP, but why not just do:

=ROUNDUP(($B2-StartDate)/7,0)

where "StartDate" is a named cell, or if you don't want to use named
ranges try:

=ROUNDUP(($B2-WorksheetB!$A$1)/7,0)

in this example the start date would be in cell A1 of WorksheetB

I'm not trying to round up a calendar week date - I'm using a different range
based on my company's fiscal year of November to October.

The vlookup function resides in Worksheet A - therefore B2 is actually the
Start Date of the resource in Worksheet A.

a) I need to take the Start Date of the Resource from Worksheet A and
compare it against the calendar date range in Worksheet B
b) Worksheet B contains the 'fiscal calendar' breakdown by week (start date
of week, end date of week, week #). This fiscal calendar goes from November
to October ... and not January to December
c) I then need to find which week number the resource started on (because I
need the week number for another calculation)


" wrote:

Not sure how to fix your VLOOKUP, but why not just do:

=ROUNDUP(($B2-StartDate)/7,0)

where "StartDate" is a named cell, or if you don't want to use named
ranges try:

=ROUNDUP(($B2-WorksheetB!$A$1)/7,0)

in this example the start date would be in cell A1 of WorksheetB

I think "StartDate" means "FiscalYearStartDate", in which case the
formula barbetta suggested would calculate the week of the fiscal year
based on the fiscal year start date. It finds the number of days
between the fiscal year start date *you provide* and the date in column
B of worksheetA, then divides by 7 and rounds up to the nearest integer
to give you the correct week based on your fiscal year. Maybe I'm not
understanding this correctly, but using that formula is much simpler
than the VLOOKUP you're trying to do.

It appears the fiscal year start date is currently at WorksheetB!$A$2,
so

=IF($B2="","",ROUNDUP(($B2-WorksheetB!$A$2)/7,0))

should do it. Let me know if I misunderstand.

I suggest you take a look at the help index for vlookup. In order to look
over 3 columns you will need to have three columns to look in.

--
Don Guillett
SalesAid Software

"Mary-Lou" wrote in message
...
a) I forgot to mention I enter the vlookup formula in column D in Workbook
A.
b) I also realized for a heading in Worksheet B I entered "Week $" instead
of "Week #".
c) Also, I notice the columns in my example have lost their alignment
which
makes the example harder to read

For the vlookup statement:
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )

B2 = Column B in Worksheet A (Start date of resource)
WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and column
C
(end date of week)
3 = the week # (this is the value I'm trying to return)
1 = true

Hope this helps.


"Don Guillett" wrote:

WorksheetB!B$2:C$53,3??

--
Don Guillett
SalesAid Software

"Mary-Lou" wrote in message
...
On a vlookup, I am getting strange results on only 5% of the values.
I have a Worksheet A containing hundreds of resources showing:
A B C D
Resource Name Start Date etc etc
Joe 3/16/2006
Mary 4/5/2006

I have a Worksheet B containing a fiscal calendar containing 52 weeks:
A B C
Start Date of Week End Date of Week Week $
22-Oct-05 28-Oct-05 1
29-Oct-05 4-Nov-05 2
5-Nov-05 11-Nov-05 3
12-Nov-05 18-Nov-05 4
.. .. ..
17-Dec-05 23-Dec-05 9
24-Dec-05 30-Dec-05 10
31-Dec-04 6-Jan-05 11
7-Jan-05 13-Jan-05 12
.. .. ..
14-Oct-06 20-Oct-06 52

I am using the following vlookup that takes the Start Date in worksheet
A
and compares it against the calendar range in worksheet B to find out
which
week it falls into within the fiscal year.
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )
To test this out, I created a similar Worksheet as A that contains an
entry
for every day of the fiscal year - 365 entries.
95% of the results are fine - but every entry from Dec 18 thru to Jan 6
all
point to week 12. Strange.

Can anyone help me figure this out.

There is 3 columns: Start Date of Week (Saturday), End Date of Week
(Friday), Week # (identifies if it's 1 thru 52)


"Don Guillett" wrote:

I suggest you take a look at the help index for vlookup. In order to look
over 3 columns you will need to have three columns to look in.

--
Don Guillett
SalesAid Software

"Mary-Lou" wrote in message
...
a) I forgot to mention I enter the vlookup formula in column D in Workbook
A.
b) I also realized for a heading in Worksheet B I entered "Week $" instead
of "Week #".
c) Also, I notice the columns in my example have lost their alignment
which
makes the example harder to read

For the vlookup statement:
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )

B2 = Column B in Worksheet A (Start date of resource)
WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and column
C
(end date of week)
3 = the week # (this is the value I'm trying to return)
1 = true

Hope this helps.


"Don Guillett" wrote:

WorksheetB!B$2:C$53,3??

--
Don Guillett
SalesAid Software

"Mary-Lou" wrote in message
...
On a vlookup, I am getting strange results on only 5% of the values.
I have a Worksheet A containing hundreds of resources showing:
A B C D
Resource Name Start Date etc etc
Joe 3/16/2006
Mary 4/5/2006

I have a Worksheet B containing a fiscal calendar containing 52 weeks:
A B C
Start Date of Week End Date of Week Week $
22-Oct-05 28-Oct-05 1
29-Oct-05 4-Nov-05 2
5-Nov-05 11-Nov-05 3
12-Nov-05 18-Nov-05 4
.. .. ..
17-Dec-05 23-Dec-05 9
24-Dec-05 30-Dec-05 10
31-Dec-04 6-Jan-05 11
7-Jan-05 13-Jan-05 12
.. .. ..
14-Oct-06 20-Oct-06 52

I am using the following vlookup that takes the Start Date in worksheet
A
and compares it against the calendar range in worksheet B to find out
which
week it falls into within the fiscal year.
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )
To test this out, I created a similar Worksheet as A that contains an
entry
for every day of the fiscal year - 365 entries.
95% of the results are fine - but every entry from Dec 18 thru to Jan 6
all
point to week 12. Strange.

Can anyone help me figure this out.

Your vlookup formula, AS SHOWN, will give a "REF!" everytime. Look at the
formula againread my last post think about it.

--
Don Guillett
SalesAid Software

"Mary-Lou" wrote in message
...
There is 3 columns: Start Date of Week (Saturday), End Date of Week
(Friday), Week # (identifies if it's 1 thru 52)


"Don Guillett" wrote:

I suggest you take a look at the help index for vlookup. In order to look
over 3 columns you will need to have three columns to look in.

--
Don Guillett
SalesAid Software

"Mary-Lou" wrote in message
...
a) I forgot to mention I enter the vlookup formula in column D in
Workbook
A.
b) I also realized for a heading in Worksheet B I entered "Week $"
instead
of "Week #".
c) Also, I notice the columns in my example have lost their alignment
which
makes the example harder to read

For the vlookup statement:
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )

B2 = Column B in Worksheet A (Start date of resource)
WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and
column
C
(end date of week)
3 = the week # (this is the value I'm trying to return)
1 = true

Hope this helps.


"Don Guillett" wrote:

WorksheetB!B$2:C$53,3??

--
Don Guillett
SalesAid Software

"Mary-Lou" wrote in message
...
On a vlookup, I am getting strange results on only 5% of the values.
I have a Worksheet A containing hundreds of resources showing:
A B C D
Resource Name Start Date etc etc
Joe 3/16/2006
Mary 4/5/2006

I have a Worksheet B containing a fiscal calendar containing 52
weeks:
A B C
Start Date of Week End Date of Week Week $
22-Oct-05 28-Oct-05 1
29-Oct-05 4-Nov-05 2
5-Nov-05 11-Nov-05 3
12-Nov-05 18-Nov-05 4
.. .. ..
17-Dec-05 23-Dec-05 9
24-Dec-05 30-Dec-05 10
31-Dec-04 6-Jan-05 11
7-Jan-05 13-Jan-05 12
.. .. ..
14-Oct-06 20-Oct-06 52

I am using the following vlookup that takes the Start Date in
worksheet
A
and compares it against the calendar range in worksheet B to find
out
which
week it falls into within the fiscal year.
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )
To test this out, I created a similar Worksheet as A that contains
an
entry
for every day of the fiscal year - 365 entries.
95% of the results are fine - but every entry from Dec 18 thru to
Jan 6
all
point to week 12. Strange.

Can anyone help me figure this out.