Lookup a value across sheets

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

--
Message posted from http://www.ExcelForum.com

0
7/28/2004 8:56:57 PM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
534 Views

Similar Articles

[PageSpeed] 48

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


On Wed, 28 Jul 2004 15:56:57 -0500, KSpider
<<KSpider.1a4gkv@excelforum-nospam.com>> wrote:

>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
>
>Does this make sense?
>
>I trying to use Index and a function called Threed to no avail
>Thanks,
>Spider
>
>
>---
>Message posted from http://www.ExcelForum.com/

0
anonymous (74722)
7/28/2004 9:25:50 PM
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

--
Regards
Frank Kabel
Frankfurt, Germany


> 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
>
> Does this make sense?
>
> I trying to use Index and a function called Threed to no avail
> Thanks,
> Spider
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
7/28/2004 9:27:27 PM
Myrna Larson wrote:
> 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

0
frank.kabel (11126)
7/28/2004 9:35:34 PM
Thanks. I guess that means he can't post it <g>.

On Wed, 28 Jul 2004 23:35:34 +0200, "Frank Kabel" <frank.kabel@freenet.de>
wrote:

>Myrna Larson wrote:
>> 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

0
anonymous (74722)
7/28/2004 9:44:15 PM
unfortunately not <vbg>
would be interesting to see Laurents code!!

Frank


Myrna Larson wrote:
> Thanks. I guess that means he can't post it <g>.
> 
> On Wed, 28 Jul 2004 23:35:34 +0200, "Frank Kabel"
> <frank.kabel@freenet.de> wrote:
> 
>> Myrna Larson wrote:
>>> 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
0
frank.kabel (11126)
7/28/2004 9:51:05 PM
I use the vlookup formula for this all the time, but I 
have never tried crafting it to then check multiple 
sheets.


>-----Original Message-----
>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
>
>Does this make sense?
>
>I trying to use Index and a function called Threed to no 
avail
>Thanks,
>Spider
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
7/28/2004 10:48:41 PM
=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

"KSpider >" wrote:

> 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
> 
> Does this make sense?
> 
> I trying to use Index and a function called Threed to no avail
> Thanks,
> Spider
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
7/29/2004 5:31:02 AM
"KSpider >" <<KSpider.1a4gkv@excelforum-nospam.com> wrote...
>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.


0
hrlngrv (1990)
7/29/2004 8:10:06 AM
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.



>-----Original Message-----
>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
>
>--
>Regards
>Frank Kabel
>Frankfurt, Germany
>
>
>> 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
>>
>> Does this make sense?
>>
>> I trying to use Index and a function called Threed to 
no avail
>> Thanks,
>> Spider
>>
>>
>> ---
>> Message posted from http://www.ExcelForum.com/
>
>.
>
0
7/29/2004 8:29:46 AM
[...]
>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)

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

0
frank.kabel (11126)
7/29/2004 12:31:22 PM
Frank Kabel wrote...
...
>How do you often say: A quibble:
>[I] 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 > "'" & <

--
Message posted from http://www.ExcelForum.com

0
7/29/2004 5:18:36 PM
Reply:

Similar Artilces:

Need to Prevent a protected sheet from being copied?!?
I have a spread sheet that contains sensitive pricing details not to b viewed by the users, but needed due to being referenced in variou equations. This file is to be used by multiple persons who will get this vi email, and then enter data into unlocked cells in order to get a fina 'price'. I have protected both the sheets and the workbook. BUT I can still hilight and copy individual protected sheets and the paste them into a new workbook at which point it is completel unprotected. Ultimately I need to email this to others. Is there a way to prevent this copying and pasting? Thanks...

Customer lookup
I would like to see RMS have a pop up window that would indicate "Customer not found" or something similar instead of going to the closest match. Problem - When employee badges/discount cards/etc are being scanned at POS registers; If the account does NOT exist; The program appears to currently find the closest match to the number scanned. (an existing account) Issues - Potential exists for intentional fraud by customer Timely process to check entire number Potential for employee error in not verifying name with client Suggested solution – message should pop up, “account does...

