Question about auto fills or linking sheets

CEN · #1 07-13-2005, 11:11 PM

Hello all. I am very new to excel and want to know if this is possible.
I want to enter a name into box I6 and have it automatically fill in box B6, C6,D6 AND K6 on sheet 1, when I hit enter or toggle one of the arrow keys to another box.. I have about 200 or so different items that can go into row I. Sheet 2 has all the serial numbers and stock numbers etc.

Row B,C,D and K are names of the items in the inventory, basically a combination of numbers and names with no math involved. Just serial numbers and stock numbers, that sort of thing.

What I need is to be able to enter the common name into row I6 thru I15 (One row at a time. These are the only rows that will be used at any one time for each sheet before printing) and have the corresponding serial numbers / stock numbers/ inventory numbers etc entered automatically. I realize that setting this up initially could be time consuming but it would save me tons of time in the long run.

Ie, I enter “cotton twine” for I6 and enter, then the corresponding info for that twine pops up in B,C,D and K6.
Thanks for the help!

#2 07-14-2005, 02:05 AM

You can use the Vlookup function to do this. Have a look for it in help.
Rowan

"CEN" wrote:

>
> Hello all. I am very new to excel and want to know if this is possible.
>
> I want to enter a name into box I6 and have it automatically fill in
> box B6, C6,D6 AND K6 on sheet 1, when I hit enter or toggle one of the
> arrow keys to another box.. I have about 200 or so different items that
> can go into row I. Sheet 2 has all the serial numbers and stock numbers
> etc.
>
> Row B,C,D and K are names of the items in the inventory, basically a
> combination of numbers and names with no math involved. Just serial
> numbers and stock numbers, that sort of thing.
>
> What I need is to be able to enter the common name into row I6 thru I15
> (One row at a time. These are the only rows that will be used at any one
> time for each sheet before printing) and have the corresponding serial
> numbers / stock numbers/ inventory numbers etc entered automatically. I
> realize that setting this up initially could be time consuming but it
> would save me tons of time in the long run.
>
> Ie, I enter â€œcotton twineâ€ for I6 and enter, then the corresponding
> info for that twine pops up in B,C,D and K6.
> Thanks for the help!
>
>
> --
> CEN
> ------------------------------------------------------------------------
> CEN's Profile: http://www.excelforum.com/member.php...o&userid=25215
> View this thread: http://www.excelforum.com/showthread...hreadid=387048
>
>

#3 07-14-2005, 02:05 AM

> I want to enter a name into box I6 and have it automatically fill in
> box B6, C6,D6 AND K6 on sheet 1

Think a suitable set-up would be to create data validation droplists with
which to select the items in I6 to I15 in Sheet1, then have either
VLOOKUP(...) or INDEX(.., MATCH(...)) to lookup the selected values and
return the corresponding info from the inventory sheet into the desired
columns B, C, D & K, with the range/match type set to 0/FALSE for exact
matches.

Perhaps take a look at Debra Dalgleish's nice coverage at her:
http://www.contextures.com/xlOrderForm01.html
(there's also a sample file available to d/l and study)

And if you could post (post in plain text in the message area) some sample
data from the inventory sheet showing how it's set-up there, and what you
want returned exactly from the inventory sheet into the cols B, C, D & K in
Sheet1 (describe and take us through an example or two), think you'd be able
to receive help on the required formulas etc fairly quickly.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"CEN" <CEN.1s55qx_1121313929.2191@excelforum-nospam.com> wrote in message
news:CEN.1s55qx_1121313929.2191@excelforum-nospam.com...
>
> Hello all. I am very new to excel and want to know if this is possible.
>
> I want to enter a name into box I6 and have it automatically fill in
> box B6, C6,D6 AND K6 on sheet 1, when I hit enter or toggle one of the
> arrow keys to another box.. I have about 200 or so different items that
> can go into row I. Sheet 2 has all the serial numbers and stock numbers
> etc.
>
> Row B,C,D and K are names of the items in the inventory, basically a
> combination of numbers and names with no math involved. Just serial
> numbers and stock numbers, that sort of thing.
>
> What I need is to be able to enter the common name into row I6 thru I15
> (One row at a time. These are the only rows that will be used at any one
> time for each sheet before printing) and have the corresponding serial
> numbers / stock numbers/ inventory numbers etc entered automatically. I
> realize that setting this up initially could be time consuming but it
> would save me tons of time in the long run.
>
> Ie, I enter “cotton twine” for I6 and enter, then the corresponding
> info for that twine pops up in B,C,D and K6.
> Thanks for the help!
>
>
> --
> CEN
> ------------------------------------------------------------------------
> CEN's Profile:
http://www.excelforum.com/member.php...o&userid=25215
> View this thread: http://www.excelforum.com/showthread...hreadid=387048
>

CEN · #4 07-14-2005, 01:02 PM

......B...............C...................D............ I .............. K
1. Twine...... TW-4357 ...... 049830d.....2BRT3.....5820-01-410-8981
2. AC........... HK-4723...... 5788125..... 1dep ....6234-00-257-9831
3. AC........... HK-4723...... 578126..... 2dep ....6234-00-257-9831
4. AC........... HK-4723...... 578995..... 3dep ....6234-00-257-9831
5. AC........... HK-4723...... 578414..... 4dep ....6234-00-257-9831

It looks like this, while some items are the same but have different serial numbers. On the second page I have a complete list of all the items (about 200 or so) but only use about 16 lines at any one time on this sheet. I want to type in the info from box I and have the other information (which is on the second page) put in.
So far this is some great responses and quick too.

I will have to try it out this friday when my schedule lets up a little. Thanks all!

#5 07-14-2005, 04:05 PM

Assuming your inventory sheet is in Sheet2, and is identical in structure to
Sheet1, with data from row2 down

In Sheet1
------------
Item will be keyed into I6:I16

Put in B6:
=IF(ISNA(MATCH($I6,Sheet2!$I:$I,0)),"",INDEX(Sheet2!B:B,MATCH($I6,Sheet2!$I:
$I,0)))
Copy B6 across to D6, fill down D16

Put in K6
=IF(ISNA(MATCH($I6,Sheet2!$I:$I,0)),"",INDEX(Sheet2!K:K,MATCH($I6,Sheet2!$I:
$I,0)))
Copy K6 down to K16

(Items are assumed unique in the inventory sheet)

A sample file is available at:
http://www.savefile.com/files/3429148
File: Cen_misc.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"CEN" <CEN.1s65v5_1121360801.1948@excelforum-nospam.com> wrote in message
news:CEN.1s65v5_1121360801.1948@excelforum-nospam.com...
>
> B C D I
> K
> 1 Twine TW-4357 049830A 2BRT3 5820-01-410-8981
> 2 AC HK-4723 5788125 1dep 6234-00-257-9831
> 3 AC HK-4723 578126 2dep 6234-00-257-9831
> 4 AC HK-4723 578995 3dep 6234-00-257-9831
> 5 AC HK-4723 578414 4dep 6234-00-257-9831
>
> It looks like this, while some items are the same but have different
> serial numbers. On the second page I have a complete list of all the
> items (about 200 or so) but only use about 16 lines at any one time on
> this sheet. I want to type in the info from box I and have the other
> information (which is on the second page) put in.
> So far this is some great responses and quick too.

> I will have to try it out this friday when my schedule lets up a
> little. Thanks all!
>
>
> --
> CEN
> ------------------------------------------------------------------------
> CEN's Profile:
http://www.excelforum.com/member.php...o&userid=25215
> View this thread: http://www.excelforum.com/showthread...hreadid=387048
>

Welcome to the Excel Forum

Forum Statistics