Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup referencing ranges that are variable | Excel Worksheet Functions | |||
Help! Need to lookup referencing 2 variables | Excel Worksheet Functions | |||
Referencing cell text in a lookup | Excel Discussion (Misc queries) | |||
Referencing 3 values for a lookup | Excel Worksheet Functions | |||
What is the syntax for referencing another sheet with 'lookup' | Excel Worksheet Functions |