Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 5th 05, 07:16 AM
Clay
 
Posts: n/a
Default need check two worksheets to lookup a value

Help I cannot figure out how to check two worksheets to
return a value. The lookup is for part number price and
description workbook. Excel dose not have enough rows for
the data so I had to split it. My customers do not have
access they do have excel. Some numbers start with
letters (letters sheet) the rest numbers (numbers sheet).
The part number is in the A column in both. I tried
vlookup it works great to bring back one or the other.
This leaves two cells one with data one #n/a. I cannot
get both together I tried : + & , = Excel fixed it once
when I used the : but it was invalad. I am not opposed to
using a dummy cell or two to change things. I have been
working that angle for the past few hours. I get the #n/a
whitch seems to kill many of the lookup functions. I
converted it to iserror that got me T F but you cannot
seem to get T F to refreance the cell that has the data.
I tried to refreance the data from access but it keeps
trying to bring it all in (makes for a break till cancel
kicks in) I saw a couple of posts for similar things but
they do not look close enough. There must be a simple way
to do this.

Thank you,
Clay

  #2   Report Post  
Old January 5th 05, 08:31 AM
Frank Kabel
 
Posts: n/a
Default

Hi
not really sure what formulas you have used. Please post your working and
non-working formulas together with some example data and your expected
results

--
Regards
Frank Kabel
Frankfurt, Germany

Clay wrote:
Help I cannot figure out how to check two worksheets to
return a value. The lookup is for part number price and
description workbook. Excel dose not have enough rows for
the data so I had to split it. My customers do not have
access they do have excel. Some numbers start with
letters (letters sheet) the rest numbers (numbers sheet).
The part number is in the A column in both. I tried
vlookup it works great to bring back one or the other.
This leaves two cells one with data one #n/a. I cannot
get both together I tried : + & , = Excel fixed it once
when I used the : but it was invalad. I am not opposed to
using a dummy cell or two to change things. I have been
working that angle for the past few hours. I get the #n/a
whitch seems to kill many of the lookup functions. I
converted it to iserror that got me T F but you cannot
seem to get T F to refreance the cell that has the data.
I tried to refreance the data from access but it keeps
trying to bring it all in (makes for a break till cancel
kicks in) I saw a couple of posts for similar things but
they do not look close enough. There must be a simple way
to do this.

Thank you,
Clay



  #3   Report Post  
Old January 5th 05, 08:35 AM
Mike H
 
Posts: n/a
Default

On Tue, 4 Jan 2005 23:16:52 -0800, Clay wrote:

Help I cannot figure out how to check two worksheets to
return a value. The lookup is for part number price and
description workbook. Excel dose not have enough rows for
the data so I had to split it. My customers do not have
access they do have excel. Some numbers start with
letters (letters sheet) the rest numbers (numbers sheet).
The part number is in the A column in both. I tried
vlookup it works great to bring back one or the other.
This leaves two cells one with data one #n/a. I cannot
get both together I tried : + & , = Excel fixed it once
when I used the : but it was invalad. I am not opposed to
using a dummy cell or two to change things. I have been
working that angle for the past few hours. I get the #n/a
whitch seems to kill many of the lookup functions. I
converted it to iserror that got me T F but you cannot
seem to get T F to refreance the cell that has the data.
I tried to refreance the data from access but it keeps
trying to bring it all in (makes for a break till cancel
kicks in) I saw a couple of posts for similar things but
they do not look close enough. There must be a simple way
to do this.

Thank you,
Clay


Hi Clay. I found that I wasn't entirely clear on your meaning, but I
think I understand your problem well enough to take a shot at it.

It sound as if you have a source worksheet that contains a part number
in some column. You then want to use that part number to look up more
information in one of two other worksheets. Which sheet you reference
depends on whether the part number is a number or converts to a number,
or if it is text or alpha-numeric. Your problem is making that
distinction.

IF I have that right, here's one possibility:

A1 your part number
B1 =IF(ISBLANK(A1),NA(),VALUE(A1))
(this is to see if the part number can be converted to a number. If
there's no part number at all, return #N/A)
C1 =ISERROR(B1)
(if it couldn't make a number from A1, you'll get TRUE here)
D1 =IF(C1,ERROR.TYPE(B1),0)
(if there was no error, it was a number. if the error was #VALUE, it was
an alpha-numeric)
E1 =IF(D1=0,
"'[part_number_file_name]number_sheet_name'!range",
IF(D1=3,"'[part_number_file_name]letter_sheet_name'!range",NA()))
(creates a reference that looks like [file]sheet!range. Fill in your own
values here)
F1 =IF(ISNA(E1),NA(),VLOOKUP(A1,INDIRECT(E1,TRUE),col _index_num,FALSE))
(Do the actual lookup)

A lot of that can be combined in a single cell, but I spread it out
hopefully to make it more clear. You could also do more or less error
checking, depending on your needs. Also, you may need to modify cell E1
to reflect how you've stored your information.

The use of the VALUE function in B1 could be dispensed with and maybe
you could use ISNUMBER. I didn't know how you might have A1 formatted.
It's possible you use text in order to preserver leading zeros, etc.

Finally, if you have sheets are large as you say, where you've used the
entire row capacity of Excel, this may be horribly slow. You could make
some assumptions and avoid some of the IF tests. You could also avoid
the use of INDIRECT and instead hard-code your sheet names there, and
maybe use lookup instead of vlookup.

Hopefully, this will give you some ideas.
--
Mike H


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
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 10:05 PM
Using a Check Box as a control item Dan G Excel Discussion (Misc queries) 2 December 14th 04 07:59 PM
data entry on multiple worksheets diosdias Excel Discussion (Misc queries) 1 December 7th 04 05:33 PM
Why does spelling check close Excel when checking spanish? RCP Excel Discussion (Misc queries) 2 December 4th 04 07:37 PM
Assigning Cells in worksheets to other data in other worksheets. David McRitchie Excel Discussion (Misc queries) 0 November 27th 04 06:15 PM


All times are GMT +1. The time now is 11:12 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017