lookup function help

**darkbearpooh1** · 12-08-2008, 01:09 PM

ok i have seperate sheets monday through sunday

in my example on sheet 2 if on sheet 1 named monday i want to look in column c1 and if the number 1 for example is in c1 through c8 or whatever i want the corresponding name in a1 to show up... my actual schedule is more complex but im sending this example to simplify my question and hopefully show what i need... thanks in advance everyone is so awsome on here!

**JBeaucaire** · 12-08-2008, 01:37 PM

An actual sample of the actual data is usually the best test. The answer we give here might not apply to your other "real" need. Just a warning.

Anyway, here is what you looked for:

Please Login or Register  to view this content.

**darkbearpooh1** · 12-08-2008, 02:20 PM

thanks!!!!! you guys are the best! i think thats perfect is there a way if there is no 1 there to just leave the space blank instead of #N/A error? like say if the value is not equal to 1 then no name shall show just blank

**darkbearpooh1** · 12-08-2008, 02:22 PM

ps. yeah i know i should have sent the real sheet but once i did that the the users said you shouldn't do that or at least change the names ext as it is so large too much trouble

**JBeaucaire** · 12-08-2008, 02:24 PM

Please Login or Register  to view this content.

If this solves your original issue(s), be sure to EDIT your original post and set the PREFIX box tom [SOLVED]. New questions should be posted in a thread of their own with appropriate titles (to help people who search the forum for answers).

Cheers.

**JBeaucaire** · 12-08-2008, 02:26 PM

Originally Posted by darkbearpooh1

ps. yeah i know i should have sent the real sheet but once i did that the the users said you shouldn't do that or at least change the names ext as it is so large too much trouble

I disagree. Randomizing data is simple with quick SEARCH/REPLACE functions, and with the free help you get here, it is worth your trouble to assist us, isn't it?

**darkbearpooh1** · 12-09-2008, 08:48 AM

thanks! your the best!

**darkbearpooh1** · 12-11-2008, 08:57 AM

Ok, now I am going to go ahead and wise up and send my actual data as you were right I should have did that to start with... here is my best attempt to explain what I need and what is going on.. in cell B6 on training schedule worksheet i want it to enter the name of the employee that needs to be trained, we will use monday worksheet as the example obviously i need it for each day though. ok my idea was to enter 1 in any cell from L28:L46 and have the corresponding name from h:28:h46 show up... the formula you gave works perfect for that but my problem is i need more than one person training under monday column on the training schedule sheet so i wanted to change the formula to 2 for the second person and 3 for the third and so on... but for some reason i am getting inaccurate results when I change the number. Again thank you for taking your time to look at this and help! I actually would like it to be able to look not only in L28:L46 but also BB28:BB46 and give the name to the right of BB28 in BC28:BC46 as well but if thats not possible i have a way around that I will simply divide the training schedule sheet into 2 parts for the day shift all the numbers and corresponding names on the left side and the numbers and corresponding names on the right side. Thanks

**diecot** · 12-11-2008, 11:49 AM

I have two question.
I want to drag formulas between cells, and also they will need to drag in different worksheets.
For instance, i want to bring from different worksheets (like about 100 worksheets) a data from an specific cell. How can I make it so I dont have to do a link per each worksheet?
The second question may be solved with the response on the first one.
I want to search between worksheet. I want to do a formula to identify an specific worksheet name, so it brings me a specific value of that specific worksheet.
i.e. I want to bring value C2, from a worksheet named E003. So I need a formula to first identify E003 from all the other worksheets, and then at that sheet, bring me C2.
Is there anything I could do for this?

Thank you very much

**darkbearpooh1** · 12-11-2008, 09:11 PM

Ok, now I am going to go ahead and wise up and send my actual data as you were right I should have did that to start with... here is my best attempt to explain what I need and what is going on.. in cell B6 on training schedule worksheet i want it to enter the name of the employee that needs to be trained, we will use monday worksheet as the example obviously i need it for each day though. ok my idea was to enter 1 in any cell from L28:L46 and have the corresponding name from h:28:h46 show up... the formula you gave works perfect for that but my problem is i need more than one person training under monday column on the training schedule sheet so i wanted to change the formula to 2 for the second person and 3 for the third and so on... but for some reason i am getting inaccurate results when I change the number. Again thank you for taking your time to look at this and help! I actually would like it to be able to look not only in L28:L46 but also BB28:BB46 and give the name to the right of BB28 in BC28:BC46 as well but if thats not possible i have a way around that I will simply divide the training schedule sheet into 2 parts for the day shift all the numbers and corresponding names on the left side and the numbers and corresponding names on the right side. Thanks i posted it again because some one posted a different question in my question