hide columns on a protected sheet???
When using MS Excel 2003: I have turned on "Protect Sheet" to protect some formulas from being deleted. Prior to turning on protection I unlocked the cells that I wanted users to be able to edit. And I have checked the box to "Allow all users of this worksheet to:" "Delete Columns". What I *don't* see on the "Protect Sheet" menu is an option to "Hide Columns" Do you know of any way that I can allow users to do this, without turning off the protection in MS Excel 2003? Check "Format Columns" option "under...

Code Check Request
Dear all, I have two tables, POs and Orders. POs represent purchase orders from our client, and Orders represent internal orders. One PO can be related to many orders. Each order has a Sales Rep (data in a different table, ID in Orders as FK). The PO number is not a unique ID in the client order data (as one PO can cover many separate client orders). Graphically, the structure looks something like this (* indicates a unique ID): POs Orders Reps Client Order ID* ORDER ID* PO Code <---------> PO NUM SALES REP ID <------- SALES REP ID* In theory, each PO...

Updating subform control based on another control value in the SAME subform.
Greetings, Calculated result is a field that is updated based on the two other fields, this is done in the subforms query and as based on an expression cannot be overtyped (which is good!). All I want to do is once a value appears in the field is to populate another field (see code below). Runs without errors but see no results. Any help would be appreciated. Private Sub CalculatedResult_AfterUpdate() Dim CResult As Integer CResult = Me.CalculatedResult.Value Me.ActualResult.Value = CResult Me.ActualResult.Requery End Sub Thanks Rob SOLVED "Rob W" <Whllrob@aol.com&g...

help with 2 xl sheets...
I need help with 2 worksheets. first sheet is a time schedule sheet. I need to count the total number of shifts entered in a week The sheet is set up so columns are days of weeks, each person has 2 rows, top row is the start time bottom row is end time, time is entered is in 24Hr format and shifts start at various times of days. Second I have a row of numbers 1 to 20 that is averaged in row 21 in column A. Row 1 has the most current figure, row 20 is oldest, Can I set it up so I can enter the most current number in row 1 column A and all the existing figures automatically bump down 1 row? ...

Unwanted zero values in a graph
Hi! Here I am once again. I have some graphs in a spreadsheet, and the sourcedata is being filled with values as time goes on and I key in new values every fisrt day of the month. Hence the cells containing future readings is empty. The graph jumps from the latest value lets say 40, and stoops down to zero on the X-axis, and follows it the for the rest of the year. How can I get my graphs to stop showing any line after the latest typed value? I have tried every possible combination when cliking the graph, choose Options on the Tools menu, and the tab named Graph, but in vain. Nothing happe...

#VALUE! problem; not enough cond formatting slots to fix.
I usu. deal with #VALUE! with conditional formatting and "iserror". In a current spreadsheet, I can't fix errors because I need alternating row colours which take 2 of the 3 conditional formatting slots. To fix this for the 2 colours of rows, I'd need an additional conditional formatting slot (i.e., 4 instead of 3, total) so that the font colours could be accommodated against 2 backgrounds. .... the cell that gets a #VALUE! in it is a calcuation between dates. The first date is typed into a cell to the left of this calculation one and it corresponds to the date tha...

lookup and picklist
hi there, I have a lookup (as customer with 100 contacts and users internal external users/contacts). based on the selection of lookup value (user/contact) an automaitcally picklist value should be change... example. i have a lookup field with (Users/Contact). and a piklist field with departments (7 departments). if john belongs to IT department. when i select john from lookup then automatically in picklist IT department should show. please help me. Regards, syed. I would suggest Workflow as easy solution, it depends, if it can be filled after creating record or immediately &q...

