Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP for this problem?
I have an employee's name in cell A5 on a SS called Repair rotas.xls on the
Plymouth Staff tab. The Saturday rota for this employee, called 'Saturday C', is in cell D5. The spreadsheet on whicih I wish to input this rota is called 'Scheduling Pots' and Amy Banks is located in cell D1588 and the cell in which I want to input 'Saturday C' is AQ1588. Do I use VLOOKUP to get this result and, if so, what would be the form of this function to return the above result? Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP for this problem?
Hi,
Move to the cell where you want the formula, and type =VLOOKUP( click on the cell were the employees name appears, A5?. Type a comma, and click on the sheet tab where the lookup table is, select all the data starting with the Name column on the left and including all the column upto the one you want to return, looks like D1:AQ2000 or something similar. Type a comman and then the numeric position of the column you want to return, then another comma and False) =VLOOKUP(A5,'Scheduling Pots'!D1:AQ2000,39,FALSE) If this helps, please click the Yes button. Cheers, Shane Devenshire "Zakynthos" wrote: I have an employee's name in cell A5 on a SS called Repair rotas.xls on the Plymouth Staff tab. The Saturday rota for this employee, called 'Saturday C', is in cell D5. The spreadsheet on whicih I wish to input this rota is called 'Scheduling Pots' and Amy Banks is located in cell D1588 and the cell in which I want to input 'Saturday C' is AQ1588. Do I use VLOOKUP to get this result and, if so, what would be the form of this function to return the above result? Many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP for this problem?
Shane,
Thanks so much for your help! No, it didn't work - I got a #N/A in the cell. The formula I used was something like (transferred it to OPen Office on my Mac but created in Excel on my PC, but I think the actual form corresponds with your example below): =VLOOKUP('REPAIR ROTAS.XLS'!'Plymouth Staff'.$A$5;C5:AQ2000;39;FALSE) Where have I gone wrong? "Shane Devenshire" wrote: Hi, Move to the cell where you want the formula, and type =VLOOKUP( click on the cell were the employees name appears, A5?. Type a comma, and click on the sheet tab where the lookup table is, select all the data starting with the Name column on the left and including all the column upto the one you want to return, looks like D1:AQ2000 or something similar. Type a comman and then the numeric position of the column you want to return, then another comma and False) =VLOOKUP(A5,'Scheduling Pots'!D1:AQ2000,39,FALSE) If this helps, please click the Yes button. Cheers, Shane Devenshire "Zakynthos" wrote: I have an employee's name in cell A5 on a SS called Repair rotas.xls on the Plymouth Staff tab. The Saturday rota for this employee, called 'Saturday C', is in cell D5. The spreadsheet on whicih I wish to input this rota is called 'Scheduling Pots' and Amy Banks is located in cell D1588 and the cell in which I want to input 'Saturday C' is AQ1588. Do I use VLOOKUP to get this result and, if so, what would be the form of this function to return the above result? Many thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP for this problem?
Hi,
I think I got the arrangement in reverse: However, if you just wanted to do this for one cell you would enter the followng formula in AQ1588 ='REPAIR ROTAS.XLS'!'Plymouth Staff'!$A$5 Easiest way to do that is to move to AQ1588 and type = and then click on then navigate to the Plymouth Staff sheet and click on cell A5. If you want a formula which is more general, that will look up a name and bring back the corresponding entry from column D, then in AQ1588 enter: =VLOOKUP(D1588,'[REPAIR ROTAS.XLS]Plymouth Staff'!A1:D100,4,TRUE) It is not clear if you have two workbooks open or one workbook with two sheets, if it is 2 workbooks than the above formula. If this helps, please click the Yes button Cheers, Shane Devenshire "Zakynthos" wrote: Shane, Thanks so much for your help! No, it didn't work - I got a #N/A in the cell. The formula I used was something like (transferred it to OPen Office on my Mac but created in Excel on my PC, but I think the actual form corresponds with your example below): =VLOOKUP('REPAIR ROTAS.XLS'!'Plymouth Staff'.$A$5;C5:AQ2000;39;FALSE) Where have I gone wrong? "Shane Devenshire" wrote: Hi, Move to the cell where you want the formula, and type =VLOOKUP( click on the cell were the employees name appears, A5?. Type a comma, and click on the sheet tab where the lookup table is, select all the data starting with the Name column on the left and including all the column upto the one you want to return, looks like D1:AQ2000 or something similar. Type a comman and then the numeric position of the column you want to return, then another comma and False) =VLOOKUP(A5,'Scheduling Pots'!D1:AQ2000,39,FALSE) If this helps, please click the Yes button. Cheers, Shane Devenshire "Zakynthos" wrote: I have an employee's name in cell A5 on a SS called Repair rotas.xls on the Plymouth Staff tab. The Saturday rota for this employee, called 'Saturday C', is in cell D5. The spreadsheet on whicih I wish to input this rota is called 'Scheduling Pots' and Amy Banks is located in cell D1588 and the cell in which I want to input 'Saturday C' is AQ1588. Do I use VLOOKUP to get this result and, if so, what would be the form of this function to return the above result? Many thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP for this problem?
Shane,
Thanks for this. Didn't work, still getting #N/A in the cell. Will summarise again: Repair Data.xls: Amy Banks in A5 Saturday Rota C in D5 Scheduling Pots.xls Amy Banks in A1588 VLOOKUP FORMULA (to return a Saturday rota C) in AQ1588 Sorry if I didn't make it clearer before - your continued help is much appreciated. "Shane Devenshire" wrote: Hi, I think I got the arrangement in reverse: However, if you just wanted to do this for one cell you would enter the followng formula in AQ1588 ='REPAIR ROTAS.XLS'!'Plymouth Staff'!$A$5 Easiest way to do that is to move to AQ1588 and type = and then click on then navigate to the Plymouth Staff sheet and click on cell A5. If you want a formula which is more general, that will look up a name and bring back the corresponding entry from column D, then in AQ1588 enter: =VLOOKUP(D1588,'[REPAIR ROTAS.XLS]Plymouth Staff'!A1:D100,4,TRUE) It is not clear if you have two workbooks open or one workbook with two sheets, if it is 2 workbooks than the above formula. If this helps, please click the Yes button Cheers, Shane Devenshire "Zakynthos" wrote: Shane, Thanks so much for your help! No, it didn't work - I got a #N/A in the cell. The formula I used was something like (transferred it to OPen Office on my Mac but created in Excel on my PC, but I think the actual form corresponds with your example below): =VLOOKUP('REPAIR ROTAS.XLS'!'Plymouth Staff'.$A$5;C5:AQ2000;39;FALSE) Where have I gone wrong? "Shane Devenshire" wrote: Hi, Move to the cell where you want the formula, and type =VLOOKUP( click on the cell were the employees name appears, A5?. Type a comma, and click on the sheet tab where the lookup table is, select all the data starting with the Name column on the left and including all the column upto the one you want to return, looks like D1:AQ2000 or something similar. Type a comman and then the numeric position of the column you want to return, then another comma and False) =VLOOKUP(A5,'Scheduling Pots'!D1:AQ2000,39,FALSE) If this helps, please click the Yes button. Cheers, Shane Devenshire "Zakynthos" wrote: I have an employee's name in cell A5 on a SS called Repair rotas.xls on the Plymouth Staff tab. The Saturday rota for this employee, called 'Saturday C', is in cell D5. The spreadsheet on whicih I wish to input this rota is called 'Scheduling Pots' and Amy Banks is located in cell D1588 and the cell in which I want to input 'Saturday C' is AQ1588. Do I use VLOOKUP to get this result and, if so, what would be the form of this function to return the above result? Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup problem | Excel Worksheet Functions | |||
problem with vlookup | Excel Worksheet Functions | |||
VLOOKUP and IF AND problem | Excel Discussion (Misc queries) | |||
VLOOKUP problem | Excel Worksheet Functions | |||
VLOOKUP problem using VBA | Excel Discussion (Misc queries) |