Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 115
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 115
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 115
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup problem Robbyn Excel Worksheet Functions 10 August 6th 07 02:49 AM
problem with vlookup ruchie Excel Worksheet Functions 7 June 8th 07 07:11 PM
VLOOKUP and IF AND problem excelnewbie44 Excel Discussion (Misc queries) 10 October 4th 06 04:31 PM
VLOOKUP problem fastballfreddy Excel Worksheet Functions 3 May 16th 06 11:53 AM
VLOOKUP problem using VBA matpj Excel Discussion (Misc queries) 4 April 28th 06 12:07 PM


All times are GMT +1. The time now is 09:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"