Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup returning more than one result
Dear all,
please i need your help in solving the below problem: I am preparing a report to summarize the Shortage amount for every employee in each of the 3 items that we are selling to customers Sheet 1: Item1 shortages as follow Staff ID Shortage amount Sheet 2: Item2 shortages as follow Staff ID Shortage amount Sheet 3: Item3 shortages as follow Staff ID Shortage amount I need a function to consolidate in sheet4 the shortage for each one of the staff in the 3 items Sheet4: Staff ID Item1 Item2 Item3 xxx xxx xxx xxxx BR, Khaled |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup returning more than one result
One set-up using SUMIF with INDIRECT which might work here ..
In Sheet1, Sheet2 & Sheet3 (all assumed identically structured) assume Staff ids are listed in A2 down, with corresponding shortage amounts in B2 down, eg: Staff ID Shortage Amt 1111 60 1112 80 1113 80 etc Then in Sheet4, assume staff ids are listed in A2 down and actual sheetnames are listed in B1 across to D1, viz : Staff ID Sheet1 Sheet2 Sheet3 1111 1112 1113 etc you could place in B2: =SUMIF(INDIRECT("'"&B$1&"'!A:A"),$A2,INDIRECT("'"& B$1&"'!B:B")) Copy B2 to D2, fill down to populate the table. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "kshaheen" wrote: Dear all, please i need your help in solving the below problem: I am preparing a report to summarize the Shortage amount for every employee in each of the 3 items that we are selling to customers Sheet 1: Item1 shortages as follow Staff ID Shortage amount Sheet 2: Item2 shortages as follow Staff ID Shortage amount Sheet 3: Item3 shortages as follow Staff ID Shortage amount I need a function to consolidate in sheet4 the shortage for each one of the staff in the 3 items Sheet4: Staff ID Item1 Item2 Item3 xxx xxx xxx xxxx BR, Khaled |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning header row as a result... | Excel Worksheet Functions | |||
vlookup based on random result returns incorrect result | Excel Worksheet Functions | |||
Returning Forumla For Result | Excel Discussion (Misc queries) | |||
vlookup returning a n/a result | Excel Worksheet Functions | |||
vlookup and filename returning same result on each sheet. | Excel Discussion (Misc queries) |