I've tried that, and it just brings in a bunch of data from the database, not
just a record based on a number I type in. I need a way to type in a part
number in Excel and have it refer out to the database to look up description,
list price, and my cost.
"Frank Kabel" wrote:
Hi
Access <- Excel. In Excel you can use 'Data - Import External Data' for
this
--
Regards
Frank Kabel
Frankfurt, Germany
Chris Kellock wrote:
It appears there were some spaces at the end of some part numbers. I
thought I had stripped them out, but there was still a number of them
with spaces. Fixed that now. I'd love to just dump all my price
lists into Access and then have one Excel sheet that looked up any
part number, instead of making separate Excel sheets for each
vendor's part number lookups. But I don't know how to make Access
and Excel talk...
"Frank Kabel" wrote:
Hi
the only reason for this could be spaces or invisible characters in
your lookup range (or the lookup value cell A4). Try checking the
lookup value and the expected match manually. e.g. with
=$A4='Lookup - Siemens1'!A10
if you would to expect cell A10 on the lookup sheet to match with
your lookup value. If this returns FALSE then you could start
checking it character by character. e.g. try the following formula:
=IF(MID($A$4,ROW(1:1),1)=MID('Lookup -
Siemens1'!$A$10,ROW(1:1),1),"Character: " & ROW(1:1) & "
matches","Character: " & ROW(1:1) & " does NOT match")
and copy this formula down
--
Regards
Frank Kabel
Frankfurt, Germany
Chris Kellock wrote:
=IF($A4="","",VLOOKUP($A4,'Lookup - Siemens1'!Siemens,2,FALSE))
Lookup-Siemens1 is the name of the workbook (made from a template
called Lookup - Siemens.
Range name Siemens covers 4 coumns in a second sheet in this
workbook.
Part number 3SB3400-0A works but 3SB3400-0B (next one in list, both
are around 7800 parts down into the list) won't look up. 54161 will
look up (near top of list), 6ED1052-1FB00-0BA4 will look up, but
MSE6 (almost at bottom of list) will not.
"Frank Kabel" wrote:
Hi
typical cause would be that either you're searching for a numeric
but the lookup list is stored as 'Text' or vice versa. You may post
your exact formula and the values which do not work
--
Regards
Frank Kabel
Frankfurt, Germany
Chris Kellock wrote:
I have made several worksheets for lookup up pricing for our
various product lines. These sheets look up a description, list
price, and my cost in another worksheet (in the same XLS file)
based on the part number I enter on the first sheet. The list on
the second page has been assigned a name, and I'm using VLOOKUP
to find the part number in that range, and then return the values
into my original sheet. The list is sorted alphabetically by the
part number. I can send the file is someone can help!
My problem is that if the lookup list has over about 5,000 items,
the results of the VLOOKUP are intermittent. I can actually copy
and paste a value from the list into my sheet and it won't look
up. Some of my price lists are over 15,000 items.
Does anyone have any experience with VLOOKUP not working in a
large list? How about any ideas of how to make it work more
reliably. I have one sheet I'm trying to make that I expect to
have about 50,000 items in it. I desperately need to get this
worksheet working. Please help!
|