Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default Trying to lookup values on another sheet

Hi i have a spreadsheet with multiple workbooks.
on one workbook data is entered using lists. (the data for the Lists is on a
workbook called "lists")
when data is selected from the list i need the row above to display a value
based on the list choice.
eg if i select "chc" from the list, then the cell above displays
"06:00:14:30" if "CW" was selected from the drop down list then the cell
above displays "06:00 to 14:00"
this data is on a workbook called "Times"

CHC 06:00 to 14:30 8
CW 06:00 to 14:00 6

the formula i am using is

=IF(ISERROR(MATCH(C8,Times!$A$2:$A$21,0)),"",LOOKU P(C8,Times!$B$2:$B$21))

this seems to select the first time range only and does not seem to
reference all the data, or update if you change the list choice.

i have tried changing the sort of the "lookup" range but that does not seem
to help

hope someone can help.



  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Trying to lookup values on another sheet

Try the below instead

=IF(ISERROR(MATCH(C8,Times!$A$2:$A$21,0)),"",
VLOOKUP(C8,Times!$A$2:$B$21,2,0))

If this post helps click Yes
---------------
Jacob Skaria


"David Ryan" wrote:

Hi i have a spreadsheet with multiple workbooks.
on one workbook data is entered using lists. (the data for the Lists is on a
workbook called "lists")
when data is selected from the list i need the row above to display a value
based on the list choice.
eg if i select "chc" from the list, then the cell above displays
"06:00:14:30" if "CW" was selected from the drop down list then the cell
above displays "06:00 to 14:00"
this data is on a workbook called "Times"

CHC 06:00 to 14:30 8
CW 06:00 to 14:00 6

the formula i am using is

=IF(ISERROR(MATCH(C8,Times!$A$2:$A$21,0)),"",LOOKU P(C8,Times!$B$2:$B$21))

this seems to select the first time range only and does not seem to
reference all the data, or update if you change the list choice.

i have tried changing the sort of the "lookup" range but that does not seem
to help

hope someone can help.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default Trying to lookup values on another sheet

Hi Jacob worked a treat. Thanks

"Jacob Skaria" wrote:

Try the below instead

=IF(ISERROR(MATCH(C8,Times!$A$2:$A$21,0)),"",
VLOOKUP(C8,Times!$A$2:$B$21,2,0))

If this post helps click Yes
---------------
Jacob Skaria


"David Ryan" wrote:

Hi i have a spreadsheet with multiple workbooks.
on one workbook data is entered using lists. (the data for the Lists is on a
workbook called "lists")
when data is selected from the list i need the row above to display a value
based on the list choice.
eg if i select "chc" from the list, then the cell above displays
"06:00:14:30" if "CW" was selected from the drop down list then the cell
above displays "06:00 to 14:00"
this data is on a workbook called "Times"

CHC 06:00 to 14:30 8
CW 06:00 to 14:00 6

the formula i am using is

=IF(ISERROR(MATCH(C8,Times!$A$2:$A$21,0)),"",LOOKU P(C8,Times!$B$2:$B$21))

this seems to select the first time range only and does not seem to
reference all the data, or update if you change the list choice.

i have tried changing the sort of the "lookup" range but that does not seem
to help

hope someone can help.



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
i wish to lookup values in column A, & add adjacent values in colu Browny Excel Discussion (Misc queries) 4 September 26th 08 05:00 PM
lookup single value in one sheet, return multiple results from theother sheet Chuck[_3_] Excel Worksheet Functions 1 April 4th 08 06:17 AM
copy values generated by conditional formula in one sheet to the other work sheet as values ramana Excel Worksheet Functions 1 October 5th 05 01:04 PM
copy values generated by conditional formula in one sheet to the other work sheet as values bobby Excel Discussion (Misc queries) 1 October 5th 05 12:18 PM
Lookup values in multipul sheets and show value in another sheet Kim Excel Worksheet Functions 3 June 17th 05 01:56 PM


All times are GMT +1. The time now is 02:43 PM.

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"