**JBeaucaire** · 12-11-2008, 09:40 PM

Originally Posted by diecot

I have two question.
I want to drag formulas between cells, and also they will need to drag in different worksheets.
For instance, i want to bring from different worksheets (like about 100 worksheets) a data from an specific cell. How can I make it so I dont have to do a link per each worksheet?
The second question may be solved with the response on the first one.
I want to search between worksheet. I want to do a formula to identify an specific worksheet name, so it brings me a specific value of that specific worksheet.
i.e. I want to bring value C2, from a worksheet named E003. So I need a formula to first identify E003 from all the other worksheets, and then at that sheet, bring me C2.
Is there anything I could do for this?

Thank you very much

There are two concepts you can make use of, 3D references and INDIRECT function.
=============
INDIRECT
Let's say on your TALLY sheet, you want to have a cell bring you the data from cell B1 on any sheet you name in a cell. The way you do that is with INDIRECT, it let's you "construct" a reference from pieces.

If the cell you want to type a sheetname in is cell A1, and the cell on the other sheet(s) you want data from will be B1, then the formula to construct it is:

=INDIRECT("'"A1&"'!B1)

If you type Sheet2 into cell A1, that converts to =('Sheet2'!B1)
Pretty cool.

This works to create references to sheets you haven't even created yet, it just returns zero instead of a error.
===========
3D-Reference
Let's say you want a single cell on your tally sheet to sum together cell B1 from a bunch of other sheets. You can create a 3D-Reference to them.

On your tally sheet, the formula would be:
=SUM(Sheet1:Sheet3!B1)

That's a relative reference, too, so dragging it down would adjust the same way it would if it were a reference to a single sheet.

Can you use these concepts to solve your problem?

**darkbearpooh1** · 12-12-2008, 11:02 AM

my post before diecot, I still can't seem to draw the names i need by specific number being entered.. ok say i enter 1 as my reference number its fine if i enter a 1 on monday or if i enter a 2 on monday sheet it doesn't show a name on the training sheet which is fine but if i put the value i need under the specified value of "1" say 0 then it still gives me that name which is not correct because i need a way to pick several different people for training as you see on the training sheet... so the next cell for the 2nd person that needs to be trained would be
=IF(ISERROR(LOOKUP(2,monday!$C$1:$C$5)),"",LOOKUP(2,monday!$C$1:$C$5,monday!$A$1:$A$5))

but that code gives a name if a 1 is the reference or a 2.... it doesn't even have to be numeric values but i need a way to easily draw the correct person's name if they need to be trained.. the number idea was just an idea i had... or if i can put a specific pattern fill on a certain person's line bar and have it show the person's name on the training schedule that would be great!! i can't use a color fill because its already used to calculate the person's time in & out with a macro.... Thanks for any ideas!!!

**darkbearpooh1** · 12-12-2008, 11:04 AM

i just realized the formula i sent was for the test sheet... but it doesn't matter i am sure you understand what i am talking about if not i'll explain

**JBeaucaire** · 12-12-2008, 01:03 PM

=IF(ISERROR(LOOKUP(2,monday!$C$1:$C$5)),"",LOOKUP(2,monday!$C$1:$C$5,monday!$A$1:$A$5))

Monday!C1:C5 is a blank range.

Tell me what cell you're putting that formula and what result you expect?

**JBeaucaire** · 12-12-2008, 01:12 PM

I'm sorry, I'm a little slow in the morning. Need coffee. Can you highlight some cells in yellow and enter desired results and tell me where you pulled it from, maybe that will help me see it.

**darkbearpooh1** · 12-12-2008, 07:05 PM

np, look i appreciate your help so much!

ok on this worksheet im sending i highlighted in red on the training schedule sheet where the formula should go it needs to look on the monday sheet in L28:L46 and if i put a number 1 in there it will give the name corresponding to it in H28:H46... and on the training schedule sheet again in the next spot for the next person that needs to be trained i will just re write the formula but put a 2 and on the monday sheet i can just enter a 2 and have the corresponding name show up.... i kinda got that to work with a if function but it doesn't scroll down the whole range only for one cell...

**JBeaucaire** · 12-12-2008, 07:40 PM

Here you go. Tan cells are my formulas...I had to put some numbers in the POS column to get Monday and Tuesday etc to work. If there isn't a single number in the POS column, it gives an error. You can probably trap that, too, but this is working now.

Note, you HAVE to put 1, 2, 3 etc. down the POS column in order, don't skip around or that will break the LOOKUP. The first person to train at the top of the list gets the 1, the ext one down gets 2, etc. OK?