Load the address from the lookup table to corresponding contact fi
Is it possible to create a custom mapping / loading between the contact and account entities in such a way that when selected the existing account value from the lookup table of account on the contact page, the existing address fields will be populated into corresponding contact fields? Hi Tom, Yes, it is possible with the help of little coding. Basically you need to add JScript code in onChange event of the Account lookup on Contact entity form. That code will be calling the CRM webservice and fetching the account's address fields. Following posts will explain you a little about ...

Need to return a value!
HI, I have eight columns of data. Each cell either has N/A (returned from another sheet) or a number, only one column will have a number. My question is, how do i show the number in another column?? Current SalesP Total Cost (L) 24,595.00 #N/A 18628.18 #N/A #N/A #N/A #N/A #N/A #N/A #N/A 718 #N/A 718.18 #N/A #N/A #N/A #N/A #N/A #N/A #N/A 17,140.00 #N/A 17140.00 #N/A #N/A #N/A #N/A #N/A #N/A #N/A Try this… =SUMIF(B1:J1,"<>#N/A",B1:J1) Change the cell reference of B1:J1 to your desired range, if required. If this post helps,...

Can I obtain x values from y values on an Excel chart?
I've constructed an exponential curve, is there a function whereby I can enter new y values to obtain the corresponding x values, instead of having to print it off and read off manually? Many thanks from a novice -Helen Moore if Y=A*Exp(BX); then Ln(Y) - Ln(A) = BX or X= (Ln(Y) -Ln(A))/B Also remember you can use LOGEST or LINEST to extract the trendline coefficients into cells rather than copying them form equation on chart; see http://www.stfx.ca/people/bliengme/ExcelTips/ExponentialTrendline&%20LOGEST.htm http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm best wishes...

Can I show a reference value with a string?
Hi All, For a single field, can I show a refernce value (ex: =B2+B3) and then follow up some strings? the final result looks like this: The price is 20 Thank you very much. Best regards, Boki. ="The price is "&B2+B3 -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=486137 If you want to use a format like currency you need to use the TEXT function ="The price is &q...

Cost basis < market value = gain?
I have a fund in Money that is showing: - cost basis of $500 - market value of $475 and gain of $65 How is that possible? I did have a large gain in this fund at one time and sold most of the holding. I thought maybe somehow that threw off Money but if that's the case, then there is a bug. In microsoft.public.money, brett wrote: >I have a fund in Money that is showing: > >- cost basis of $500 >- market value of $475 >and gain of $65 > >How is that possible? Distributions (from typical mutual funds, that would be dividends and LT CG paid) of $100 would explain i...

Why am I getting #VALUE! when working with a TIMEVALUE formula?
This formula is working on other pages within the same workbook, yet it does not work on a newly inserted worksheet. NOTE: this orginial workbook was created in an early version of Excel (95 or 98), now using 2000. Hi what is your exact formula and what is in the referenced cells? -- Regards Frank Kabel Frankfurt, Germany TIMEVALUE ERROR wrote: > This formula is working on other pages within the same workbook, yet > it does not work on a newly inserted worksheet. NOTE: this orginial > workbook was created in an early version of Excel (95 or 98), now > using 2000. The for...

Can you script preset values for forms?
I have a Third Party software that is based on Access 2003. They have a login screen that requires a few pieces of information (SQL Server Name, Port number, SQL Database name, ID and Password) in addition to the Database User Name and Password. Depending on which office I'm in, there is a different SQL Server I will need to connect to. What would be the easiest way to PREPOPULATE the infomation for the specific office so that a user only needs to click on an Icon? I know how to connect the approprieate drive via scripting, just unsure how to get the info populated. I'd ask ...

Best way to get return value from a .bat file
Platform: VC7, MFC, windows 2000 Hi, What is best way to get return value from a .bat to the calling program. I am using bat file to get return from a bat file For example bat file test.bat sets a value in environment variable as given below set %1 = 12345 when i execute bat file from cmd shell c:\>test AAA this sets value AAA=12345. If I execute same command from our program using CreateProcess. GetEnvironmetVariable function fails for the environment variable AAA. May i know what mistake am i doing? Regards Vijay vijay wrote: > What is best way to get return value from a ...

