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

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

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