Validate Sku No in Sheet 1 against Sheet 2

[jerminski73]

I have a master Inventory file with a full item list (sheet1)which has 3600
SKUs by item then color.
I am creating a cheat sheet (sheet2) which has 300 SKUs setup by item only
that I want to update the pricing from the master file.
Currently the cheat sheet points to the cell that has the pricing for 1 of
the item I want to use. The issue I have is some of the line numbers getting
deleted in the master file which causes sheet2 to grab the next item in line
causing MASS CONFUSION.
I have now included a SKU column in sheet2 which I want to tie to the SKU
number in the master file.
Sorry if this is so confusing....
I just want the SKU number of sheet2 to validate using the SKU number of
sheet1 before grabbing the pricing column.

 

[John Bundy]

Sounds like a vlookup will do this just fine, have you tried it? If not look
into it and post back any questions.

 

[jerminski73]

Hi John,

I was actually working with the VLOOKUP function but am unable to make it
work.
This is the function I have defined...
=VLOOKUP("a10",[RShopinmas.xls]hopinmas!$A$2:$S$4500,19,FALSE)

a10 is the cell with the SKU on my cheat sheet.
[RShopinmas.xls]hopinmas!$A$2:$S$4500 is my Inventory master file with
column A being SKU number to match to and column S being the price column I
want to pull pricing from.
19 is in column S
False because I want exact lookups.

Anything obviously wrong?

 

[jerminski73]

I have tried using the master file both as its own workbook and as a new
sheet in the same workbook as my cheat sheet. I keep getting #N/A as my
result.
This is the function I have defined...
=VLOOKUP(a10,[RShopinmas.xls]hopinmas!$A$2:$S$4500,19,FALSE) seperate workbook
=VLOOKUP(a10,hopinmas!$A$2:$S$4500,19,FALSE)same workbook

a10 is the cell with the SKU on my cheat sheet.
hopinmas!$A$2:$S$4500 is my Inventory master file with column A being SKU
number to match to and column S being the price column I
want to pull pricing from.
19 is in column S
False because I want exact lookups.

Anything obviously wrong?

 

[vezerid]

Following your descriptions, you seem to be using it correctly. Hence,
if you get #N/A it means that the value of A10 is not found in
hopinmas!A:A. Possible reasons:
1. A10 contains a number and the values in hopinmas!A:A *appear* to be
numbers but are actually text, or vice versa. Use the functions
ISTEXT() and ISNUMBER() to see if either is text.
2. If your sku's have dots, or things that might make them look like
decimal numbers, then maybe you have a rounding issue.

In general, try the following formula in a blank cell

=A10=hopinmas!Ax

where you manually find the row x where the same sku value lies as in
A10. If you get FALSE you know something is wrong.
Depending on the reason of discrepancy different things can be
suggested.

HTH
Kostis Vezerides

 

[jerminski73]

THIS IS EXACTLY WHAT I FOUND OUT!!!

A10 contains a number and the values in hopinmas!A:A *appear* to be
numbers but are actually text, or vice versa.

Is there somewhere I can find a list of functions and what they are supposed
to do? It is semi foreign when I look at the function listing in the
"insert" menu

Thanks

 

[jerminski73]

Problem solved on the #N/A issue, now the field is blank, A blank field
presents problems for formulas on other sheets. Is there a way to use this
same function and have it enter "0" if item not found?

=IF(ISNA(VLOOKUP(TEXT(A43,"000000"),Mas!$A$2:$S$5000,19,FALSE)),"",VLOOKUP(TEXT(A43,"000000"),Mas!$A$2:$S$5000,19,FALSE))

 

[T. Valko]

In your formula, replace the "" with 0.