Lookup a value across sheets

[KSpider]

I have a master sheet of workers names, the names are in the A column.
in the B colm i want a formula that will go thru department sheets an
find the name in A, and return the value in c1 on that sheet whic
contains the department name.

Master Sheet
cell a2 Joe Smith
cell a3 (returns the word HR)

HR sheet
cell a12 Joe Smith
Cell c1 HR

RD Sheet
cell a2 Allie Jones
cell c1 RD

Does this make sense?

I trying to use Index and a function called Threed to no avail
Thanks,
Spide

 

[Myrna Larson]

Is ThreeD a UDF written in VBA? If so, can you post it?

You may need VBA for this. Many worksheet functions can't be written as 3D
formulas. I have included below a somewhat generic function. It searches
column 1 on ALL worksheets EXCEPT the sheet that contains the formula. When
there's a match, it returns the value from cell C1 on that sheet.

Function DeptName(EmployeeName As String) As String
Dim i As Long
Dim x As Variant
Dim SkipSheet As String

SkipSheet = Application.Caller.Parent.Name

For i = 1 To ThisWorkbook.Worksheets.Count
With Worksheets(i)
If .Name <> SkipSheet Then
x = Application.Match(EmployeeName, .Columns(1), 0)
If IsNumeric(x) Then
DeptName = .Range("C1").Value
Exit Function
End If
End If
End With
Next i
DeptName = "Not found!"
End Function

 

[Frank Kabel]

Hi
you may post the formula you have tried. Also it would be easier if you
duplicate the department name in column C for all rows

 

[Frank Kabel]

Is ThreeD a UDF written in VBA? If so, can you post it?

Hi Myrna
THREED is part of the add-in MoreFunc.xll (see:
http://longre.free.fr/english)

Frank

 

[Myrna Larson]

Thanks. I guess that means he can't post it <g>.

Hi Myrna
THREED is part of the add-in MoreFunc.xll (see:
http://longre.free.fr/english)

Frank

 

[Frank Kabel]

unfortunately not <vbg>
would be interesting to see Laurents code!!

Frank

 

[John]

I use the vlookup formula for this all the time, but I
have never tried crafting it to then check multiple
sheets.

 

[Guest]

=IF(ISNA(MATCH(A6,HR!A$1:A$100,0)),IF(ISNA(MATCH(A6,RD!A$1:A$100,0)),IF(ISNA(MATCH(A6,Sales!A$1:A$100,0)),"No Such Person In Any Department",Sales!C$1),RD!C$1),HR!C$1)

Works for 3 departments (i.e. HR, Sales & RD) and by extension would work for up to 7 departments, but after that, because of the limit of 7 nested if statements, it will not work.

unlikeKansas

 

[Harlan Grove]

I have a master sheet of workers names, the names are in the A column.
in the B colm i want a formula that will go thru department sheets and
find the name in A, and return the value in c1 on that sheet which
contains the department name.

Master Sheet
cell a2 Joe Smith
cell a3 (returns the word HR)

HR sheet
cell a12 Joe Smith
Cell c1 HR

RD Sheet
cell a2 Allie Jones
cell c1 RD

Lemme guess, you have multiple employees' names but only a single department
name in each department worksheet. If your Master!A3 formula looks like

=INDEX(THREED(AAA:ZZZ!C1),MATCH(A2,THREED(AAA:ZZZ!A2:A101),0))

and you're getting #REF! errors, it'd be due to the lookup range,
AAA:ZZZ!A2:A101, exceeding the size of the results range, AAA:ZZZ!C1. Try
something like

=INDEX(THREED(AAA:ZZZ!C1),ROUNDUP(MATCH(A2,THREED(AAA:ZZZ!A2:A101),0)/100,0)
)

Also note that if you're willing to wade into the filth and slime of Excel's
undocumented array of ranges semantics, you wouldn't need THREED.

=INDEX(T(INDIRECT(T(OFFSET(WSList,INT(seq3D/MEPD),0,1,1))&"!C1")),
1+(MATCH(A3,T(INDIRECT(T(OFFSET(WSList,INT(seq3D/MEPD),0,1,1))
&"!A"&(2+MOD(seq3D,MEPD)))),0)-1)/MEPD)

where WSList would be a list of the department worksheet names, MEPD would
be the maximum number of employees per department (or the number of rows of
employee names to check in each department worksheet), and seq3D defined as

=ROW(INDIRECT("1:"&(ROWS(WSList)*MEPD)))-1

Note that the '2' in '(2+MOD(...' is the starting row number for employee
names in each department worksheet.

 

[Anthony Slater]

I use the following formula to look accross various sheets
and it works fine for me.

If(iserror(vlookup(a2,HRsheet!$A$1:$C$9999,3,0))=FALSE,
(vlookup(a2,HRsheet!$A$1:$C$9999,3,0)),IF(iserror(vlookup
(a2,RDsheet!$A$1:$C$9999,3,0))=FALSE,(vlookup(a2,RDsheet!
$A$1:$C$9999,3,0)))

Adjust the ranges to suit as neccessary

Hope this helps.

 

[Frank Kabel]

[...]

Also note that if you're willing to wade into the filth and slime of Excel's
undocumented array of ranges semantics, you wouldn't need THREED.
(seq3D/MEPD),0,1,1))
&"!A"&(2+MOD(seq3D,MEPD)))),0)-1)/MEPD)

Hi Harlan
I think 'filth and slime' is the correct description for
this
How do you often say: A quibble:
II would assume your formula errors out if the sheet name
contains spaces, etc. so I'd propose:

=INDEX(T(INDIRECT("'! & T(OFFSET(WSList,INT
(seq3D/MEPD),0,1,1))&"'!C1")),
1+(MATCH(A3,T(INDIRECT("'" & T(OFFSET(WSList,INT
(seq3D/MEPD),0,1,1))
&"'!A"&(2+MOD(seq3D,MEPD)))),0)-1)/MEPD)

just do deal with this kind of sheetnames

Frank

 

[hgrove]

Frank Kabel wrote...
...

How do you often say: A quibble:
would assume your formula errors out if the sheet name
contains spaces, etc. so I'd propose:

When using a list of worksheet names, who's to say that the names i
that list don't contain enclosing single quotes?

=INDEX(T(INDIRECT("'! & T(OFFSET(WSList,INT(seq3D/MEPD),

...

If you're gonna quibble, don't make typos! That > "'! & < is a synta
error. You meant > "'" & <