Software

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.

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

This question was auto closed due to inactivity