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

On Dec 11, 10:56*am, ritpg 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.

On Dec 12, 8:54*am, ritpg 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

On Dec 15, 1:54*pm, ritpg 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!

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.