General discussion

Locked

Excel97: Find data in an independant she

By ToerVla ·
Okay, before I start explaining, and you start thinking the same as me : I know this would be a lot easier if I could just use Access for this... Unfortunately, the user does not have Access installed, and doesn't want to either. So I need to find away to do this in Excel.

There is an Excel file which contains a list of products, and its specifications (product nr, product name, price a piece, ...). There are several (1 per client) excel files which contain 1 or more invoices. In column A he would enter a product nr, and then the user wants the other specifications (name, price) to appear in column B and C.

I truly would appreciate if someone could help me out on this one...

Sabrina

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel97: Find data in an independant she

by steven.green In reply to Excel97: Find data in an ...

You will need to use look-up tables. Most Excel manuals will cover this, but you will need to bring together all the product nr, name, price etc.. into one spreadsheet (perhaps just create a combination one). You can then create a look-up table that will auto-fill your table when the product number is put in.

If you are unsure how to do this, send me an e-mail and I can send you a variety of manuals and sites that will explain how to do this.

Hope this is what you are after.

Collapse -

Excel97: Find data in an independant she

by ToerVla In reply to Excel97: Find data in an ...

The question was auto-closed by TechRepublic

Collapse -

Excel97: Find data in an independant she

by ljubisab In reply to Excel97: Find data in an ...

Suppose your sheet is named "Invoices".

Add another sheet "Products" that contains product numbers, names and prices (3 columns - A, B and C), beginning from 2nd row (first row is a header).

Define a dynamic range name "AllProd" as
=OFFSET(Products!$A$2;0;0;COUNTA(Products!$A:$A)-1;3)

In sheet "Invoices", if cell A2 contains product number, enter in cell B2 =VLOOKUP(A2;AllProd;2;FALSE)
and in cell C2 =VLOOKUP(A2;AllProd;3;FALSE)
etc. Then copy these formulas down the rows.

Unfortunately, this solution works ONLY if you have a sheet "Products" in every Excel file with invoices, which is ok if products are not changed frequently. Or maybe you can somehow automate these changes.

If you don't want "FALSE" to appear when there is no product number in column A, you can put something like this in cell B2 =IF(A2<>"";VLOOKUP(A2;AllProd;3;FALSE);"") and similar in cell C2.

Dynamic range name is important so you don't change a range that name refers to, every time a new row of product data is

Collapse -

Excel97: Find data in an independant she

by ToerVla In reply to Excel97: Find data in an ...

The question was auto-closed by TechRepublic

Collapse -

Excel97: Find data in an independant she

by ToerVla In reply to Excel97: Find data in an ...

This question was auto closed due to inactivity

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums