Hi Biff!
Yes, definitely. No need to complicate things. But it does take all
the fun out of it! :)
In article ,
"Biff" wrote:
Hi!
Answered this earlier in .Functions without any details included in the OP.
After seeing what the data looks like perhaps something a little less
complicated is in order.
List the sheet names in a range, say H1:H10.
Normally entered:
=SUMPRODUCT((SUMIF(INDIRECT("'"&H1:H10&"'!A2:A10") ,A1,INDIRECT("'"&H1:H10&"'!B
2:B10"))))
Biff
"Domenic" wrote in message
...
Try...
=VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1","Sheet2"} ,MATCH(TRUE,COUNTIF(IND
IRECT("'"&{"Sheet1","Sheet2"}&"'!A2:A4"),A1)0,0)) &"'!A2:B4"),2,0)
OR
=VLOOKUP(A1,INDIRECT("'"&INDEX(B1:B2,MATCH(TRUE,CO UNTIF(INDIRECT("'"&B1:B
2&"'!A2:A4"),A1)0,0))&"'!A2:B4"),2,0)
...confirmed with CONTROL+SHIFT+ENTER, where A1 contains the salesman of
interest, and B1:B2 contains your sheet names. Adjust the ranges
accordingly.
Hope this helps!
In article ,
"TPratt" wrote:
Is there a way to use a vlookup to look across multiple tabs within an
excel
file?
Example
Tab 1 contains sales data from salesmen in State A
Salesman Sales
1 100
2 200
3 500
Tab 2 contains sales data from salesmen in State B
Salesman Sales
5 1000
6 600
7 800
From another tab, I would like to be able to type in a salesman's number
and
get their sales, regardless of which tab they're on, without having to
first
combine the data onto one sheet.
* The data is in the exact same place on each tab. (ie. Salesman 1 and
Salesman 5 are each in Cell A2 on their respective tabs)
|