Auto fill Adjacent cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simon Blackburn - 26 Sep 2007 20:55 GMT

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.

Reply to this Message

Gord Dibben - 26 Sep 2007 21:06 GMT

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

>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
[quoted text clipped - 8 lines]
>
>Many thanks and any help is appreciated.

Reply to this Message

Simon Blackburn - 26 Sep 2007 23:00 GMT

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.

> VLOOKUP formulas will pull the data to appropriate cells.
>
[quoted text clipped - 21 lines]
> >
> >Many thanks and any help is appreciated.

Reply to this Message

Gord Dibben - 26 Sep 2007 23:53 GMT

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

> Example.
>
[quoted text clipped - 35 lines]
>> >
>> >Many thanks and any help is appreciated.

Reply to this Message

Simon Blackburn - 27 Sep 2007 09:07 GMT

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

> Debra explains it quite well but here goes a try.
>
[quoted text clipped - 51 lines]
> >> >
> >> >Many thanks and any help is appreciated.

Reply to this Message

Gord Dibben - 27 Sep 2007 16:50 GMT

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

>Thats much easier to understand, like i said, i'm a complete novice. Thanks
>Gord!
[quoted text clipped - 54 lines]
>> >> >
>> >> >Many thanks and any help is appreciated.

Reply to this Message

Wondering - 27 Sep 2007 00:37 GMT

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.

> I'm trying to create an an invoice/receipt worksheet, so that when I input
> a
[quoted text clipped - 10 lines]
>
> Many thanks and any help is appreciated.

Reply to this Message

Wondering - 27 Sep 2007 00:44 GMT

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

> 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
[quoted text clipped - 83 lines]
>>
>> Many thanks and any help is appreciated.

Reply to this Message