I'm trying to create an an invoice/receipt worksheet, so that when I input a
number in a cell, the adjacent cells automatically fill using predefined
information.
For example:

If i input 1234(item no) into cell A1, I want a description to appear in
cell B1 and a Price in C1.

If anyone knows how to do this, please could you explain in Laymens terms as
Im new to all this.

Many thanks and any help is appreciated.

VLOOKUP formulas will pull the data to appropriate cells.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

On Wed, 26 Sep 2007 12:55:04 -0700, Simon Blackburn
wrote:

I'm trying to create an an invoice/receipt worksheet, so that when I input a
number in a cell, the adjacent cells automatically fill using predefined
information.
For example:

If i input 1234(item no) into cell A1, I want a description to appear in
cell B1 and a Price in C1.

If anyone knows how to do this, please could you explain in Laymens terms as
Im new to all this.

Many thanks and any help is appreciated.

Example.

A1 A2 A3
code Desc price
1234 white 1.99
2345 red 2.99
3456 black 3.99

I will create the table (above) on worksheet 3.
When i select A1 on worksheet 1 and type in 1234, I want White to appear in
B1 and 1.99 to appear in C1. The instructions given on
http://www.contextures.on.ca/xlFunctions02.html doesn't really explain
clearly how to do this.

"Gord Dibben" wrote:

VLOOKUP formulas will pull the data to appropriate cells.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

On Wed, 26 Sep 2007 12:55:04 -0700, Simon Blackburn
wrote:

I'm trying to create an an invoice/receipt worksheet, so that when I input a
number in a cell, the adjacent cells automatically fill using predefined
information.
For example:

If i input 1234(item no) into cell A1, I want a description to appear in
cell B1 and a Price in C1.

If anyone knows how to do this, please could you explain in Laymens terms as
Im new to all this.

Many thanks and any help is appreciated.

Debra explains it quite well but here goes a try.

First of all A1, A2, A3 do not run across the sheet as you show.

Your table of A1, A2, A3 on Sheet3 must be a typo.

I think you mean Column A, Column B and Column C

On Sheet1 B1 enter =VLOOKUP(A1,Sheet3!$A$2:$C$4,2,FALSE)

In C1 enter =VLOOKUP(A1,Sheet3!$A$2:$C$4,3,FALSE)

Enter a code number into A1 to return Desc. and Price. from Sheet3


Gord


On Wed, 26 Sep 2007 15:00:00 -0700, Simon Blackburn
wrote:

Example.

A1 A2 A3
code Desc price
1234 white 1.99
2345 red 2.99
3456 black 3.99

I will create the table (above) on worksheet 3.
When i select A1 on worksheet 1 and type in 1234, I want White to appear in
B1 and 1.99 to appear in C1. The instructions given on
http://www.contextures.on.ca/xlFunctions02.html doesn't really explain
clearly how to do this.

"Gord Dibben" wrote:

VLOOKUP formulas will pull the data to appropriate cells.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

On Wed, 26 Sep 2007 12:55:04 -0700, Simon Blackburn
wrote:

I'm trying to create an an invoice/receipt worksheet, so that when I input a
number in a cell, the adjacent cells automatically fill using predefined
information.
For example:

If i input 1234(item no) into cell A1, I want a description to appear in
cell B1 and a Price in C1.

If anyone knows how to do this, please could you explain in Laymens terms as
Im new to all this.

Many thanks and any help is appreciated.

You set up an Item table such as columns G, H and I for all your items.
Then you enter your item numbers in column A. Column C shows the formulas
that are actually in column B and column E shows the formulas that are in
column D. Column B looks up the column A item number in the Item table to
get the desc and column D looks up the column A item number in the Item
table to get the price. If the item number in column A is not in the table,
#N/A is returned. (#N/A = not available). After you put the formulas in
columns B and D, you can delete columns C and E. Look again at
http://www.contextures.on.ca/xlFunctions02.html. That example shows one look
up to get the desc. I just extended it with a second formula to get the
price.


A
B
C
D
E
F
G
H
I

1

2
Items
3
Enter Item no.
Item no.
Desc.
Price

4
3456
black
=VLOOKUP(A4,$G$4:$I$6,2,FALSE)
3.99
=VLOOKUP(A4,$G$4:$I$6,3,FALSE)
1234
white
1.99

5
2345
red
=VLOOKUP(A5,$G$4:$I$6,2,FALSE)
2.99
=VLOOKUP(A5,$G$4:$I$6,3,FALSE)
2345
red
2.99

6
6789
#N/A
=VLOOKUP(A6,$G$4:$I$6,2,FALSE)
#N/A
=VLOOKUP(A6,$G$4:$I$6,3,FALSE)
3456
black
3.99

7
1234
white
=VLOOKUP(A7,$G$4:$I$6,2,FALSE)
1.99
=VLOOKUP(A7,$G$4:$I$6,3,FALSE)


"
Have fun.




Simon Blackburn" wrote in message
...
I'm trying to create an an invoice/receipt worksheet, so that when I input
a
number in a cell, the adjacent cells automatically fill using predefined
information.
For example:

If i input 1234(item no) into cell A1, I want a description to appear in
cell B1 and a Price in C1.

If anyone knows how to do this, please could you explain in Laymens terms
as
Im new to all this.

Many thanks and any help is appreciated.

Sorry, that didn't work well. Lost all positions for columns and rows.

