Referencing a cell where lookup value is not on same row
 

Hi,

I need some help... Here is my data:
_____________________________________________
A B C D
1 ID Number Name Hours
2 10153 Joe 8
3 8
4 8
5 Total 24
6
7 10219 Bob 5
8 5
9 Total 10
10
11 10569 Tim 10
12 8
13 8
14 5
15 Total 31
___________________________________________

Basically, I want to copy the ID number, Name, and Total onto a separate
worksheet minimizing the need for manual data entry e.g.
10153 Joe 24
10569 Tim 31
etc.

Basically, I can copy the ID number onto the new worksheet and use a vlookup
to get the name. But I'm having difficultly referencing the Total since it's
not on the name row as the ID and Name. Any suggestions? Thanks!

Referencing a cell where lookup value is not on same row
 

As far as I know, I am afraid to say that Excel need to have flat database to
work on, meaning that the list should be fill up with values and there is no
blank column


"Aaron" wrote:

Hi,

I need some help... Here is my data:
_____________________________________________
A B C D
1 ID Number Name Hours
2 10153 Joe 8
3 8
4 8
5 Total 24
6
7 10219 Bob 5
8 5
9 Total 10
10
11 10569 Tim 10
12 8
13 8
14 5
15 Total 31
___________________________________________

Basically, I want to copy the ID number, Name, and Total onto a separate
worksheet minimizing the need for manual data entry e.g.
10153 Joe 24
10569 Tim 31
etc.

Basically, I can copy the ID number onto the new worksheet and use a vlookup
to get the name. But I'm having difficultly referencing the Total since it's
not on the name row as the ID and Name. Any suggestions? Thanks!

Referencing a cell where lookup value is not on same row
 

Hi,

It is unclear if you have the flexibility to clean the source data up or
not, but if you do could remove the blank & total rows, fill in the ID#'s and
Names and then you can run a SUMIF or SUMPRODUCT to get the totals to anothe
sheet.

Do you have that flexibility?

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Aaron" wrote:

Hi,

I need some help... Here is my data:
_____________________________________________
A B C D
1 ID Number Name Hours
2 10153 Joe 8
3 8
4 8
5 Total 24
6
7 10219 Bob 5
8 5
9 Total 10
10
11 10569 Tim 10
12 8
13 8
14 5
15 Total 31
___________________________________________

Basically, I want to copy the ID number, Name, and Total onto a separate
worksheet minimizing the need for manual data entry e.g.
10153 Joe 24
10569 Tim 31
etc.

Basically, I can copy the ID number onto the new worksheet and use a vlookup
to get the name. But I'm having difficultly referencing the Total since it's
not on the name row as the ID and Name. Any suggestions? Thanks!

Referencing a cell where lookup value is not on same row
 

Hi Shane,

Like I said, I want to minimize the amount of manual actions I would have to
perform, so i would say no, this couldn't work. Also, it's worth noting that
the values in the totals are already precalculated since the source data
comes from running a report in a different program.

Although, you might be on to something. Using the same data, let's say I
copy the ID Number from A2 to A6. If I do some kind of a vlookup on the ID
Number, is there a way to reference the last one, thus providing me with the
corresponding value in column D?

"Shane Devenshire" wrote:

Hi,

It is unclear if you have the flexibility to clean the source data up or
not, but if you do could remove the blank & total rows, fill in the ID#'s and
Names and then you can run a SUMIF or SUMPRODUCT to get the totals to anothe
sheet.

Do you have that flexibility?

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Aaron" wrote:

Hi,

I need some help... Here is my data:
_____________________________________________
A B C D
1 ID Number Name Hours
2 10153 Joe 8
3 8
4 8
5 Total 24
6
7 10219 Bob 5
8 5
9 Total 10
10
11 10569 Tim 10
12 8
13 8
14 5
15 Total 31
___________________________________________

Basically, I want to copy the ID number, Name, and Total onto a separate
worksheet minimizing the need for manual data entry e.g.
10153 Joe 24
10569 Tim 31
etc.

Basically, I can copy the ID number onto the new worksheet and use a vlookup
to get the name. But I'm having difficultly referencing the Total since it's
not on the name row as the ID and Name. Any suggestions? Thanks!

Referencing a cell where lookup value is not on same row
 

Try this

Col A Id number
Col B Name
Col C Hours

Col D - cell D2 put this formula ( helping column )
=IF(A2<A3,A2,D1) and drag it down

Now Copy the Col A and Paste it in Col E

