On Dec 15, 4:08*pm, ritpg wrote:
On Dec 15, wrote:





On Dec 12, wrote:

On Dec 11, wrote:

I've got the following Excel sheet in which the work figures represent
the weekly and cumulative hours in a person's hours budget:

* * * * * * * * * * * * * * * *05-Jan-09 * 12-Jan-09 * 19-Jan-09
etc.
Joe Smith
* *Work * * * * * * * * * * * *40 * * * * * *40 * * * * * * *35
* *Cumulative Work * * * 40 * * * * * *80 * * * * * * *115
Mary Jones
* *Work * * * * * * * * * * * *25 * * * * * *40 * * * * * * *35
* *Cumulative Work * * * 25 * * * * * *65 * * * * * * *100
John Jefferson
* *Work * * * * * * * * * * * *35 * * * * * *40 * * * * * * *35
* *Cumulative Work * * * 35 * * * * * *75 * * * * * * * 110
etc.

And I have a 2nd sheet as follows:

Current Date: * 12-Jan-09

* * * * * * * * * * * * * *Cumulative Hours
* * * * * * * * * * * * * *Budgeted to Date
Mary Jones * * * * * * * * * *65
Joe Smith * * * * * * * * * * *80
John Jefferson * * * * * * * *75
etc.

Note that the names on the 2nd sheet are not in the same order as the
1st sheet. *I want to be able to change the Current Date on the 2nd
sheet and have the Cumulative Hours Budgeted to Date column populated
automatically.

I know there are folks out there much smarter about Excel than I. *Any
and all inputs will be greatly appreciated.

Thanks,
Terry

I suspect the answer has something to do with a combination of the
VLOOKUP and HLOOKUP functions. *Or perhaps the INDEX function would do
it. *Any thoughts?

Thanks.- Hide quoted text -

- Show quoted text -

My! *It's quiet out there.

I've come to the conlusion that a combination of the HLOOKUP and MATCH
functions should do it. *However, I don't seem to be able to get it to
work.

Here are the real Excel sheets and matching cell equations so you have
all the info:

Sheet 1 with results from equations (shown below) in columns B and C:

* * * *A * * * * * *B * * * * * * * C
1 * Date: * * * 11-Apr-09
2 * Name: * * * Cum Hrs From Row No.
3 * Joe 0 * * * 11
4 * Sue #REF! * 14
5 * Mike * * * *70 * * *12
6 * Mary * * * *0 * * * 11

Sheet 2:

1 * * * * * * * * 4-Apr-09 * *11-Apr-09 * *18-Apr-09 *25-Apr-09
2 * Mary
3 * Period Hrs. 80 * * *80 * * *70 * * *60
4 * Cum Hrs * * 80 * * * * * *160 * * * 230 * * 290
5 * Joe
6 * Period Hrs. 75 * * *75 * * *60 * * *80
7 * Cum Hrs * * 75 * * * * * *150 * * * 210 * * 290
8 * Sue
9 * Period Hrs. 60 * * *60 * * *70 * * *80
10 *Cum Hrs * * 60 * * *120 * * 190 * * 270
11 *Mike
12 *Period Hrs. 70 * * *70 * * *60 * * *80
13 *Cum Hrs * * 70 * * *140 * * 200 * * 280

The following is the equation I have come up with so far for Sheet 1
cell B3:B6

* * * =HLOOKUP($B$1,'Sheet 2'!$A$1:$E$13,MATCH(A3,'Sheet 2'!$A$2:$A
$13)+2).

I added 2 rows at the end to get the 2nd row following the row
containing the person's name.

I also broke out the equation

* * * =MATCH(A3,'Sheet 2'!$A$2:$A$13)+2

and put it in cells C3:C6 to see what row the MATCH function is
returning.

As you can, the MATCH function is not returning a correct value and I
have no idea why. *When I
substitute the correct row number for the MATCH function in the
HLOOKUP function, I get the correct results. *So I know the HLOOKUP
function is working correctly.

Anybody have any thoughts? *Please?

Thanks.
Terry- Hide quoted text -

- Show quoted text -

Duh!!!! *I just fixed the problem. *For some unknown reason, I was
ignoring the 3rd argument of the MATCH function. *It must contain a
-1, 0 or +1. *Leaving the 3rd argument blank defaults it to +1 which
is not what I wanted. *When I entered a value of 0 (=MATCH(A3,'Sheet
2'!$A$2:$A$13,0)+3) (which requires an exact match and allows the
lookup array to be in any order) and bumped the row increment from 2
to 3, everything worked fine. *Life is good!- Hide quoted text -

- Show quoted text -

Yoo hoo!!! Anyone out there???? I used to post to this NG and get
lots of responses. Has everyone moved from usenet to the web?
Someone, please respond.