A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Vlookup returning more than one result



 
 
Thread Tools Display Modes
  #1  
Old June 16th 07, 11:22 PM posted to microsoft.public.excel.misc
kshaheen
external usenet poster
 
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


Ads
  #2  
Old June 17th 07, 01:50 AM posted to microsoft.public.excel.misc
Max
external usenet poster
 
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
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
returning header row as a result... mj Excel Worksheet Functions 17 March 6th 06 01:13 AM
vlookup based on random result returns incorrect result rickat Excel Worksheet Functions 1 December 6th 05 01: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 10:08 PM


All times are GMT +1. The time now is 09:59 PM.


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