delete the blank cells - ( select the Range in Col E - Click F5 |
click special | choose blanks | ok | right click on col E | delete |
shift cells up | ok | esc

Now using Vlookup u can get the names in Col F =VLOOKUP(E2,$A$2:$B
$200,2,0)

Now in cell G2 put this forumla =INDEX($C$2:$C$200,MATCH(E2,$D$2:$D
$200,0)+COUNTIF($D$2:$D$200,E2),0)




On Nov 20, 9:14*pm, Aaron wrote:
Hi,

I need some help... Here is my data:
_____________________________________________
* * * A * * * * * * * B * * * * *C * * * * * * *D
1 *ID Number *Name * * * * * * *Hours
2 *10153 * * * * * *Joe * * * * 8
3 * * * * * * * * * * * 8
4 * * * * * * * * * * * 8
5 * * * * * * * Total * 24
6
7 *10219 * * * * * *Bob * * * * 5
8 * * * * * * * * * * * 5
9 * * * * * * * Total * 10
10
11 10569 * * * * * *Tim * * * * 10
12 * * * * * * * * * * *8
13 * * * * * * * * * * *8
14 * * * * * * * * * * *5
15 * * * * * * *Total * 31
___________________________________________

Basically, I want to copy the ID number, Name, and Total onto a separate
worksheet minimizing the need for manual data entry e.g.
10153 Joe 24
10569 Tim 31
etc.

Basically, I can copy the ID number onto the new worksheet and use a vlookup
to get the name. *But I'm having difficultly referencing the Total since it's
not on the name row as the ID and Name. *Any suggestions? *Thanks!

Referencing a cell where lookup value is not on same row
 

All is good until the last step with the formula :
=INDEX($C$2:$C$200,MATCH(E2,$D$2:$D$200,0)+COUNTIF ($D$2:$D$200,E2),0)

What value am I trying to get exactly?

"muddan madhu" wrote:

Try this

Col A Id number
Col B Name
Col C Hours

Col D - cell D2 put this formula ( helping column )
=IF(A2<A3,A2,D1) and drag it down

Now Copy the Col A and Paste it in Col E

delete the blank cells - ( select the Range in Col E - Click F5 |
click special | choose blanks | ok | right click on col E | delete |
shift cells up | ok | esc

Now using Vlookup u can get the names in Col F =VLOOKUP(E2,$A$2:$B
$200,2,0)

Now in cell G2 put this forumla =INDEX($C$2:$C$200,MATCH(E2,$D$2:$D
$200,0)+COUNTIF($D$2:$D$200,E2),0)




On Nov 20, 9:14 pm, Aaron wrote:
Hi,

I need some help... Here is my data:
_____________________________________________
A B C D
1 ID Number Name Hours
2 10153 Joe 8
3 8
4 8
5 Total 24
6
7 10219 Bob 5
8 5
9 Total 10
10
11 10569 Tim 10
12 8
13 8
14 5
15 Total 31
___________________________________________

Basically, I want to copy the ID number, Name, and Total onto a separate
worksheet minimizing the need for manual data entry e.g.
10153 Joe 24
10569 Tim 31
etc.

Basically, I can copy the ID number onto the new worksheet and use a vlookup
to get the name. But I'm having difficultly referencing the Total since it's
not on the name row as the ID and Name. Any suggestions? Thanks!

Referencing a cell where lookup value is not on same row
 

you will get the total hours

On Nov 21, 1:53*am, Aaron wrote:
All is good until the last step with the formula :
=INDEX($C$2:$C$200,MATCH(E2,$D$2:$D$200,0)+COUNTIF ($D$2:$D$200,E2),0)

What value am I trying to get exactly?

"muddan madhu" wrote:
Try this

Col A Id number
Col B Name
Col C Hours

Col D - cell D2 put this formula ( helping column )
=IF(A2<A3,A2,D1) and drag it down

Now Copy the Col A and Paste it in Col E

delete the blank cells - ( select the Range in Col E - Click F5 |
click special | choose blanks | ok | right click on col E | delete |
shift cells up | ok | esc

Now using Vlookup u can get the names in Col F =VLOOKUP(E2,$A$2:$B
$200,2,0)

Now in cell G2 put this forumla =INDEX($C$2:$C$200,MATCH(E2,$D$2:$D
$200,0)+COUNTIF($D$2:$D$200,E2),0)

On Nov 20, 9:14 pm, Aaron wrote:
Hi,

I need some help... Here is my data:
_____________________________________________
* * * A * * * * * * * B * * * * *C * * * * * * *D
1 *ID Number *Name * * * * * * *Hours
2 *10153 * * * * * *Joe * * * * 8
3 * * * * * * * * * * * 8
4 * * * * * * * * * * * 8
5 * * * * * * * Total * 24
6
7 *10219 * * * * * *Bob * * * * 5
8 * * * * * * * * * * * 5
9 * * * * * * * Total * 10
10
11 10569 * * * * * *Tim * * * * 10
12 * * * * * * * * * * *8
13 * * * * * * * * * * *8
14 * * * * * * * * * * *5
15 * * * * * * *Total * 31
___________________________________________

Basically, I want to copy the ID number, Name, and Total onto a separate
worksheet minimizing the need for manual data entry e.g.
10153 Joe 24
10569 Tim 31
etc.

Basically, I can copy the ID number onto the new worksheet and use a vlookup
to get the name. *But I'm having difficultly referencing the Total since it's
not on the name row as the ID and Name. *Any suggestions? *Thanks!