VBA

J

jennifer

I have never used VBA, but was told I would need to in order to accomplish the following

Have 3 worksheets, sheet 1 and 2 are production schedules for two facilities

Sheet 3 is the data the would be used in the first two sheets. The data consists of ingredients needed to produce a case of product, associated product number and product name. I would like to be able to enter a product code number in either sheet 1 or 2 and the product name and ingredients associated with that code number automatically fill in and multiply by the number of cases of product needed. The product data on sheet three extends from column 'C' to 'CN', ingredients extend from b8-b372. I tried to use the "if" function but discovered it would only take up to 7 nested functions (the first 7 worked, but after that I was at a loss.
 
T

Trevor Shuttleworth

Jennifer

you don't need VBA for this. Have a look at the Help for VLOOKUP or search
the Google Archives for some examples.

An example:

=VLOOKUP(D3,Sheet3!A:D,2,FALSE)

Regards

Trevor


jennifer said:
I have never used VBA, but was told I would need to in order to accomplish the following:

Have 3 worksheets, sheet 1 and 2 are production schedules for two facilities.

Sheet 3 is the data the would be used in the first two sheets. The data
Click to expand...
consists of ingredients needed to produce a case of product, associated
product number and product name. I would like to be able to enter a product
code number in either sheet 1 or 2 and the product name and ingredients
associated with that code number automatically fill in and multiply by the
number of cases of product needed. The product data on sheet three extends
from column 'C' to 'CN', ingredients extend from b8-b372. I tried to use
the "if" function but discovered it would only take up to 7 nested functions
(the first 7 worked, but after that I was at a loss.
 
F

Frank Kabel

Hi
use VLOOKUP for this. See:
http://www.mvps.org/dmcritchie/excel/vlookup.htm

--
Regards
Frank Kabel
Frankfurt, Germany

jennifer said:
I have never used VBA, but was told I would need to in order to accomplish the following:

Have 3 worksheets, sheet 1 and 2 are production schedules for two facilities.

Sheet 3 is the data the would be used in the first two sheets. The
Click to expand...
data consists of ingredients needed to produce a case of product,
associated product number and product name. I would like to be able to
enter a product code number in either sheet 1 or 2 and the product name
and ingredients associated with that code number automatically fill in
and multiply by the number of cases of product needed. The product
data on sheet three extends from column 'C' to 'CN', ingredients extend
from b8-b372. I tried to use the "if" function but discovered it would
only take up to 7 nested functions (the first 7 worked, but after that
I was at a loss.
 
B

Bob Phillips

Jennifer,

VBA is not necessary, Lookup functions will do it.

What exactly are product cases, and what do you mean by '...multiply by the
number of cases of product needed'? And the difference between sheet 1 and
2?

Can you give an example of data and expected results?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

jennifer said:
I have never used VBA, but was told I would need to in order to accomplish the following:

Have 3 worksheets, sheet 1 and 2 are production schedules for two facilities.

Sheet 3 is the data the would be used in the first two sheets. The data
Click to expand...
consists of ingredients needed to produce a case of product, associated
product number and product name. I would like to be able to enter a product
code number in either sheet 1 or 2 and the product name and ingredients
associated with that code number automatically fill in and multiply by the
number of cases of product needed. The product data on sheet three extends
from column 'C' to 'CN', ingredients extend from b8-b372. I tried to use
the "if" function but discovered it would only take up to 7 nested functions
(the first 7 worked, but after that I was at a loss.
 
S

steveB

Jennifer,

There are many ways to accomplish this - with or without VBA

Let's look at how to do this with worksheet formulas:

You say that the product data is on Sheet 3, columns C to CN, and the
ingrediants range from B8 to B372.

You can use "Lookup()", Index(), or SumIf to extract the data from Sheet 3
to sheets 1 and/or 2. (I am not sure how your data fits together, but...)

I would need to know what columns are what:

But the SumIf function looks at a column to find your criteria (product ID)
and than sums each instance of this criteria in the lookup column.

=SumIf(Sheet3!A:A,$A$1,Sheet3!B:B)

You can add a multiplier:
=SumIf(....)*$B$1

This presumes that A1 is the product ID and B1 is the number of cases input
on sheets 1 or 2. Than adjust the formula for each cell. Also replace
Sheet3 with the name of sheet 3.

hth
--

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


jennifer said:
I have never used VBA, but was told I would need to in order to accomplish the following:

Have 3 worksheets, sheet 1 and 2 are production schedules for two facilities.

Sheet 3 is the data the would be used in the first two sheets. The data
Click to expand...
consists of ingredients needed to produce a case of product, associated
product number and product name. I would like to be able to enter a product
code number in either sheet 1 or 2 and the product name and ingredients
associated with that code number automatically fill in and multiply by the
number of cases of product needed. The product data on sheet three extends
from column 'C' to 'CN', ingredients extend from b8-b372. I tried to use
the "if" function but discovered it would only take up to 7 nested functions
(the first 7 worked, but after that I was at a loss.
 
Top