Item numbers and descriptions

  • Thread starter Thread starter Mike F.
  • Start date Start date
M

Mike F.

I am attempting to create a semi-automated billing/receipt form. I
would like it to behave so that, when an item number is filled in one
cel, the cels that hold the descriptions and price for that item fill
out automatically.

The only way I know how to do it is by using IF and that is a little
cumbersome and has limitations.

For example:
A1: item1
B1: =IF(A1="item1","This is the description of
item1",IF(A1="item2","This is the description of
item2",IF(A1="item3",etc....
C1: =IF(A1="item1",4.95,IF(A1="item2",12.95,IF(A1="item3",etc....

This method quickly becomes cumbersome for even a small number of
items. Plus, the IF statement seems to have a maximum number of times
you can use it in a single phrase.

It seems like I need to build some sort of database or array that a
cel can refer to to retrieve the proper description and price for an
item. But I have no idea how I would do this. Is there an obvious
solution?
 
Mike F. said:
I am attempting to create a semi-automated billing/receipt form. I
would like it to behave so that, when an item number is filled in one
cel, the cels that hold the descriptions and price for that item fill
out automatically.

The only way I know how to do it is by using IF and that is a little
cumbersome and has limitations.

For example:
A1: item1
B1: =IF(A1="item1","This is the description of
item1",IF(A1="item2","This is the description of
item2",IF(A1="item3",etc....
C1: =IF(A1="item1",4.95,IF(A1="item2",12.95,IF(A1="item3",etc....

This method quickly becomes cumbersome for even a small number of
items. Plus, the IF statement seems to have a maximum number of times
you can use it in a single phrase.

It seems like I need to build some sort of database or array that a
cel can refer to to retrieve the proper description and price for an
item. But I have no idea how I would do this. Is there an obvious
solution?
Click to expand...

What you speak of in your last paragraph can be done with VLOOKUP, and would
be much faster for formula writing than IF (which you are right about being
limited in nesting depth, 29 If I remember correctly)
 
The nesting limit is 7.

--
Regards,
Tom Ogilvy

M. lane said:
What you speak of in your last paragraph can be done with VLOOKUP, and would
be much faster for formula writing than IF (which you are right about being
limited in nesting depth, 29 If I remember correctly)
Click to expand...
 
Back
Top