software

I've got a sheet that looks like this:Code:
--------------------

Week Day Sales
3/4 10
3/5 20
3/6 30
Week 1 3/7 20
3/8 10
3/9 20
3/10 10
Total 120
3/11
3/12
3/13
Week 2 3/14
3/15
3/16
3/17
Total

--------------------The cells with the week's listed (i.e. Week 1, Week 2, etc...) are
merged for the week. So for example if the dates for 3/4 through 3/10
were entered into cells B3 through B9 then cells A3 through A9 are
merged together with Week 1 in it.

What I'm trying to do is use a LOOKUP function (or any other function
that would work) to display the week in a cell. In A1 I have entered a
date. Based on that date I want cell A2 to list what week it is. So for
example if I put 3/7/06 into cell A1 I'd want A2 to display quot;Week 1quot;.

As a sidenote is it possible to also change it so A2 displays it as
quot;week 1quot;. By that I mean changing the W in Week to lowercase. Thanks in
advance for any help.--
Weasel
------------------------------------------------------------------------
Weasel's Profile: www.excelforum.com/member.php...oamp;userid=27206
View this thread: www.excelforum.com/showthread...hreadid=526307Hi!

Try this:

=LOWER(LOOKUP(REPT(quot;Zquot;,255),A3:INDEX(A3:A20,MATCH( A1,B3:B20,0))))

Adjust the range references to suit.

Merged cells are a disease! Cure the disease!

Biff

quot;Weaselquot; gt; wrote in
message ...
gt;
gt; I've got a sheet that looks like this:
gt;
gt;
gt; Code:
gt; --------------------
gt;
gt; Week Day Sales
gt; 3/4 10
gt; 3/5 20
gt; 3/6 30
gt; Week 1 3/7 20
gt; 3/8 10
gt; 3/9 20
gt; 3/10 10
gt; Total 120
gt; 3/11
gt; 3/12
gt; 3/13
gt; Week 2 3/14
gt; 3/15
gt; 3/16
gt; 3/17
gt; Total
gt;
gt; --------------------
gt;
gt;
gt; The cells with the week's listed (i.e. Week 1, Week 2, etc...) are
gt; merged for the week. So for example if the dates for 3/4 through 3/10
gt; were entered into cells B3 through B9 then cells A3 through A9 are
gt; merged together with Week 1 in it.
gt;
gt; What I'm trying to do is use a LOOKUP function (or any other function
gt; that would work) to display the week in a cell. In A1 I have entered a
gt; date. Based on that date I want cell A2 to list what week it is. So for
gt; example if I put 3/7/06 into cell A1 I'd want A2 to display quot;Week 1quot;.
gt;
gt; As a sidenote is it possible to also change it so A2 displays it as
gt; quot;week 1quot;. By that I mean changing the W in Week to lowercase. Thanks in
gt; advance for any help.
gt;
gt;
gt; --
gt; Weasel
gt; ------------------------------------------------------------------------
gt; Weasel's Profile:
gt; www.excelforum.com/member.php...oamp;userid=27206
gt; View this thread: www.excelforum.com/showthread...hreadid=526307
gt;
Weasel wrote:
gt; I've got a sheet that looks like this:
gt;
gt;
gt; Code:
gt; --------------------
gt;
gt; Week Day Sales
gt; 3/4 10
gt; 3/5 20
gt; 3/6 30
gt; Week 1 3/7 20
gt; 3/8 10
gt; 3/9 20
gt; 3/10 10
gt; Total 120
gt; 3/11
gt; 3/12
gt; 3/13
gt; Week 2 3/14
gt; 3/15
gt; 3/16
gt; 3/17
gt; Total
gt;
gt; --------------------
gt;
gt;
gt; The cells with the week's listed (i.e. Week 1, Week 2, etc...) are
gt; merged for the week. So for example if the dates for 3/4 through 3/10
gt; were entered into cells B3 through B9 then cells A3 through A9 are
gt; merged together with Week 1 in it.
gt;
gt; What I'm trying to do is use a LOOKUP function (or any other function
gt; that would work) to display the week in a cell. In A1 I have entered a
gt; date. Based on that date I want cell A2 to list what week it is. So
gt; for example if I put 3/7/06 into cell A1 I'd want A2 to display quot;Week
gt; 1quot;.
gt;
gt; As a sidenote is it possible to also change it so A2 displays it as
gt; quot;week 1quot;. By that I mean changing the W in Week to lowercase. Thanks
gt; in advance for any help.

Use the =OFFSET function
gt;Use the =OFFSET function

Care to show us how you would use offset to solve this?

Biff

quot;Paul Lautmanquot; gt; wrote in message
...
gt; Weasel wrote:
gt;gt; I've got a sheet that looks like this:
gt;gt;
gt;gt;
gt;gt; Code:
gt;gt; --------------------
gt;gt;
gt;gt; Week Day Sales
gt;gt; 3/4 10
gt;gt; 3/5 20
gt;gt; 3/6 30
gt;gt; Week 1 3/7 20
gt;gt; 3/8 10
gt;gt; 3/9 20
gt;gt; 3/10 10
gt;gt; Total 120
gt;gt; 3/11
gt;gt; 3/12
gt;gt; 3/13
gt;gt; Week 2 3/14
gt;gt; 3/15
gt;gt; 3/16
gt;gt; 3/17
gt;gt; Total
gt;gt;
gt;gt; --------------------
gt;gt;
gt;gt;
gt;gt; The cells with the week's listed (i.e. Week 1, Week 2, etc...) are
gt;gt; merged for the week. So for example if the dates for 3/4 through 3/10
gt;gt; were entered into cells B3 through B9 then cells A3 through A9 are
gt;gt; merged together with Week 1 in it.
gt;gt;
gt;gt; What I'm trying to do is use a LOOKUP function (or any other function
gt;gt; that would work) to display the week in a cell. In A1 I have entered a
gt;gt; date. Based on that date I want cell A2 to list what week it is. So
gt;gt; for example if I put 3/7/06 into cell A1 I'd want A2 to display quot;Week
gt;gt; 1quot;.
gt;gt;
gt;gt; As a sidenote is it possible to also change it so A2 displays it as
gt;gt; quot;week 1quot;. By that I mean changing the W in Week to lowercase. Thanks
gt;gt; in advance for any help.
gt;
gt; Use the =OFFSET function
gt;