Lookup Value in an External Spreadsheet

[The Moose]

I have a several small spreadsheets that have two different columns
that change periodically -- sometimes every two weeks, sometimes once a
week.

Question One:
---------------------
In small spreadsheet "A", I want to be able to lookup the part-number
in the master spreadsheet and pull out two values -- the price and the
availability??

I tried VLOOKUP with a defined-name for the external data this morning
and it worked except on a couple of values. I modified the master
spreadsheet and now I can't get it to work again, it keeps pulling from
the wrong row (not matching the part-number).

Question Two:
---------------------
Also, sometimes the part-number in the master spreadsheet is no longer
available -- it's either conditionally-highlighted in orange in the
master spreadsheet or has the word "closeout" in the title or is not in
the spreadsheet at all. If it's in the master at all, I can just pull
availability=zero and that takes care of it. But, what do I do if it's
no longer in the master spreadsheet??

Thanks.

Barb

 

[The Moose]

Well, I finally got most of this thing to work.

The formula to lookup the quantity-available is as follows:
=VLOOKUP(C2,'[uploaded-to-store-09-29-2006.xls]products-09-08-2006'!C:E,3,FALSE)
Notes:
---------
C2 is the part-number in the small spreadsheet.
C is the column that contains the part-number in the master
spreadsheet.
E is the column that contains the quantity-available in the master
spreadsheet.
and 3 is the column number that contains the value in the master
spreadsheet that I want to pull (AKA the quantity that is available).
You count the column number in the master spreadsheet starting with the
column that you are using for both spreadsheets to do the vlookup. All
of the values that you are looking for have to be to the RIGHT of the
vlookup column.

The formula to lookup the current-price is as follows:
=VLOOKUP(C2,'[uploaded-to-store-09-29-2006.xls]products-09-08-2006'!C:J,8,FALSE)
Notes:
---------
C2 is the part-number in the small spreadsheet.
C is the column that contains the part-number in the master
spreadsheet.
J is the column that contains the current-price in the master
spreadsheet.
and 8 is the column number that contains the current-price in the
master spreadsheet that I want to pull. You count the column number in
the master spreadsheet starting with the column that you are using for
both spreadsheets to do the vlookup. All of the values that you are
looking for have to be to the RIGHT of the vlookup column.

I haven't yet seen what happens when I run across a product that is no
longer in the master spreadsheet. I'm gonna be pulling all these
little spreadsheets into one master spreadsheet.

The one last problem that I had was this info HAS TO BE in TAB format.
You lose all your formulas when you write the TAB file. So, you need
to have a master file with all the formulas that gets saved as a TAB
file when you run an availability and price update.

More later. Thanks.

Barb

 

[The Moose]

Well, I guess I've automated this as much as I can.

The one thing I wanted to keep track of (be aware of) was closeout
merchandise. So, this is what i'm doing:

I put a list of words on another worksheet and give that list a
range-name = mylist:
closeout
CLOSEOUT
closeout!
CLOSEOUT!

Then I apply a conditional format (using color -- peach) to the entire
spreadsheet with the merchandise on it:
=SUM(COUNTIF($B1,"*"&closeout&"*"))
(That highlights all the rows that have the word "closeout" in the
title with a lovely peach color; then I delete those rows.)

That's it -- it works. It's as automated as i can get it.

Barb

PS: Oh, the one thing that didn't mention is that for the product that
is BACKORDERED, I put that word in the product title and set the
amount-available to zero. I can either upload those as-is or I can
manually set them to not-available on my store. Either way, the
zero-availability or BACKORDERED in the title will let the customer
know they cannot order that item yet.

Well, I finally got most of this thing to work.

The formula to lookup the quantity-available is as follows:
=VLOOKUP(C2,'[uploaded-to-store-09-29-2006.xls]products-09-08-2006'!C:E,3,FALSE)
Notes:
---------
C2 is the part-number in the small spreadsheet.
C is the column that contains the part-number in the master
spreadsheet.
E is the column that contains the quantity-available in the master
spreadsheet.
and 3 is the column number that contains the value in the master
spreadsheet that I want to pull (AKA the quantity that is available).
You count the column number in the master spreadsheet starting with the
column that you are using for both spreadsheets to do the vlookup. All
of the values that you are looking for have to be to the RIGHT of the
vlookup column.

The formula to lookup the current-price is as follows:
=VLOOKUP(C2,'[uploaded-to-store-09-29-2006.xls]products-09-08-2006'!C:J,8,FALSE)
Notes:
---------
C2 is the part-number in the small spreadsheet.
C is the column that contains the part-number in the master
spreadsheet.
J is the column that contains the current-price in the master
spreadsheet.
and 8 is the column number that contains the current-price in the
master spreadsheet that I want to pull. You count the column number in
the master spreadsheet starting with the column that you are using for
both spreadsheets to do the vlookup. All of the values that you are
looking for have to be to the RIGHT of the vlookup column.

I haven't yet seen what happens when I run across a product that is no
longer in the master spreadsheet. I'm gonna be pulling all these
little spreadsheets into one master spreadsheet.

The one last problem that I had was this info HAS TO BE in TAB format.
You lose all your formulas when you write the TAB file. So, you need
to have a master file with all the formulas that gets saved as a TAB
file when you run an availability and price update.

More later. Thanks.

Barb

I have a several small spreadsheets that have two different columns
that change periodically -- sometimes every two weeks, sometimes once a
week.

Question One:
---------------------
In small spreadsheet "A", I want to be able to lookup the part-number
in the master spreadsheet and pull out two values -- the price and the
availability??

I tried VLOOKUP with a defined-name for the external data this morning
and it worked except on a couple of values. I modified the master
spreadsheet and now I can't get it to work again, it keeps pulling from
the wrong row (not matching the part-number).

Question Two:
---------------------
Also, sometimes the part-number in the master spreadsheet is no longer
available -- it's either conditionally-highlighted in orange in the
master spreadsheet or has the word "closeout" in the title or is not in
the spreadsheet at all. If it's in the master at all, I can just pull
availability=zero and that takes care of it. But, what do I do if it's
no longer in the master spreadsheet??

Thanks.

Barb