GP 9.0 Modifier/VBA Cannot close lookup window
Hi: The look up window ( VendorDetail(Grid) ) for vendors still open when close the vendor’s maintenance window. I tried this: Private Sub Window_AfterClose() If VendorsDetail.IsLoaded = True Then VendorsDetail.IsLoaded = False End If End Sub But I had Compile error Method or Data member not found. Any idea to close this window after VendorMaintenance close will be appreciated. Regards for all of you Hello Valentine, what i followed from your question here is you want to close the lookup of the vendor list when you close the window of vendor maintenance. here a...

Formula for getting values in File-Properties-Custom
Is there a formula for retrieving values stored in the variables in File-Properties-Custom? Thx ...

Update of data feed in sheet
Hi Can someone help? I have a spreadsheet that retrieves data from an external source, this works fine. The size of the download is such that it takes about 15 to 20 minutes to complete. This is not to much of a problem apart from the fact that if I touch the mouse while hovering over the sheet when it is retrieving the data the process stops. I know I can restart the retrieval process by hitting the F9 key but is there anything I can do to stop the mouse click from ending my download? Any thoughts greatly appreciate. Regards Dave Hickey ...

Blank cell if no value
I have a spreadsheet in which I enter numbers in, then calculate a certain range - sum(f4:f10). The cell that contains this formula always shows 0 if nothing is entered in this range. How can I edit this forumla that will display nothing in the cell if nothing is entered, if something is entered, then it will display the sum Any suggestions? -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24735 View this thread: http://www.excelforum.com/showthread.php?threadid=397250...

Populating a UserForm'sTextBox with a value
Hi, I would like to know how to get a UserForm's TextBox to show the value of a cell when the UserForm is opened. I named the TextBox GiftTextBox, and I was hoping something like GiftTextBox.Value = ActiveCell.Offset(0, 6).Value would do it but I can't get that to work. Is there a simple way for the ActiveCell.Offset(0, 6).Value to be shown somehow on the UserForm? I don't care whether it's in a TextBox or some other box, as long as I can get the value to appear on the UserForm. Rob Rob, Something like... UserForm1.GiftTextBox.Value = ActiveCell.Offset(0, 6).Value...

Change the value of a text box in Form
Hi. I have a textbox named PseudoCLLI that and i want this Auto-generated suggested value equal to the ShortCLLI. How do i do this? PLUS, it is suggested meaning i can overwrite what's there. emerlita@shaw.ca wrote: > Hi. > > I have a textbox named PseudoCLLI that and i want this Auto-generated > suggested value equal to the ShortCLLI. How do i do this? PLUS, it is > suggested meaning i can overwrite what's there. In the AfterUpdate event of the ShortCLLI TextBox... Me.PseudoCLLI = Me.ShortCLLI -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBra...

Lockink the scrolling of a cursor in a protected sheet.
Hi! Can someone help me please? I have a large worksheet that's protected and most of the cells are locked When I scroll thru the sheet with the arrow keys (left arrow key, for example) instead of stopping when it hits left side of the sheet (column a) it jumps all the way to the right of the sheet. The same thing happens when i scroll up with the up arrow key, it moves to the bottom of the sheet instead of stopping when i hit the top (row 1 It's driving me nuts I want it to stop when you reach the left or top, just like it does when the sheet is unprotected thanx in advanc Robert~ ...

Custom Format for Null or Zero Values
I am using the following custom format for null and zero values... #,##0.00;(#,##0.00)[Red];-;- ....but when my report runs all the columns do not line up. The dash ("-") for null or zero values is much farther right than the rest fo the values. I would like it to line up with the last decimal place showing. Please help... ...