"Wondering" wrote in message
t...
You set up an Item table such as columns G, H and I for all your items.
Then you enter your item numbers in column A. Column C shows the formulas
that are actually in column B and column E shows the formulas that are in
column D. Column B looks up the column A item number in the Item table to
get the desc and column D looks up the column A item number in the Item
table to get the price. If the item number in column A is not in the
table, #N/A is returned. (#N/A = not available). After you put the
formulas in columns B and D, you can delete columns C and E. Look again
at http://www.contextures.on.ca/xlFunctions02.html. That example shows one
look up to get the desc. I just extended it with a second formula to get
the price.


A
B
C
D
E
F
G
H
I

1

2
Items
3
Enter Item no.
Item no.
Desc.
Price

4
3456
black
=VLOOKUP(A4,$G$4:$I$6,2,FALSE)
3.99
=VLOOKUP(A4,$G$4:$I$6,3,FALSE)
1234
white
1.99

5
2345
red
=VLOOKUP(A5,$G$4:$I$6,2,FALSE)
2.99
=VLOOKUP(A5,$G$4:$I$6,3,FALSE)
2345
red
2.99

6
6789
#N/A
=VLOOKUP(A6,$G$4:$I$6,2,FALSE)
#N/A
=VLOOKUP(A6,$G$4:$I$6,3,FALSE)
3456
black
3.99

7
1234
white
=VLOOKUP(A7,$G$4:$I$6,2,FALSE)
1.99
=VLOOKUP(A7,$G$4:$I$6,3,FALSE)


"
Have fun.




Simon Blackburn" wrote in
message ...
I'm trying to create an an invoice/receipt worksheet, so that when I
input a
number in a cell, the adjacent cells automatically fill using predefined
information.
For example:

If i input 1234(item no) into cell A1, I want a description to appear
in
cell B1 and a Price in C1.

If anyone knows how to do this, please could you explain in Laymens terms
as
Im new to all this.

Many thanks and any help is appreciated.

Thats much easier to understand, like i said, i'm a complete novice. Thanks
Gord!

"Gord Dibben" wrote:

Debra explains it quite well but here goes a try.

First of all A1, A2, A3 do not run across the sheet as you show.

Your table of A1, A2, A3 on Sheet3 must be a typo.

I think you mean Column A, Column B and Column C

On Sheet1 B1 enter =VLOOKUP(A1,Sheet3!$A$2:$C$4,2,FALSE)

In C1 enter =VLOOKUP(A1,Sheet3!$A$2:$C$4,3,FALSE)

Enter a code number into A1 to return Desc. and Price. from Sheet3


Gord


On Wed, 26 Sep 2007 15:00:00 -0700, Simon Blackburn
wrote:

Example.

A1 A2 A3
code Desc price
1234 white 1.99
2345 red 2.99
3456 black 3.99

I will create the table (above) on worksheet 3.
When i select A1 on worksheet 1 and type in 1234, I want White to appear in
B1 and 1.99 to appear in C1. The instructions given on
http://www.contextures.on.ca/xlFunctions02.html doesn't really explain
clearly how to do this.

"Gord Dibben" wrote:

VLOOKUP formulas will pull the data to appropriate cells.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

On Wed, 26 Sep 2007 12:55:04 -0700, Simon Blackburn
wrote:

I'm trying to create an an invoice/receipt worksheet, so that when I input a
number in a cell, the adjacent cells automatically fill using predefined
information.
For example:

If i input 1234(item no) into cell A1, I want a description to appear in
cell B1 and a Price in C1.

If anyone knows how to do this, please could you explain in Laymens terms as
Im new to all this.

Many thanks and any help is appreciated.

I could have copied that directly from Debra's site.

Also if you had downloaded the sample workbook you could have just substituted
your ranges for the ones Debra used.


Gord

On Thu, 27 Sep 2007 01:07:01 -0700, Simon Blackburn
wrote:

Thats much easier to understand, like i said, i'm a complete novice. Thanks
Gord!

"Gord Dibben" wrote:

Debra explains it quite well but here goes a try.

First of all A1, A2, A3 do not run across the sheet as you show.

Your table of A1, A2, A3 on Sheet3 must be a typo.

I think you mean Column A, Column B and Column C

On Sheet1 B1 enter =VLOOKUP(A1,Sheet3!$A$2:$C$4,2,FALSE)

In C1 enter =VLOOKUP(A1,Sheet3!$A$2:$C$4,3,FALSE)

Enter a code number into A1 to return Desc. and Price. from Sheet3


Gord


On Wed, 26 Sep 2007 15:00:00 -0700, Simon Blackburn
wrote:

Example.

A1 A2 A3
code Desc price
1234 white 1.99
2345 red 2.99
3456 black 3.99

I will create the table (above) on worksheet 3.
When i select A1 on worksheet 1 and type in 1234, I want White to appear in
B1 and 1.99 to appear in C1. The instructions given on
http://www.contextures.on.ca/xlFunctions02.html doesn't really explain
clearly how to do this.

"Gord Dibben" wrote:

VLOOKUP formulas will pull the data to appropriate cells.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

On Wed, 26 Sep 2007 12:55:04 -0700, Simon Blackburn
wrote:

I'm trying to create an an invoice/receipt worksheet, so that when I input a
number in a cell, the adjacent cells automatically fill using predefined
information.
For example:

If i input 1234(item no) into cell A1, I want a description to appear in
cell B1 and a Price in C1.

If anyone knows how to do this, please could you explain in Laymens terms as
Im new to all this.

Many thanks and any help is appreciated.