help with updating spreadsheet

**JAZZNAURA** · 08-01-2006, 04:15 PM

hi all,

I’m new to excel and learning as i go. i need help, I’m trying to make my work a bit easier. i want to keep a historical record of products and there locations at work (work in a warehouse by the way), I’ve managed to create a worksheet (see below, worksheet 1) that uses vlookup (=VLOOKUP(Sheet1!A:A,Sheet2!A:B,2, FALSE)) to get a list of about 4000 products and there locations from worksheet 2, worksheet 2 is updated daily by importing a text file.

what i can't figure out is how to update work sheet 1 using a formula/macro, one that updates the location of what’s already on the list (list is column A) in the next empty "location" column (in this case column C) and if that product number is no longer on the list on sheet 2, leaves the cell under column C blank or ******. Also, adds any new product numbers at the bottom of the list in column A (no duplicates) and the location in the next free "location" column (in this case column C), but leaves all data already in the worksheet (historical record).
I’m happy to paste formulas on a daily bases to the next free column if it is difficult.

I hope I explained what I’m after clearly; any help anyone can give me would be gratefully received.

Please remember I’m new to this and may need it explained as simple as possible

Thanks again

TPN LOCATION LOCATION LOCATION LOCATION LOCATION
1843 BA010
6947 BX080
11838 BG780
12618 BI129
13679 BW610
13698 BH260
21786 BR900
21807 BR699
21813 CA520
21826 CA719
21859 BT699
21871 CZ450

Glen · 08-01-2006, 05:10 PM

Hello,

I don't have time to do the whole thing, but here are some thoughts...

> what i can't figure out is how to update work sheet 1 using a
> formula/macro, one that updates the location of whatâ€™s already on the
> list (list is column A) in the next empty "location" column (in this
> case column C) and if that product number is no longer on the list on
> sheet 2, leaves the cell under column C blank or ******.

My suggestion would be to insert the new days data into column B each time,
with older data moving to the right. This makes building the macro much
easier as it doesn't need to work out which column is the next blank column -
just use the macro recording tool to record inserting a new column and
copying the formula in from column C (was column B). Then copy column C and
use paste special/values to remove formula but retain data, so vlookup
doesn't continue to update this column with new values, then update text file
data in sheet2.

Keep in mind that at some stage you will need to archive old data as it will
only take 256 work days before you run out of columns.

Rather than having an N/A error returned when the vlookup can't find the
right product in sheet 2, use an if statement with ISERROR formula to check
for discontinued items. Something like...

=IF(ISERROR(VLOOKUP(Sheet1!A:A,Sheet2!A:B,2,
FALSE)),"******",VLOOKUP(Sheet1!A:A,Sheet2!A:B,2, FALSE))

>Also, adds
> any new product numbers at the bottom of the list in column A (no
> duplicates) and the location in the next free "location" column (in
> this case column C), but leaves all data already in the worksheet
> (historical record).

To find new records in sheet2, I would do a vlookup the other way in sheet2,
and filter for N/A errors, where vlookup can't find a match for that part
with the table in sheet1. These values could then be copied to the bottom of
sheet 1.

> Iâ€™m happy to paste formulas on a daily bases to the next free column if
> it is difficult.

To be honest, this would be way easier in Access and you wouldn't have to
worry about running out of columns. If you can get your employer to spring
for introductory Access course, it's not that difficult after that. If you
figured out vlookup-ing yourself, you should be able to pick up Access OK.

> Please remember Iâ€™m new to this and may need it explained as simple as
> possible

You had a lot of questions in your post. You might get more replies if you
broke it down into several posts.

Cheers,

Glen