Auto fill Adjacent cells

[Guest]

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.

 

[Gord Dibben]

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

 

[Guest]

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]

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

 

[Wondering]

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.

 

[Wondering]

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

 

[Guest]

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

 

[Gord Dibben]

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