Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 16th 07, 11:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 1
Default 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   Report Post  
Old June 17th 07, 01:50 AM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
returning header row as a result... mj Excel Worksheet Functions 17 March 6th 06 02:13 AM
vlookup based on random result returns incorrect result rickat Excel Worksheet Functions 1 December 6th 05 02:16 PM
Returning Forumla For Result MIKE0W Excel Discussion (Misc queries) 1 August 18th 05 05:20 AM
vlookup returning a n/a result jeanette.rimmer Excel Worksheet Functions 4 July 14th 05 01:00 AM
vlookup and filename returning same result on each sheet. RogueSwan Excel Discussion (Misc queries) 3 March 22nd 05 11:08 PM


All times are GMT +1. The time now is 11:19 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017