Hi all,

I am new here and found this forum because, as most persons here I
guess, I need some help in Excel.
I have been building a worksheet for my job information but get stuck.

What is the problem? I'll try and explain:
I am building an overview of ingredients of petfood. It should finally
result in a sheet in which you can fill in a value and the sheet says
it is ok or not.

In one sheet I have therefore lined out a couple of sorts petfoods
(junior, adult, and so on) with the values of the ingredients it would
contain in an ideal situation (for instance, vitamines of the standard
food with a minimum, maximum and mean value). I hope this part is
clear, please ask if it's not.

In the second sheet, the values which I find in a batch are filled in.
It shows as following:
date-batchcode-productcode-productname-and then the values I have found
for the ingredients followed by a correct / incorrect
It contains multiple functions.
The first function is the function that allows you to only insert the
productcode and excel will automatically fill in the productname. I
created this function for it:

=IF(C4="010";"product A";IF(C4="015";"product B";IF(C4="020";"product
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"product
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))

Uptill the moment of product G it works fine, but then I get product H,
which is the 8th product and therefore excel can not put it in one line
anymore (as excel can only include 7 IF functions) so it automatically
gives an INCORRECT.

First question is therefo Is there a way to change this so I can
include more then 7 products in the function line?


Then, I'll continue, If this is possible: How can I recreate this to an
automatic function which automatically takes the values of sheet 1 for
that specific product into account?
So when I insert a date, batchcode, a productcode (at this moment excel
should automatically insert the productname) and then insert a value
which I found for that batch, excel should automatically compare this
to that specific value in sheet 1...

I hope you know what I mean and furthermore I of course hope you can
help me because I almost gave up on this already....

Thanks!

CdFMarshall
I need a worksheet which contains in one sheet stats that can be
considered normal.


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile: http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392

You need the VLOOKUP() function. With Col A & B set up to hold the codes &
prod desc, like so, starting in row 2

010 prodcut A
015 product B
020 product C

and so on, you'd use the formula

=VLOOKUP(C4,$a$2:$b$10,2,false)

This would return an error if the value in C4 didn't appear in column a.
Get around that by using this variation

=IF(ISNA(VLOOKUP(C4,$a$2:$b$10,2,false)),"Incorrec t",
VLOOKUP(C4,$a$2:$b$10,2,false))


"CdFMarshall" wrote:


Hi all,

I am new here and found this forum because, as most persons here I
guess, I need some help in Excel.
I have been building a worksheet for my job information but get stuck.

What is the problem? I'll try and explain:
I am building an overview of ingredients of petfood. It should finally
result in a sheet in which you can fill in a value and the sheet says
it is ok or not.

In one sheet I have therefore lined out a couple of sorts petfoods
(junior, adult, and so on) with the values of the ingredients it would
contain in an ideal situation (for instance, vitamines of the standard
food with a minimum, maximum and mean value). I hope this part is
clear, please ask if it's not.

In the second sheet, the values which I find in a batch are filled in.
It shows as following:
date-batchcode-productcode-productname-and then the values I have found
for the ingredients followed by a correct / incorrect
It contains multiple functions.
The first function is the function that allows you to only insert the
productcode and excel will automatically fill in the productname. I
created this function for it:

=IF(C4="010";"product A";IF(C4="015";"product B";IF(C4="020";"product
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"product
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))

Uptill the moment of product G it works fine, but then I get product H,
which is the 8th product and therefore excel can not put it in one line
anymore (as excel can only include 7 IF functions) so it automatically
gives an INCORRECT.

First question is therefo Is there a way to change this so I can
include more then 7 products in the function line?


Then, I'll continue, If this is possible: How can I recreate this to an
automatic function which automatically takes the values of sheet 1 for
that specific product into account?
So when I insert a date, batchcode, a productcode (at this moment excel
should automatically insert the productname) and then insert a value
which I found for that batch, excel should automatically compare this
to that specific value in sheet 1...

I hope you know what I mean and furthermore I of course hope you can
help me because I almost gave up on this already....

Thanks!

CdFMarshall
I need a worksheet which contains in one sheet stats that can be
considered normal.


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile: http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392

Change =IF(C4="010";"product A";IF(C4="015";"product B";IF(C4="020";"product
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"product
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))
to
=IF(C4="010";"product A";"")&IF(C4="015";"product
B";"")&IF(C4="020";"product
C";"")&IF(C4="030";"product D";"")&IF(C4="040";"product
E";"")&IF(C4="050";"product
F";"")&IF(C4="060";"product G";"")&IF(C4="070";"product H";"")

better yet, make a table to look up the value:
010 product A
015 product B
020 product C
etc.
Suppose this is range F1:G10. You can use
=IF(ISERROR(VLOOKUP(C4,F1:G10,2,FALSE)),"INCORRECT ",VLOOKUP(C4,F1:G10,2,FALS
E))
and this can be as large as you need.
HTH
Bob Umlas

"CdFMarshall"
wrote in message
...

Hi all,

I am new here and found this forum because, as most persons here I
guess, I need some help in Excel.
I have been building a worksheet for my job information but get stuck.

What is the problem? I'll try and explain:
I am building an overview of ingredients of petfood. It should finally
result in a sheet in which you can fill in a value and the sheet says
it is ok or not.

In one sheet I have therefore lined out a couple of sorts petfoods
(junior, adult, and so on) with the values of the ingredients it would
contain in an ideal situation (for instance, vitamines of the standard
food with a minimum, maximum and mean value). I hope this part is
clear, please ask if it's not.

In the second sheet, the values which I find in a batch are filled in.
It shows as following:
date-batchcode-productcode-productname-and then the values I have found
for the ingredients followed by a correct / incorrect
It contains multiple functions.
The first function is the function that allows you to only insert the
productcode and excel will automatically fill in the productname. I
created this function for it:

=IF(C4="010";"product A";IF(C4="015";"product B";IF(C4="020";"product
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"product
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))

Uptill the moment of product G it works fine, but then I get product H,
which is the 8th product and therefore excel can not put it in one line
anymore (as excel can only include 7 IF functions) so it automatically
gives an INCORRECT.

First question is therefo Is there a way to change this so I can
include more then 7 products in the function line?


Then, I'll continue, If this is possible: How can I recreate this to an
automatic function which automatically takes the values of sheet 1 for
that specific product into account?
So when I insert a date, batchcode, a productcode (at this moment excel
should automatically insert the productname) and then insert a value
which I found for that batch, excel should automatically compare this
to that specific value in sheet 1...

I hope you know what I mean and furthermore I of course hope you can
help me because I almost gave up on this already....

Thanks!

CdFMarshall
I need a worksheet which contains in one sheet stats that can be
considered normal.


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile:
http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392

Try a lookup

Create a table

010 product A
015 product B
020 product C
030 product D
040 product E
050 product F
060 product G
070 product H

and use

=IF(ISNA(VLOOKUP(C2;M1:N8;2;False));"INCORRECT",VL OOKUP(C2;M1:N8;2;False))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"CdFMarshall"
wrote in message
...

Hi all,

I am new here and found this forum because, as most persons here I
guess, I need some help in Excel.
I have been building a worksheet for my job information but get stuck.

What is the problem? I'll try and explain:
I am building an overview of ingredients of petfood. It should finally
result in a sheet in which you can fill in a value and the sheet says
it is ok or not.

In one sheet I have therefore lined out a couple of sorts petfoods
(junior, adult, and so on) with the values of the ingredients it would
contain in an ideal situation (for instance, vitamines of the standard
food with a minimum, maximum and mean value). I hope this part is
clear, please ask if it's not.

In the second sheet, the values which I find in a batch are filled in.
It shows as following:
date-batchcode-productcode-productname-and then the values I have found
for the ingredients followed by a correct / incorrect
It contains multiple functions.
The first function is the function that allows you to only insert the
productcode and excel will automatically fill in the productname. I
created this function for it:

=IF(C4="010";"product A";IF(C4="015";"product B";IF(C4="020";"product
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"product
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))

Uptill the moment of product G it works fine, but then I get product H,
which is the 8th product and therefore excel can not put it in one line
anymore (as excel can only include 7 IF functions) so it automatically
gives an INCORRECT.

First question is therefo Is there a way to change this so I can
include more then 7 products in the function line?


Then, I'll continue, If this is possible: How can I recreate this to an
automatic function which automatically takes the values of sheet 1 for
that specific product into account?
So when I insert a date, batchcode, a productcode (at this moment excel
should automatically insert the productname) and then insert a value
which I found for that batch, excel should automatically compare this
to that specific value in sheet 1...

I hope you know what I mean and furthermore I of course hope you can
help me because I almost gave up on this already....

Thanks!

CdFMarshall
I need a worksheet which contains in one sheet stats that can be
considered normal.


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile:
http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392

The first thing I would do is create a table containing your product codes
and numbers.

Example: In D1 to D8, enter:
010
015
020
030
040
050
060
070

In E1 to E8, enter:
product A
product
product
product
product
product
product

"CdFMarshall"
wrote in message
...

Hi all,

I am new here and found this forum because, as most persons here I
guess, I need some help in Excel.
I have been building a worksheet for my job information but get stuck.

What is the problem? I'll try and explain:
I am building an overview of ingredients of petfood. It should finally
result in a sheet in which you can fill in a value and the sheet says
it is ok or not.

In one sheet I have therefore lined out a couple of sorts petfoods
(junior, adult, and so on) with the values of the ingredients it would
contain in an ideal situation (for instance, vitamines of the standard
food with a minimum, maximum and mean value). I hope this part is
clear, please ask if it's not.

In the second sheet, the values which I find in a batch are filled in.
It shows as following:
date-batchcode-productcode-productname-and then the values I have found
for the ingredients followed by a correct / incorrect
It contains multiple functions.
The first function is the function that allows you to only insert the
productcode and excel will automatically fill in the productname. I
created this function for it:

=IF(C4="010";"product A";IF(C4="015";"product B";IF(C4="020";"product
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"product
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))

Uptill the moment of product G it works fine, but then I get product H,
which is the 8th product and therefore excel can not put it in one line
anymore (as excel can only include 7 IF functions) so it automatically
gives an INCORRECT.

First question is therefo Is there a way to change this so I can
include more then 7 products in the function line?


Then, I'll continue, If this is possible: How can I recreate this to an
automatic function which automatically takes the values of sheet 1 for
that specific product into account?
So when I insert a date, batchcode, a productcode (at this moment excel
should automatically insert the productname) and then insert a value
which I found for that batch, excel should automatically compare this
to that specific value in sheet 1...

I hope you know what I mean and furthermore I of course hope you can
help me because I almost gave up on this already....

Thanks!

CdFMarshall
I need a worksheet which contains in one sheet stats that can be
considered normal.


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile:
http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392

You guys rule! ;)
I never even knew this function, wow! It did needed some translation to
find out what you guys meant, but it works! Thanks!

Ok, so part 1 of the question is solved. It now indeed gives a nice
productname whenever I fill in a value.
Now the, in my opinion even more tricky part of the question.

I'll draw it out for you;
I now have in sheet 2 colomn C the productcodes which I fill in, in
colomn D it now autimatically gives the productname with the function
you guys gave me, but now I have 5 ingredients of the product from
which I take samples. Every ingredient gives a value which I add to
this sheet (for instance vitamin D has a value of 50 in the test, this
is added in colomn E).
In another sheet (sheet 1) I have an overview of the minimum, maximum
and mean value of every ingredient (suppose for vitamin D this is 20,
70 and 45). In the sheet 2 colomn F, it should give an outcome whether
the filled in value is within the range of minimum and maximum
mentioned in sheet 1.

So it is:

C - D - E - F
productcode - productname - filled in value - correct/incorrect
(comparison with sheet1)

Thats the last part, you have any idea how this can be done?

Thanks a lot again!!! You guys are the greatest!

CdF Marshall


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile: http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392

Just use VLOOKUP again

=IF(AND(VLOOKUP(C2,Sheet1!A1:C10,2,False)=E2,VLOO KUP(C2,Sheet1!A1:C10,3,Fal
se)<=E2),"In range","Out of range")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"CdFMarshall"
wrote in message
...

You guys rule! ;)
I never even knew this function, wow! It did needed some translation to
find out what you guys meant, but it works! Thanks!

Ok, so part 1 of the question is solved. It now indeed gives a nice
productname whenever I fill in a value.
Now the, in my opinion even more tricky part of the question.

I'll draw it out for you;
I now have in sheet 2 colomn C the productcodes which I fill in, in
colomn D it now autimatically gives the productname with the function
you guys gave me, but now I have 5 ingredients of the product from
which I take samples. Every ingredient gives a value which I add to
this sheet (for instance vitamin D has a value of 50 in the test, this
is added in colomn E).
In another sheet (sheet 1) I have an overview of the minimum, maximum
and mean value of every ingredient (suppose for vitamin D this is 20,
70 and 45). In the sheet 2 colomn F, it should give an outcome whether
the filled in value is within the range of minimum and maximum
mentioned in sheet 1.

So it is:

C - D - E - F
productcode - productname - filled in value - correct/incorrect
(comparison with sheet1)

Thats the last part, you have any idea how this can be done?

Thanks a lot again!!! You guys are the greatest!

CdF Marshall


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile:
http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392

Bob,

Thanks for your response (have been ill a couple of days so couldnt
respons sooner) but I don't get it (still have to convert it to the
dutch language ;) )...

You say, use a combination of IF, AND and VLOOKUP...:

=IF(AND(VLOOKUP(C2,Sheet1!A1:C10,2,False)=E2,VLOO
KUP(C2,Sheet1!A1:C10,3,False)<=E2),"In range","Out of range")

But I don't get it.
What you say in my reading is: If the value in E2 (I suppose thats the
value I fill in?) is both equal/smaller then and equal/bigger then the
values in sheet 1-A1 till C10 column 2 and 3 (I suppose with this you
mean bigger/equal then minimum and smaller/equal then maximum value)
excel should mention "in range".
Ok, if thats what you mean, then I get that. But how do you make excell
look in the correct row that is combined with that product?

For example, if I fill in productcode 010 in sheet 2, excel now
mentions automatically the productname in the next cell. Ok, nice, but
in sheet 1 all products are mentioned below eachother with all
different minimum and maximum values for, for eaxample, vitamin B.
So if column A of sheet 2 is the batchcode I fill in, column B would be
the productcode (010 and so on) and column C would be the productname
(which excel automatically pops up). Column D would then be the value
of the ingredient (take vitamin B) which I found in the product and
which I fill in. In column E would then be mentioned if this value is
in range/out of range. But for this excel should look at the values
specifically for that product (010 for instance) and not for all
minimum and maximum values. Because the value could be ok for product C
(030) but out of range of product A (010) and then it should give an out
of range...

You understand what I mean? (it is hard to explain)

Thanks again for any response!

Marshall


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile: http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392

I understand from the radio silence that no one knows? Do I really need
to use acces for this? I resent Acces... :(

CdFMarshall


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile: http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392

You can replace your formula with a much simpler VLOOKUP, and this will
allow you to have many more products. First of all set up a two-column
table somewhere (assume Y1 to Z30 on the same Sheet1). In column Y you
would enter '010, '015, '020, '030 etc down the column, and in column Z
you would have product A, product B, product C etc adjacent to the
codes.

Then your formula can be replaced with this one:

=VLOOKUP(C4,Y$1:Z$30,2,0)

This takes the value in C4 and sees if there is an exact match in the
first column of the table Y1:Z30 - if there is it then returns the
value which is in the second column of the table (this is the parameter
2 in the formula). If there is not an exact match the formula returns
the #N/A error.

Hope this helps.

Pete

My reply was sent inadvertently prior to being complete. I was suggesting
the Lookup function. However, I see someone has now already suggested that.
So you should have what you need.

"CdFMarshall"
wrote in message
...

Hi all,

I am new here and found this forum because, as most persons here I
guess, I need some help in Excel.
I have been building a worksheet for my job information but get stuck.

What is the problem? I'll try and explain:
I am building an overview of ingredients of petfood. It should finally
result in a sheet in which you can fill in a value and the sheet says
it is ok or not.

In one sheet I have therefore lined out a couple of sorts petfoods
(junior, adult, and so on) with the values of the ingredients it would
contain in an ideal situation (for instance, vitamines of the standard
food with a minimum, maximum and mean value). I hope this part is
clear, please ask if it's not.

In the second sheet, the values which I find in a batch are filled in.
It shows as following:
date-batchcode-productcode-productname-and then the values I have found
for the ingredients followed by a correct / incorrect
It contains multiple functions.
The first function is the function that allows you to only insert the
productcode and excel will automatically fill in the productname. I
created this function for it:

=IF(C4="010";"product A";IF(C4="015";"product B";IF(C4="020";"product
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"product
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))

Uptill the moment of product G it works fine, but then I get product H,
which is the 8th product and therefore excel can not put it in one line
anymore (as excel can only include 7 IF functions) so it automatically
gives an INCORRECT.

First question is therefo Is there a way to change this so I can
include more then 7 products in the function line?


Then, I'll continue, If this is possible: How can I recreate this to an
automatic function which automatically takes the values of sheet 1 for
that specific product into account?
So when I insert a date, batchcode, a productcode (at this moment excel
should automatically insert the productname) and then insert a value
which I found for that batch, excel should automatically compare this
to that specific value in sheet 1...

I hope you know what I mean and furthermore I of course hope you can
help me because I almost gave up on this already....

Thanks!

CdFMarshall
I need a worksheet which contains in one sheet stats that can be
considered normal.


--
CdFMarshall
------------------------------------------------------------------------
CdFMarshall's Profile:
http://www.excelforum.com/member.php...o&userid=32085
View this thread: http://www.excelforum.com/showthread...hreadid=518392